# Traffic light warning system

#### R0nseal

##### Board Regular
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

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
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
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
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...

 Income Expenses 1000 100

<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
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
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
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
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
@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
@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?

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...