In cell formulas

[Julio Medallion]

Hi,
Just wondering if anyone can help me with an incell formula.
(See cells below).
Currently the formulas in C2 to C11 start with in cell C2
=IF($A2>0,REPT($C$1,ROUND($A2/3,1)),REPT($C$1,ROUND(-$A2/3,1)))

Is it possible to write a formula based on column C, in two seperate columns,(one for positive values and one for negative values).
What I want to get is two seperate columns, one with red incell bars based on the negative values and one with green incell bars for positive values. I am working in Excel 2010. Maybe this is possible using sparklines??? All suggestions appreciated.

<table border="0" cellpadding="0" cellspacing="0" width="256"><colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:48pt" align="center" height="20" width="64">A1</td> <td class="xl68" style="width:48pt" align="center" width="64">B1</td> <td class="xl68" style="width:48pt" align="center" width="64">C1</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="center" height="20">
</td> <td class="xl65" style="border-left:none" align="center">
</td> <td class="xl67" style="border-left:none" align="center">█</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">0</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">0</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">0</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">0</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">-2</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">0</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">0</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">4</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">█</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">8</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">██</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" align="center" height="20">0</td> <td class="xl65" style="border-top:none;border-left:none" align="center">
</td> <td class="xl67" style="border-top:none;border-left:none" align="center">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">
</td> <td>
</td> <td class="xl64">
</td> <td>
</td> </tr> </tbody></table>

 

[GlennUK]

Just use conditional formatting to colour the bars, like this:

Excel Workbook
	A	B	C
1	*	*
2	0	*	*
3	0	*	*
4	0	*	*
5	0	*	*
6	-12	*
7	0	*	*
8	0	*	*
9	4	*
10	8	*
Sheet21

.. it doesn't show the same as on my machine, but you can see the colours there.

Condition formula of :
=$A2 > 0
with a font colour of green,

and a condition formula of
=$A2 < 0
with a font colour of red,

applied to C2:C10

 

[Julio Medallion]

Thanks Glenn,

I must be dense but when I tried your suggestion it worked except for the cell with -12,i.e. a6, still shows up as green when I format it. Maybe I am just not formatting it correctly?

Thanks,
Julio

 

[GlennUK]

Reply back with your Condition Formulas copied in to the reply.

 

[Julio Medallion]

Glenn,
In edit Formatting Rule-Use a formula to determine which cells to format-

Rule-Formula-$a2<0-
Format AaBBCcYyZz-
Applies to-=$c$2:$c$11

Rule-Formula-$a2>0-
Format Green-
Applies to-=$c$2:$c$11
Stop if True-not checked for both.

Can send on the file to you to look at if you like, if this does not
solve it.

Thanks,
Julio

 

[GlennUK]

I've just send you a Private Message.