Excel Formula: trouble with the low % values not showing up in an in-cell chart? (image included)

Question

I've a few tables that have % values like -4.5%; -16%; 15%; 6%; 2.5%. I'm interested in creating some in-Cell charts that show positive and negative values.

My smaller values aren't showing up, so I've used some larger %'s to see what happens (See small image). It's only near the 200% and above, or -200% and below that the bar starts to show (the bar is in Column Y2).

.

The formula in the top right cell is below, and this is copied down accordingly (i've left % in the lower rows by accident,but it doesn't change anything...).

=IF($W3>0,REPT($Y$2,ROUND($W3/2,1)),REPT($Y$2,ROUND(-$W3/2,1)))

And my actual workbook is set up as follows.

What should I do to this formula to make the lower figures show up? Even if the larger figure shoots of the screen.

Many thanks for your help.

Michael.

Answer 1

That's because 100% in Excel is actually 1.0, not 100.0

In most cases here you are telling REPT to repeat something less than once, so it prints nothing.

Instead of dividing by 2 you should be multiplying by (100 / inc) where inc is the percentage points you want per repeated text.