Traffic light warning system

R0nseal

Board Regular
Joined
Aug 25, 2015
Messages
103
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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]
 
Last edited:
Upvote 0
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...

IncomeExpenses
1000100

<tbody>
</tbody>

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
 
Upvote 0
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%
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
@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
 
Upvote 0
@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?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top