Formula for a thermometer-style status bar using 2 numbers

[VancitysFinest]

This one might be hard to explain. I'm trying to create a status bar
(mine is shaped like a thermometer and it fills in red) where the cells
fill in using Conditional Formatting.

The user enters 2 numbers, the larger number for the max value of the
thermometer (i.e. 90 degrees F, and a smaller number for the current
status (i.e. 30C). Whatever values the user enters, the values labeling
the thermometer should automatically fit that data, and the cells
beside fill in with a red color.

I currently have it working but I'm looking for a more accurate formula
not using macros that works for a greater variety of numbers. Here is
how it is setup now:
- The cell next to the top of the thermometer (B8) is set to what the
user enters for a max value (i.e. 30)
- The bottom cell (B38) is set to 0.
- The cells inbetween (minor ticks) use this formula:
=B8-($B$8-$B$13)/5 (this is for B9)
- B13 and every 5th cell below it are the major tick marks:
=ROUND(B13-$B$8/6,0)
- the cells to be colored, i.e. D8, are: =IF(B8-1<$G$37,1,0) where G37
is the current value the user entered) so if the value is one
Conditional Formatting sets the color to red, or else it stays white

It works well for max values greater than 10, but below that the status
bar doesn't exactly fill up to where it should. I can send you the file
if you email me if this is unclear! Thanks!

[VancitysFinest]

Still waiting for a response? A mathematician might have the answer.

[Florida User]

"VancitysFinest" wrote:

> Still waiting for a response? A mathematician might have the answer.
>
>Try http://www.datapigtechnologies.com/f...artcharts.html

[FxM]

Hi,

Try : http://cjoint.com/?ipoAryihn7

Regards
FxM



VancitysFinest a écrit :
> This one might be hard to explain. I'm trying to create a status bar
> (mine is shaped like a thermometer and it fills in red) where the cells
> fill in using Conditional Formatting.
>
> The user enters 2 numbers, the larger number for the max value of the
> thermometer (i.e. 90 degrees F, and a smaller number for the current
> status (i.e. 30C). Whatever values the user enters, the values labeling
> the thermometer should automatically fit that data, and the cells
> beside fill in with a red color.
>
> I currently have it working but I'm looking for a more accurate formula
> not using macros that works for a greater variety of numbers. Here is
> how it is setup now:
> - The cell next to the top of the thermometer (B8) is set to what the
> user enters for a max value (i.e. 30)
> - The bottom cell (B38) is set to 0.
> - The cells inbetween (minor ticks) use this formula:
> =B8-($B$8-$B$13)/5 (this is for B9)
> - B13 and every 5th cell below it are the major tick marks:
> =ROUND(B13-$B$8/6,0)
> - the cells to be colored, i.e. D8, are: =IF(B8-1<$G$37,1,0) where G37
> is the current value the user entered) so if the value is one
> Conditional Formatting sets the color to red, or else it stays white
>
> It works well for max values greater than 10, but below that the status
> bar doesn't exactly fill up to where it should. I can send you the file
> if you email me if this is unclear! Thanks!
>