Traffic light warning system

[R0nseal]

Hi

Hoping someone here can help me...
I have a SS which has an income cell and then an expenses cell... In a new cell which looks at the expenses and shows what percentage of the income is now covered by expenses and gives the percentage figure and then like a traffic lights system, green from 0% to 10% then 10% to 15% amber and then at 20% i want the cell to go red...

Can anyone help?

Thanks
R0nseal

 

[Mentor82]

Hi,
For this use wxcel conditional formatting.
Select the range of cells which include percentage values you want to format an then click on the tab on Conditional Formatting, select Icon Sets and.then set up colourimg based.on percentage values.

 

[kweaver]

As Mentor suggests...
Have you tried 3 conditional format formulas?

Select your range (e.g., A1:A100) and apply these three:

=AND(A1>=0%,A1<=10%) [font or fill as green]
=AND(A1>10%,A1<=15%) [amber]
=A1>15% [red]

 

[R0nseal]

Hi,
For this use wxcel conditional formatting.
Select the range of cells which include percentage values you want to format an then click on the tab on Conditional Formatting, select Icon Sets and.then set up coloring's based.on percentage values.

Hi, thanks for the reply... I'm stuck...

So all i have atm is...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Income[/TD]
[TD]Expenses[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So how do i create a third column which is % of income covered by expenses? What formula would i need so that it will then tell me for the example above, that we are at 10%? And then have it red, amber green based on the %?

Sorry im very basic wit excel

 

[kweaver]

If you mean "what % of the income is equal to the expense?" --

Column C would be formatted as a percent after: =B2/A2 which in the case of $1000 and $100 would result in 10%

 

[Mentor82]

Hi,
Lets assume that income value is in A2 and expenses in B2 so you can calculate percentage in C2 as follows:
=B2/C2
The result of that would be 0.1 and now you have to format the cell C2 to percentage (right click of the mouse on the C2 cell and select format cells and then select percentage)
Once you do it you have to use conditional formatting as I described previously.

 

[R0nseal]

Hi,
Lets assume that income value is in A2 and expenses in B2 so you can calculate percentage in C2 as follows:
=B2/C2
The result of that would be 0.1 and now you have to format the cell C2 to percentage (right click of the mouse on the C2 cell and select format cells and then select percentage)
Once you do it you have to use conditional formatting as I described previously.

Thanks, I now have the setup window for icon sets, but not sure what to fill in as everything i try, it shows an error...

when value is: >= Value etc etc

 

[kweaver]

Look at my post. Mentor didn't mean =B2/C2 but should have been as my post indicated ( =B2/A2 ). That's probably why you're getting the error.

 

[Mentor82]

@kweaver is roght. I meant =B2/A2 to place it in C2.
As far as it comes to the conditional formatting with icons set you gave to set:
->first icon from top , value is, value: >= 20 , type: percentage
-> second icon from top, when <20 and, value:>=10, type: percentage
-> third icon from top, when <10

 

[R0nseal]

@kweaver is roght. I meant =B2/A2 to place it in C2.
As far as it comes to the conditional formatting with icons set you gave to set:
->first icon from top , value is, value: >= 20 , type: percentage
-> second icon from top, when <20 and, value:>=10, type: percentage
-> third icon from top, when <10

Thanks, I have tried this, but no matter the result, its coming back as one icon set, as if the % is always over 20% and comes back red?