Shortening IF formulas

C

ChrisCusick

New Member
Joined
Mar 27, 2012
Messages
14
I am not sure if this has already been posted somewhere, as I could not see anything similar. So apologies if I am asking a repeated question.

I am trying to use a nested IF formula to return certain results in a cell, but the problem is that I am being told the formula is too long.

Is there an easier way to enter this information so it will all fit? I have copied the formula below.

=IF(D15="PG",55,IF(D15="DPG",90,IF(D15="-PG",-55,IF(D15="-DPG",-90,IF(D15="SPG",80,IF(D15="DSPG",140,IF(D15="-SPG",-55,IF(D15="-DSPG",-140,IF(D15="ATP",95,IF(D15="DATP",170,IF(D15="-ATP",-95,IF(D15="-DATP",-170,0))))))))))

As you can see it is a heck of a lot of IF's, but this was the way I was taught to do them. Unfortunately Excel doesn't seem to like this many :mad:

Please help!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Use VLOOKUP:
Like this:

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>D</th><th>E</th><th>F</th><th>G</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=";">pg</td><td style="text-align: right;;">55</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style=";">dpg</td><td style="text-align: right;;">90</td></tr><tr><td style="color: #161120;text-align: center;">3</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;">4</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;">5</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;">6</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;">7</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;">8</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;">9</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;">10</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;">11</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;">12</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;">13</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;">14</td><td style="text-align: right;;">55</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;">15</td><td style=";">pg</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>
Sheet13​


<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">D14</th><td style="text-align:left">=VLOOKUP(D15,$F$1:$G$2,2,0)</td></tr></tbody></table></td></tr></tbody></table>
 
Upvote 0
Thanks for the information regarding VLOOKUP but I am not sure how this would help, but maybe I am just not thinking right.

The cell that the IF function looks at is manually entered by a user (So the ATP,SPG etc etc) and the IF function would then return the relevant value.

So would the VLOOKUP work and return the correct responses?
 
Upvote 0
Another approach:

=LOOKUP(D15,{"ATP","-ATP","DATP","-DATP","DPG","-DPG","DSPG","-DSPG","PG","-PG","SPG","-SPG"},{95,-95,170,-170,90,-90,140,-140,55,-55,80,-55})

As an aside: Is the value for -SPG correct at -55? It is inconsistent with the rest of the relationships - I would expect it to be -80. If that is correct, then the formula above needs to be corrected (the -55 at the end of the formula should be changed to -80).
 
Upvote 0
Jeff Meyers said:
Another approach:

=LOOKUP(D15,{"ATP","-ATP","DATP","-DATP","DPG","-DPG","DSPG","-DSPG","PG","-PG","SPG","-SPG"},{95,-95,170,-170,90,-90,140,-140,55,-55,80,-55})

As an aside: Is the value for -SPG correct at -55? It is inconsistent with the rest of the relationships - I would expect it to be -80. If that is correct, then the formula above needs to be corrected (the -55 at the end of the formula should be changed to -80).
Click to expand...

VLOOKUP would be more appropriate here.
 
Upvote 0
ChrisCusick said:
Thanks for the information regarding VLOOKUP but I am not sure how this would help, but maybe I am just not thinking right.

The cell that the IF function looks at is manually entered by a user (So the ATP,SPG etc etc) and the IF function would then return the relevant value.

So would the VLOOKUP work and return the correct responses?
Click to expand...

Yes.
Look at example I have posted.Extend the lookup table as you need.
You are lucky enough to work in Excel 07/10.In 03 you would be not allowed to use so many IF ststments.
 
Upvote 0
Robert Mika said:
Yes.
Look at example I have posted.Extend the lookup table as you need.
You are lucky enough to work in Excel 07/10.In 03 you would be not allowed to use so many IF ststments.
Click to expand...

You are, quite obviously, correct and please forgive the ignorance on my part :eeek:

One small question though: I have posted the vlookup formula below but for empty cells it is returning #N/A. I need the value to be blank but I cannot figure out how to do this...yes I am feeling a right dunce at the moment.

=VLOOKUP(D15,Sheet2!$A$1:$B$12,2,0)

I have tried entering "0" and "" into the field but it still comes up the same. I have also tried extending the table to include an extra line (so that the reference would be, in my mind, "blank cell=blank response") but that doesn't work either.

What am I missing?

I just looked up some info on Vlookup and this #N/A is normal for a blank cell...am I right in saying that this cannot be changed to give a blank response (IE blank in Cell D15, blank in the response cell)
 
Last edited:
Upvote 0
Not to worry about the #N/A, I figured a way around it by using an =IFERROR.

But thank you all for your help and input on this, it has saved my brain from melting out of my ears :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
finding changes and looking back
2
Replies
10
Views
411
felixstraube
F
A
  • Question Question
Daily Interest Loan Scheduler | Missed Payments
Replies
0
Views
231
aurelius33
A
H
  • Solved
Grouping rows with the same values into columns "B" and "C"
Replies
5
Views
581
harzer
H
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
281
Cquake
C
L
  • Solved
Need A Help With My formula
Replies
3
Views
212
Cubist
Cubist

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

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