Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which formula(s) do I need?
Hello,
It has been a bit since a statistics class and I cannot remember which formula(s) I should be using. What I am trying to do is this: I have data that I want to apply conditional formatting data bars to, but the bars are not useful if they range from min to max. The data is really large but only varies by a small percentage, so I would like the bars to represent this. The data ranges from 9.360E+07 to 9.362E+07 and this is a pretty small difference so min to max is not useful. Not all of the data has such a small range, though, for instance 582 to 2677 or 6.4E7 to 1.4E8. Right now the best I have is 10 minus the number of standard deviations from the max. Any help is appreciated. Thanks, Brandon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which formula(s) do I need?
Using an expression like this:
=(number-min)*100/(max-min) you will end up with a range that varies from 0 to 100. You can change the scaling factor if you want a smaller (or larger) range. Hope this helps. Pete On Nov 27, 5:22*pm, Brandon wrote: Hello, * It has been a bit since a statistics class and I cannot remember which formula(s) I should be using. *What I am trying to do is this: *I have data that I want to apply conditional formatting data bars to, but the bars are not useful if they range from min to max. *The data is really large but only varies by a small percentage, so I would like the bars to represent this. * The data ranges from 9.360E+07 to 9.362E+07 and this is a pretty small difference so min to max is not useful. *Not all of the data has such a small range, though, for instance 582 to 2677 or 6.4E7 to 1.4E8. *Right now the best I have is 10 minus the number of standard deviations from the max. *Any help is appreciated. Thanks, Brandon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which formula(s) do I need?
Pete,
Thanks for the response. I am looking for a way to not necessarily range from 0 to 100. For instance, the percent difference of the first range of numbers I gave is 0.02%, it's not useful to me if the bar lengths range from 0 to 100, a range of ~90 to 100 would be more useful. The problem is that some number ranges are very large numbers so I cannot just set the min number of the bar length to value 0 and the max number of the bar length to the max value in the set. Brandon "Pete_UK" wrote: Using an expression like this: =(number-min)*100/(max-min) you will end up with a range that varies from 0 to 100. You can change the scaling factor if you want a smaller (or larger) range. Hope this helps. Pete On Nov 27, 5:22 pm, Brandon wrote: Hello, It has been a bit since a statistics class and I cannot remember which formula(s) I should be using. What I am trying to do is this: I have data that I want to apply conditional formatting data bars to, but the bars are not useful if they range from min to max. The data is really large but only varies by a small percentage, so I would like the bars to represent this. The data ranges from 9.360E+07 to 9.362E+07 and this is a pretty small difference so min to max is not useful. Not all of the data has such a small range, though, for instance 582 to 2677 or 6.4E7 to 1.4E8. Right now the best I have is 10 minus the number of standard deviations from the max. Any help is appreciated. Thanks, Brandon |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which formula(s) do I need?
I think I found a useful solution if no one else has a better one. I will
just set the range of the data bars to be from 50% of the max to 100% of the max. Relatively close values will have the same length of bar but it is still sensitive to a larger difference. Brandon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|