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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307
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

Well-known Member
Joined
May 8, 2018
Messages
1,076
Office Version
365, 2010
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:

R0nseal

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

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,076
Office Version
365, 2010
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

Active Member
Joined
Dec 30, 2018
Messages
307
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

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

Well-known Member
Joined
May 8, 2018
Messages
1,076
Office Version
365, 2010
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:

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307
@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

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

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,822
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top