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

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

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

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