Nested IF Statements vs. VLookup or Index lookups

SRMPURCHASE

Board Regular
Joined
Dec 23, 2014
Messages
212
Office Version
  1. 2016
Platform
  1. Windows
I have this nested IF formula that does not work, a constant keeps the formula from working. Then to work around the constant I tried an Index lookup as well that does not work.
Col S is typed in to designate a type of steel I need the weight per foot or square foot to be calculated.
W = Qty
Y =Thickness or wt per foot
Z = short side of steel
AA = long side of steel
AB = length of steel

Do I have all these arguments nested correctly? As I copy down the formula the rows change to correspond with the type of steel with dimensions is listed
If I use an Index/Vlookup formula how do I tell the formula to change to the corresponding row (some kind of wildcard for each row copied down).



=IF(S25=2,(Y25*Z25)+(AA25-Y25)*Y25*3.4032,0,IF(S25=3,(Z25*AA25*3.4032),0,IF(S25=4,(Y25*(AB25/12)*W25,0,IF(S25=5(Y25*AB25/12*W25,0,IF(S25=6,(Y25*(Z25*AA25/144)*W25,0,IF(S25=7,(Y25*(Z25*AA25/144)*W25,0,IF(S25=8,(Y25*(Z25*AA25/144)*W25,0,IF(S25=9,(Y25*Z25*3.4032,0,IF(S25=12,(Y25*Z25*ZZ25*.2836,0,IF(S25=13(Y25*Z25*AA25*.2836,0,IF(S25=14,(Y25*(Z25-Y25)*3.4032)+(Y25*AA25)*3.4032,0,IF(S25=16,(Z25-Y25)*Y25*4*.283,0)
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try this -

Excel Formula:
=IF(S25=2,(Y25*Z25)+(AA25-Y25)*Y25*3.4032,
IF(S25=3,(Z25*AA25*3.4032),
IF(S25=4,(Y25*(AB25/12)*W25,
IF(S25=5,(Y25*AB25/12)*W25,
IF(S25=6,(Y25*(Z25*AA25/144)*W25,
IF(S25=7,(Y25*(Z25*AA25/144)*W25,
IF(S25=8,(Y25*(Z25*AA25/144)*W25,
IF(S25=9,(Y25*Z25*3.4032),
IF(S25=12,(Y25*Z25*ZZ25*.2836),
IF(S25=13,(Y25*Z25*AA25*.2836),
IF(S25=14,(Y25*(Z25-Y25)*3.4032)+(Y25*AA25)*3.4032,
IF(S25=16,(Z25-Y25)*Y25*4*.283,0))))))))))))
 
Upvote 0
Try this -

Excel Formula:
=IF(S25=2,(Y25*Z25)+(AA25-Y25)*Y25*3.4032,
IF(S25=3,(Z25*AA25*3.4032),
IF(S25=4,(Y25*(AB25/12)*W25,
IF(S25=5,(Y25*AB25/12)*W25,
IF(S25=6,(Y25*(Z25*AA25/144)*W25,
IF(S25=7,(Y25*(Z25*AA25/144)*W25,
IF(S25=8,(Y25*(Z25*AA25/144)*W25,
IF(S25=9,(Y25*Z25*3.4032),
IF(S25=12,(Y25*Z25*ZZ25*.2836),
IF(S25=13,(Y25*Z25*AA25*.2836),
IF(S25=14,(Y25*(Z25-Y25)*3.4032)+(Y25*AA25)*3.4032,
IF(S25=16,(Z25-Y25)*Y25*4*.283,0))))))))))))
I've added the ending parentheses, then tried copy and pasting your answer, still no joy.
I did see and corrected one typo, IF(S25=12 the ZZ25 should be Z25.
What am I missing?
 
Upvote 0
There were too many parentheses to handle -

Try this and must check the accuracy of formula and results for all conditions -

Excel Formula:
=IF(S25=2,(Y25*Z25)+(AA25-Y25)*Y25*3.4032,
IF(S25=3,Z25*AA25*3.4032,
IF(S25=4,Y25*(AB25/12)*W25,
IF(S25=5,Y25*(AB25/12)*W25,
IF(S25=6,Y25*(Z25*AA25/144)*W25,
IF(S25=7,Y25*(Z25*AA25/144)*W25,
IF(S25=8,Y25*(Z25*AA25/144)*W25,
IF(S25=9,Y25*Z25*3.4032,
IF(S25=12,Y25*Z25*Z25*0.2836,
IF(S25=13,Y25*Z25*AA25*0.2836,
IF(S25=14,(Y25*(Z25-Y25)*3.4032)+(Y25*AA25)*3.4032,
IF(S25=16,(Z25-Y25)*Y25*4*0.283,0))))))))))))
 
Upvote 0
There were too many parentheses to handle -

Try this and must check the accuracy of formula and results for all conditions -

Excel Formula:
=IF(S25=2,(Y25*Z25)+(AA25-Y25)*Y25*3.4032,
IF(S25=3,Z25*AA25*3.4032,
IF(S25=4,Y25*(AB25/12)*W25,
IF(S25=5,Y25*(AB25/12)*W25,
IF(S25=6,Y25*(Z25*AA25/144)*W25,
IF(S25=7,Y25*(Z25*AA25/144)*W25,
IF(S25=8,Y25*(Z25*AA25/144)*W25,
IF(S25=9,Y25*Z25*3.4032,
IF(S25=12,Y25*Z25*Z25*0.2836,
IF(S25=13,Y25*Z25*AA25*0.2836,
IF(S25=14,(Y25*(Z25-Y25)*3.4032)+(Y25*AA25)*3.4032,
IF(S25=16,(Z25-Y25)*Y25*4*0.283,0))))))))))))
Are you saying to copy and paste as is with the line breaks, that puts each line in the cell below my intended cell?
 
Upvote 0
Are you saying to copy and paste as is with the line breaks, that puts each line in the cell below my intended cell?
You can do that. It's a good practice to do so when you have large formulae. This way you can easily look for corrections and errors. More significantly it becomes easy to understand at some later stage, if the need be.
 
Upvote 0
that puts each line in the cell below my intended cell?
The line breaks are within the same cell only
Screenshot 2023-10-26 at 22.43.48.png
 
Upvote 0
Just in case you are convenient with one line formula. It's exactly the same as above in #4

Excel Formula:
=IF(S25=2,(Y25*Z25)+(AA25-Y25)*Y25*3.4032,IF(S25=3,Z25*AA25*3.4032,IF(S25=4,Y25*(AB25/12)*W25,IF(S25=5,Y25*(AB25/12)*W25,IF(S25=6,Y25*(Z25*AA25/144)*W25,IF(S25=7,Y25*(Z25*AA25/144)*W25,IF(S25=8,Y25*(Z25*AA25/144)*W25,IF(S25=9,Y25*Z25*3.4032,IF(S25=12,Y25*Z25*Z25*0.2836,IF(S25=13,Y25*Z25*AA25*0.2836,IF(S25=14,(Y25*(Z25-Y25)*3.4032)+(Y25*AA25)*3.4032,IF(S25=16,(Z25-Y25)*Y25*4*0.283,0))))))))))))
 
Upvote 0
Solution

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

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