# Dynamic IF Formula Based On Percentage & Absolute Value

#### xlmaniac

##### Well-known Member
Dear All,
I do have a target vs achievement matrix as follows spread across A1:D12.
To derive the achievement indicator(Column D),I have put the IF statement manually across D2:D12.
There are 2 different targets-(a)Absolute Value(b)Percentage
The formulae for absolute value(target Vs achivement) is different from that of percentage(target vs achievement).
For example the Formula in D2(For absolutele value)=IF(C2<=B2,"Target Achieved","Target Not Achieved")
D3(For Percentage)=IF(C3>=B3,"Target Achieved","Target Not Achieved")
Presently I have changed the formula(based on absolute value & percentage) manually.
I am looking at a formula which can evalute whether the values in Column B is absoulute or percentage and then the same formula should return the desired result dynamically in COLUMN D.
For example since the value in cell B2 is 2 therefore the desired formula in D2 should consider the actual achievement value(1.35) in cell C2 and write the formula in D2=IF(C2<=B2,"Target Achieved","Target Not Achieved").
Similarly the value in cell B3 is in % form(97%) therefore the desired formula in D3 should consider the actual achievement value(100%) in cell C3 and write the formula in D3=IF(C3>=B3,"Target Achieved","Target Not Achieved")
Is it possible?
Excel Workbook
ABCD
1ParticularsTarget(Avg)Actual(Jun11-Oct11)Achievement Indicator
2Completion Of GRN(Days)21.35Target Achieved
3%Productivity(GRN)97%100%Target Achieved
4Completion Of RL/GRWM(Days)42.55Target Achieved
5%RL/GRWM Productivity95%99%Target Achieved
7%Productivity(SO)95%100%Target Achieved
8%Picking Efficiency(SO)98%99%Target Achieved
10%Productivity(STO)95%99%Target Achieved
11%Picking Efficiency(STO)98%99%Target Achieved
12% Logistics Productivity85%66%Target Not Achieved
Sheet1

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Dear All,
I have written a new formulae in COLUMN E which is yielding correct result but when the % achievement is 100%,it is giving incorrect results in E3 & E7.
Could somebody help me in tweaking this formula so that this issue could be resolved?
Thanks
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 209px"><COL style="WIDTH: 90px"><COL style="WIDTH: 77px"><COL style="WIDTH: 180px"><COL style="WIDTH: 138px"><COL style="WIDTH: 229px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 35px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; COLOR: #333399; FONT-WEIGHT: bold">Particulars</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; COLOR: #333399; FONT-WEIGHT: bold">Target(Avg)</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; COLOR: #333399; FONT-WEIGHT: bold">Actual(Jun11-Oct11)</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; COLOR: #333399; FONT-WEIGHT: bold">Achievement Indicator</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-WEIGHT: bold">New Formula</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-WEIGHT: bold">Desired Result(With New Formula)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">Completion Of GRN(Days)</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">1.35</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Achieved</TD><TD>OK</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">%Productivity(GRN)</TD><TD style="TEXT-ALIGN: center">97%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">100%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Not Achieved</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">NOT OK.</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">Completion Of RL/GRWM(Days)</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">2.55</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Achieved</TD><TD>OK</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">%RL/GRWM Productivity</TD><TD style="TEXT-ALIGN: center">95%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">99%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Achieved</TD><TD>OK</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">SO Processing Leadtime(Days)</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">1.36</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Achieved</TD><TD>OK</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">%Productivity(SO)</TD><TD style="TEXT-ALIGN: center">95%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">100%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Not Achieved</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">NOT OK.</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">%Picking Efficiency(SO)</TD><TD style="TEXT-ALIGN: center">98%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">99%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Achieved</TD><TD>OK</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">STO Processing Leadtime(Days)</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">1.43</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Achieved</TD><TD>OK</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center">%Productivity(STO)</TD><TD style="TEXT-ALIGN: center">95%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">99%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Achieved</TD><TD>OK</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">%Picking Efficiency(STO)</TD><TD style="TEXT-ALIGN: center">98%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">99%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Achieved</TD><TD>OK</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">% Logistics Productivity</TD><TD style="TEXT-ALIGN: center">85%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">66%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">Target Not Achieved</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target Not Achieved</TD><TD>OK</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=IF(C2<=B2,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E2</TD><TD>=IF(OR(AND(INT(B2)=0,INT(C2)=0,C2>=B2),AND(INT(B2),INT(C2),C2<=B2)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D3</TD><TD>=IF(C3>=B3,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E3</TD><TD>=IF(OR(AND(INT(B3)=0,INT(C3)=0,C3>=B3),AND(INT(B3),INT(C3),C3<=B3)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D4</TD><TD>=IF(C4<=B4,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E4</TD><TD>=IF(OR(AND(INT(B4)=0,INT(C4)=0,C4>=B4),AND(INT(B4),INT(C4),C4<=B4)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D5</TD><TD>=IF(C5>=B5,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E5</TD><TD>=IF(OR(AND(INT(B5)=0,INT(C5)=0,C5>=B5),AND(INT(B5),INT(C5),C5<=B5)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D6</TD><TD>=IF(C6<=B6,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E6</TD><TD>=IF(OR(AND(INT(B6)=0,INT(C6)=0,C6>=B6),AND(INT(B6),INT(C6),C6<=B6)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D7</TD><TD>=IF(C7>=B7,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E7</TD><TD>=IF(OR(AND(INT(B7)=0,INT(C7)=0,C7>=B7),AND(INT(B7),INT(C7),C7<=B7)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D8</TD><TD>=IF(C8>=B8,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E8</TD><TD>=IF(OR(AND(INT(B8)=0,INT(C8)=0,C8>=B8),AND(INT(B8),INT(C8),C8<=B8)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D9</TD><TD>=IF(C9<=B9,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E9</TD><TD>=IF(OR(AND(INT(B9)=0,INT(C9)=0,C9>=B9),AND(INT(B9),INT(C9),C9<=B9)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D10</TD><TD>=IF(C10>=B10,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E10</TD><TD>=IF(OR(AND(INT(B10)=0,INT(C10)=0,C10>=B10),AND(INT(B10),INT(C10),C10<=B10)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D11</TD><TD>=IF(C11>=B11,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E11</TD><TD>=IF(OR(AND(INT(B11)=0,INT(C11)=0,C11>=B11),AND(INT(B11),INT(C11),C11<=B11)),"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>D12</TD><TD>=IF(C12>=B12,"Target Achieved","Target Not Achieved")</TD></TR><TR><TD>E12</TD><TD>=IF(OR(AND(INT(B12)=0,INT(C12)=0,C12>=B12),AND(INT(B12),INT(C12),C12<=B12)),"Target Achieved","Target Not Achieved")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Try the following formula in D2

=IF(CELL("format",B2)="P0",IF(C2>=B2,"Target Achieved","Target Not Achieved"),IF(C2<=B2,"Target Achieved","Target Not Achieved"))

Really amazing & mesmerising solution!!
Thank you so much for your support & input.
Really appreciate the same..

Replies
7
Views
302
Replies
6
Views
476
Replies
0
Views
209
Replies
0
Views
331
Replies
1
Views
321

### Forum statistics

1,207,198
Messages
6,077,018
Members
446,250
Latest member
Dontcomehereoften

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

### Which adblocker are you using?

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

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