Nesting IF functions

Nanaia

Active Member
Joined
Jan 11, 2018
Messages
306
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I'm trying to nest IF functions but am not getting the results I need and am hoping I can find some (more) help.
I'm trying to tell Excel that if J84= 7, 8, 11, or 12 then the value in AA2 needs to be multiplied by (0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2)))) (bend allowance formula for a subtended angle). My problem is that if J84 equals 7, 8, 11, or 12 it doesn't multiply anything out, it simply lists the formula. I think I missed something but I can't figure out what was missed or how to fix it. Does anybody have any suggestions?
This is my formula:

=IF(CSV!J84=7,"CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))))",IF(CSV!J84=8,"CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))))",IF(CSV!J84=11,"CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))))",IF(CSV!J84=12,"CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))))",0))))

Regards,
Nanaia
"Do or do not, there is no try"~Yoda

P.S. you folks are awesome. :biggrin:
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
putting "" makes the formula into text to display - so remove the ""

you can use an OR

CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2)))
is the formula you want
IF
CSV!J84=7 or 8, or 11, or 12

so

IF ( OR(
CSV!J84=7, CSV!J84=8, CSV!J84=11, CSV!J84=12), CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))) , 0 )

which can also be shortened to

IF ( OR( CSV!J84={7,8,11,12}), CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))) , 0 )

 
Last edited:
Upvote 0
Hi,

Try this:

=IF(OR(J84=7,J84=8,J84=11,J84=12),(CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2)))),"")
 
Upvote 0
I can't get it to recognize any of your formula suggestions. I removed spaces and it keeps giving me the error "There's a problem with this formula. Not trying to type a formula?...." When I went through it a step at a time it inserted quotes around the 'value if true' section which resulted as the actual formula appearing in the cell instead of the calculation. When I removed the quote's, I got the "There's a problem with this formula" error again. :(
Regards,
Nanaia
"Do or do not, there is no try"~Yoda
 
Upvote 0
just type out the formula in a cell and make sure that os configured correctly
I do get an error - but took the formula you wanted to be correctly formatted

which is this
,"CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))))"
BUT without the ""
becomes
=CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))))
which i get an error on brackets

should it be
=CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2)))))

is that the correct position for the brackets

if so
then change to

=IF(OR(CSV!J84={7,8,11,12}),CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))))),0)

BUT YOU SAY
Not trying to type a formula?...."
why not - isn't that what you want , the formula to be executed
 
Last edited:
Upvote 0
My apologies for the misunderstanding, the part I had in quotes was the error Excel was giving me when I was trying to use the formula, not my own wording.

Your updated formula

=IF(OR(CSV!J84={7,8,11,12}),CSV!AA2*(0.1005*TAN((3.1416/180)*(90-CSV!AA2))-(0.0693*SIN((3.1416/180)*(90-(CSV!AA2/2))))),0)

does give me a value, however it doesn't match what separated calculations say it should be. My apologies if I have worded things poorly.

I figured out why I was stumbling. I was multiplying CSV!AA2 (right after the OR function) when I shouldn't have been. Once I corrected that it worked. If you want to check it out for yourself

CSV!J84 has a value of 7
CSV!AA2 has a value of 90

So the formula should've been

=IF(OR(CSV!J84={7,8,11,12}),(0.1005*TAN((3.1416/180)*(90-(CSV!AA2/2))))-(0.0693*SIN((3.1416/180)*(90-CSV!AA2/2))),0)

which comes out to 0.0515

Thanks for all of your help. I really appreciate it. You've been fantastic!

Regards,
Nanaia
Do or do not, there is no try"~Yoda
 
Upvote 0

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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