Dynamic IF Formula Based On Percentage & Absolute Value

X

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
531
Office Version
  1. 2010
Platform
  1. Windows
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
6SO Processing Leadtime(Days)21.36Target Achieved
7%Productivity(SO)95%100%Target Achieved
8%Picking Efficiency(SO)98%99%Target Achieved
9STO Processing Leadtime(Days)21.43Target 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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
 
Upvote 0
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"))
 
Upvote 0
Really amazing & mesmerising solution!!
Thank you so much for your support & input.
Really appreciate the same..:):)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jimmypop
  • Solved
Scatter Plot Chart not showing values from cell formula...
Replies
3
Views
869
felixstraube
F
M
  • Question Question
Comparing (comma seperated) string values between two columns
2
Replies
14
Views
452
FormR
FormR
K
  • Solved
COUNTIFS formula.
2
Replies
14
Views
685
Kayslover
K
SaraWitch
  • Solved
Return values based on multiple cells
Replies
4
Views
480
etaf
etaf
F
  • Solved
AverageIF less than 15 including values under 0 (negative numbers)
Replies
5
Views
560
Fluff
Fluff

Forum statistics

Threads
1,226,246
Messages
6,189,860
Members
453,575
Latest member
Taljanin

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