Find percentage difference between two numbers and according add column

D

divyaquamara

Board Regular
Joined
Jun 27, 2011
Messages
67
Hi,
I have data in which column
AB AC ADhave following data
AB AC AD
<TABLE style="WIDTH: 177pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=236 border=0 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=83 height=17>YTD Budget</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>YTD Actuals</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:str="Status ">Status </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>7</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
We have to use the formula :
[ |AB-AC|/AC ] *100
If this gives value less than or equal to 10% we insert Green in column AD.
If this gives value greater Than 10% and less than or equal to 20 % we insert Amber in column AD.
If this gives value greater than 20% we insert Red in column AD.
I have to write a macro to do this.
Please do guide.
Thanks in advance.
 
Sort by date Sort by votes
Hi
Thanks a lot. This runs perfectly on my data.
But I have a question , I will be greatful if you can explain to me this line:

myFormula = "=IF(RC[-2]="""","""",LOOKUP(ABS(RC[-2]-RC[-1])/RC[-1],{0,""Green"";0.101,""Amber"";0.201,""Red""}))"
Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = myFormula

Also as compared to ur previous code why did you choose to not directly use the column name while putting the formulas.

It will great if you can explain me this.
Thanks in advance.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi,
I am sorry for troubling you again but I have a change in formulas.
It should be as follows:
If YTD Actuals <= YTD Budget .............Green
When YTD Actuals > YTD Budget .....(uptil 5% difference) .........Green
i.e. [(YTD Actuals- YTD Budget)/ YTD Budget]*100

When YTD Actuals > YTD Budget .....(between 5 to 10%)................Amber

When YTD Actuals > YTD Budget .....(more than 10%)................Red

I am sorry for bothering you again But please do help.
Thanks in advance
 
Upvote 0
divyaquamara said:
Hi,
I am sorry for troubling you again but I have a change in formulas.
It should be as follows:
If YTD Actuals <= YTD Budget .............Green
When YTD Actuals > YTD Budget .....(uptil 5% difference) .........Green
i.e. [(YTD Actuals- YTD Budget)/ YTD Budget]*100

When YTD Actuals > YTD Budget .....(between 5 to 10%)................Amber

When YTD Actuals > YTD Budget .....(more than 10%)................Red

I am sorry for bothering you again But please do help.
Thanks in advance
Click to expand...

Divyaquamara,

I used the R1C1 style, not A1 style (default Excel in interactive mode) on the last macro to facilitate. Because when I use the R1C1 style I don't need to know what are the column with the data (E/F or AK/AL), I just need to know that their columns are to the left of the formula column (a column to the left -1 and two columns to the left -2).

For more details about the R1C1 reference, I suggest reading the chapter 6 of the book VBA and Macros: Microsoft Excel 2010.

Try this:

Code: 
Sub CreateStatusR1C1()
   Application.ScreenUpdating = False
    Sheets("Budget").Select
    
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells(2, LastCol + 1).Value = "Status"
    myFormula = "=IF(RC[-2]="""","""",LOOKUP((RC[-1]-RC[-2])/RC[-2]," & _
        "{-1E+300,""Green"";0.001,""Green"";0.051,""Amber"";0.101,""Red""}))"
    Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = myFormula
    
    Cells(1, LastCol + 1).EntireColumn.AutoFit
    
   Application.ScreenUpdating = True
End Sub

Result:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>AK</th><th>AL</th><th>AM</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">Sr.no</td><td style="font-weight: bold;text-align: center;;">Project No.</td><td style="font-weight: bold;text-align: center;;">Lead </td><td style="font-weight: bold;text-align: center;;">Asst Lead</td><td style="font-weight: bold;text-align: center;;">YTD Budget</td><td style="font-weight: bold;text-align: center;;">YTD Actuals</td><td style=";">Status</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">A1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">3.6</td><td style="text-align: center;;">4</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">B1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">D1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">E1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">99</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">6</td><td style="text-align: center;;">F1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">100</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">G1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">105</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">8</td><td style="text-align: center;;">H1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">106</td><td style=";">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">9</td><td style="text-align: center;;">I1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">110</td><td style=";">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">10</td><td style="text-align: center;;">J1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">111</td><td style=";">Red</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Budget</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AM3</th><td style="text-align:left">=IF(<font color="Blue">AK3="","",LOOKUP(<font color="Red">(<font color="Green">AL3-AK3</font>)/AK3,{-1E+300,"Green";0.001,"Green";0.051,"Amber";0.101,"Red"}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
 
Upvote 0
Hi
The above works good but when it encounters data as 0 in the columns it gives an error.
See below column 11 and 12.
How can this be avoided?
Thanks a lot.

<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>AK</TH><TH>AL</TH><TH>AM</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sr.no</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Project No.</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Lead </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Asst Lead</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">YTD Budget</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">YTD Actuals</TD><TD>Status</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">A1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">3.6</TD><TD style="TEXT-ALIGN: center">4</TD><TD>Red</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">B1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">C1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">7</TD><TD>Red</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">D1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD><TD>Red</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">E1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">99</TD><TD>Green</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">F1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">100</TD><TD>Green</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">G1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">105</TD><TD>Green</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">H1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">106</TD><TD>Amber</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">I1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">110</TD><TD><TABLE style="WIDTH: 37pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=49 border=0 x:str><COLGROUP><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle width=49 height=17 x:err="#DIV/0!">#DIV/0!</TD></TR></TBODY></TABLE></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">J1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD><TABLE style="WIDTH: 37pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=49 border=0 x:str><COLGROUP><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle width=49 height=17 x:err="#DIV/0!">#DIV/0!</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
Try this:

Code: 
Sub CreateStatusR1C1()
   Application.ScreenUpdating = False
    Sheets("Budget").Select
    
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells(2, LastCol + 1).Value = "Status"
    myFormula = "=IF(OR(RC[-2]={"""",0},RC[-1]={"""",0}),"""",LOOKUP((RC[-1]-RC[-2])/RC[-2]," & _
        "{-1E+300,""Green"";0.001,""Green"";0.051,""Amber"";0.101,""Red""}))"
    Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = myFormula
    
    Cells(1, LastCol + 1).EntireColumn.AutoFit
    
   Application.ScreenUpdating = True
End Sub

Result

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>AK</th><th>AL</th><th>AM</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">Sr.no</td><td style="font-weight: bold;text-align: center;;">Project No.</td><td style="font-weight: bold;text-align: center;;">Lead </td><td style="font-weight: bold;text-align: center;;">Asst Lead</td><td style="font-weight: bold;text-align: center;;">YTD Budget</td><td style="font-weight: bold;text-align: center;;">YTD Actuals</td><td style=";">Status</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">A1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">3.6</td><td style="text-align: center;;">4</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">B1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">D1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">E1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">99</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">6</td><td style="text-align: center;;">F1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">100</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">G1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">105</td><td style=";">Green</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">8</td><td style="text-align: center;;">H1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">106</td><td style=";">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">9</td><td style="text-align: center;;">I1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">110</td><td style=";">Amber</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">10</td><td style="text-align: center;;">J1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">111</td><td style=";">Red</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">11</td><td style="text-align: center;;">J2</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">0</td><td style="text-align: center;;">110</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">12</td><td style="text-align: center;;">J3</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Budget</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AK4</th><td style="text-align:left">=""</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AM3</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">AK3={"",0},AL3={"",0}</font>),"",LOOKUP(<font color="Red">(<font color="Green">AL3-AK3</font>)/AK3,{-1E+300,"Green";0.001,"Green";0.051,"Amber";0.101,"Red"}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
Hi,
I still dont seem to get it right .
I think I should re - explain.

The formula:
LOOKUP((RC[-1]-RC[-2])/RC[-2],"

should be:
LOOKUP((RC[-1]-RC[-2])/RC[-1],"

then

<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>AK</TH><TH>AL</TH><TH>AM</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sr.no</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Project No.</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Lead </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Asst Lead</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">YTD Budget</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">YTD Actuals</TD><TD>Status</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">A1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">3.6</TD><TD style="TEXT-ALIGN: center">4</TD><TD>Red</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">B1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">C1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">7</TD><TD>Red</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">D1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Green</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">E1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">7</TD><TD>Red</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">F1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">100</TD><TD>Green</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">G1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">105</TD><TD>Green</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">H1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">106</TD><TD>Amber</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">I1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">110</TD><TD>Amber</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">J1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">111</TD><TD>Red</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">J2</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">110</TD><TD>Red</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">J3</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR></TBODY></TABLE>

I have bold the rows where the changes should look like.

I appreciate your help.
Please do guide.
Thanks again
 
Upvote 0
divyaquamara said:
Hi,
I still dont seem to get it right .
I think I should re - explain.

The formula:
LOOKUP((RC[-1]-RC[-2])/RC[-2],"

should be:
LOOKUP((RC[-1]-RC[-2])/RC[-1],"

then

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>AK</TH><TH>AL</TH><TH>AM</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Sr.no</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Project No.</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Lead </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Asst Lead</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">YTD Budget</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">YTD Actuals</TD><TD>Status</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">A1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">3.6</TD><TD style="TEXT-ALIGN: center">4</TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">B1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">C1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">7</TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">D1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">E1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">7</TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">F1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">100</TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">G1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">105</TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">H1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">106</TD><TD>Amber</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">I1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">110</TD><TD>Amber</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">J1</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">111</TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">J2</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">110</TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">J3</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">xx</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR></TBODY></TABLE>

I have bold the rows where the changes should look like.

I appreciate your help.
Please do guide.
Thanks again
Click to expand...

Divyaquamara,

I created the formula of post #13 following guidelines to yours post #12.

My formula of post #15 is following guidelines to yours post #14.

To resolve the problems presented in post #16, do the following:

Note1: the result of cell AM3 (Red) in post #16 is not in accordance with your guidelines. It should be Amber, as the result of the formula is equal to 10% and not greater than 10%.

Note2: the result of cell AM12 (Red) in post #16 is not in accordance with your guidelines. It should be Amber, as the result of the formula is less than 10% and not greater than 10%.

Code: 
Sub CreateStatusR1C1()
   Application.ScreenUpdating = False
    Sheets("Budget").Select
    
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells(2, LastCol + 1).Value = "Status"
    myFormula = "=IF((RC[-2]=0)*(RC[-1]=0),"""",IF(RC[-1]=0,""Green"",LOOKUP((RC[-1]-RC[-2])/RC[-1]," & _
        "{-1E+300,""Green"";0.001,""Green"";0.051,""Amber"";0.101,""Red""})))"
    Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = myFormula
    
    Cells(1, LastCol + 1).EntireColumn.AutoFit
    
   Application.ScreenUpdating = True
End Sub

Results:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>AK</th><th>AL</th><th>AM</th><th>AN</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">Sr.no</td><td style="font-weight: bold;text-align: center;;">Project No.</td><td style="font-weight: bold;text-align: center;;">Lead </td><td style="font-weight: bold;text-align: center;;">Asst Lead</td><td style="font-weight: bold;text-align: center;;">YTD Budget</td><td style="font-weight: bold;text-align: center;;">YTD Actuals</td><td style=";">Status</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">A1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">3.6</td><td style="text-align: center;;">4</td><td style="background-color: #FF0000;;">Amber</td><td style="text-align: right;background-color: #FF0000;;">10.00%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">B1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style=";">Red</td><td style="text-align: right;;">14.29%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">4</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">D1</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">xx</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">xx</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">7</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">0</td><td style="font-weight: bold;background-color: #FFFF00;;">Green</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">5</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">E1</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">xx</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">xx</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">0</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">7</td><td style="font-weight: bold;background-color: #FFFF00;;">Red</td><td style="font-weight: bold;text-align: right;;">100.00%</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">6</td><td style="text-align: center;;">F1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">100</td><td style=";">Green</td><td style="text-align: right;;">0.00%</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">G1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">105</td><td style=";">Green</td><td style="text-align: right;;">4.76%</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">8</td><td style="text-align: center;;">H1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">106</td><td style=";">Amber</td><td style="text-align: right;;">5.66%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">9</td><td style="text-align: center;;">I1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">110</td><td style=";">Amber</td><td style="text-align: right;;">9.09%</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">10</td><td style="text-align: center;;">J1</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">100</td><td style="text-align: center;;">111</td><td style="background-color: #FF0000;;">Amber</td><td style="text-align: right;background-color: #FF0000;;">9.91%</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">11</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">J2</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">xx</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">xx</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">0</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">110</td><td style="font-weight: bold;background-color: #FFFF00;;">Red</td><td style="font-weight: bold;text-align: right;;">100.00%</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">12</td><td style="text-align: center;;">J3</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">xx</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style=";"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Budget</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AN5</th><td style="text-align:left">=(<font color="Blue">AL5-AK5</font>)/AL5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AN7</th><td style="text-align:left">=(<font color="Blue">AL7-AK7</font>)/AL7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AM3</th><td style="text-align:left">=IF(<font color="Blue">(<font color="Red">AK3=0</font>)*(<font color="Red">AL3=0</font>),"",IF(<font color="Red">AL3=0,"Green",LOOKUP(<font color="Green">(<font color="Purple">AL3-AK3</font>)/AL3,{-1E+300,"Green";0.001,"Green";0.051,"Amber";0.101,"Red"}</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AN3</th><td style="text-align:left">=(<font color="Blue">AL3-AK3</font>)/AL3</td></tr></tbody></table></td></tr></table><br />
If not already correct, I would ask that you put all the possibilities for the columns AL and AK and what you want as a result for each one.

Markmzz
 
Upvote 0
Hi Markmzz,
Sorry for all the trouble I gave you.
This works perfectly. Thanks a lot lot for your patience.
Thanks a ton.
Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,590
Messages
6,179,750
Members
452,940
Latest member
rootytrip

Share this page

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