"IF" formula with 7 logical tests

decklun

Board Regular
Joined
Dec 4, 2006
Messages
126
Hi All,

Who is ready for a challenge? I feel like I am coming up with a formula from the movie Good Will Hunting. I am trying to get a formula to work that has 7 logical tests. Here is how I would like it to work....

Test 1, if true Test 2, if false Test 4
Test 2, if true Formula, if false Test 3
Test 3, if true Formula, if false formula
Test 4, if true Test 5, if false Test 7
Test 5, if true Formula, if false Test 6
Test 6, if true formula, if false formula
Test 7, if true formula, if false formula

If I break it down into the 7 tests here is what I would like it to do...

Test 1: IF($C$2<F$2,Test 2,Test 4

Test 2: IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,Test 3

Test 3: IF(($B6*SUM($E$4:E$4)+$C6)*FICA_A<FICA_C,(($B6*SUM($E$4:F$4)*FICA_A)-FICA_C)+(($B6*SUM($E$4:F$4))*FICA_M),($B6*F$4)*FICA_M))

After the above, tests 2 and 3 should be complete.

Test 4: IF($C$2=F$2,Test 5,Test 7

Test 5: IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,(($B6*F$4)+C6)*FICA_R,Test 6

Test 6: IF(($B6*SUM($E$4:E$4))*FICA_A<FICA_C,((($B6*SUM($E$4:F$4)+$C6)*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4)+$C6)*FICA_M,(($B6*F$4)+$C6)*FICA_M,)

After the above, test 6 is complete

Test 7: IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,((($B6*SUM($E$4:F$4))*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4))*FICA_M))))

Which then should complete tests 1, 4, 5 and 7

I am having a hard time because I keep getting the "Error" message in formula. Here is the entire formula (get ready)....

=IF($C$2<F$2,IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,IF(($B6*SUM($E$4:E$4)+$C6)*FICA_A<FICA_C,(($B6*SUM($E$4:F$4)*FICA_A)-FICA_C)+(($B6*SUM($E$4:F$4))*FICA_M),($B6*F$4)*FICA_M))IF($C$2=F$2,IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,(($B6*F$4)+$C6)*FICA_R,IF(($B6*SUM($E$4:E$4))*FICA_A<FICA_C,((($B6*SUM($E$4:F$4)+$C6)*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4)+$C6)*FICA_M,(($B6*F$4)+$C6)*FICA_M)IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,((($B6*SUM($E$4:F$4))*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4))*FICA_M))))

I think that my problem is somehow in the open and shut parenthesis. Or maybe I am unable to skip from test 2 to 4?

If anyone has an ideas I could really use some advice. If this formula is way to confusing (which it is for sure driving me crazy), and more information is needed from me, please let me know!

Thanks for reading!!
decklun
 

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.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
decklun:
You want to be careful with formulas that have less than signs in them. HTML uses these for its tags, putting the formulas in Code tags will allow it to show, I think I got them all, so here is your post, so people can see it:

Hi All,

Who is ready for a challenge? I feel like I am coming up with a formula from the movie Good Will Hunting. I am trying to get a formula to work that has 7 logical tests. Here is how I would like it to work....

Test 1, if true Test 2, if false Test 4
Test 2, if true Formula, if false Test 3
Test 3, if true Formula, if false formula
Test 4, if true Test 5, if false Test 7
Test 5, if true Formula, if false Test 6
Test 6, if true formula, if false formula
Test 7, if true formula, if false formula

If I break it down into the 7 tests here is what I would like it to do...

Test 1:
Code:
IF($C$2<F$2,Test 2,Test 4

Test 2:
Code:
IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,Test 3

Test 3:
Code:
IF(($B6*SUM($E$4:E$4)+$C6)*FICA_A<FICA_C,(($B6*SUM($E$4:F$4)*FICA_A)-FICA_C)+(($B6*SUM($E$4:F$4))*FICA_M),($B6*F$4)*FICA_M))

After the above, tests 2 and 3 should be complete.

Test 4
Code:
: IF($C$2=F$2,Test 5,Test 7

Test 5:
Code:
IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,(($B6*F$4)+C6)*FICA_R,Test 6

Test 6:
Code:
IF(($B6*SUM($E$4:E$4))*FICA_A<FICA_C,((($B6*SUM($E$4:F$4)+$C6)*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4)+$C6)*FICA_M,(($B6*F$4)+$C6)*FICA_M,)

After the above, test 6 is complete

Test 7:
Code:
IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,((($B6*SUM($E$4:F$4))*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4))*FICA_M))))

Which then should complete tests 1, 4, 5 and 7

I am having a hard time because I keep getting the "Error" message in formula. Here is the entire formula (get ready)....

Code:
=IF($C$2<F$2,IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,IF(($B6*SUM($E$4:E$4)+$C6)*FICA_A<FICA_C,(($B6*SUM($E$4:F$4)*FICA_A)-FICA_C)+(($B6*SUM($E$4:F$4))*FICA_M),($B6*F$4)*FICA_M))IF($C$2=F$2,IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,(($B6*F$4)+$C6)*FICA_R,IF(($B6*SUM($E$4:E$4))*FICA_A<FICA_C,((($B6*SUM($E$4:F$4)+$C6)*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4)+$C6)*FICA_M,(($B6*F$4)+$C6)*FICA_M)IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,((($B6*SUM($E$4:F$4))*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4))*FICA_M))))

I think that my problem is somehow in the open and shut parenthesis. Or maybe I am unable to skip from test 2 to 4?

If anyone has an ideas I could really use some advice. If this formula is way to confusing (which it is for sure driving me crazy), and more information is needed from me, please let me know!

Thanks for reading!!
decklun
 

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
Please use code brakets like this
Code:
[code]=IF($C$2<F$2,1,0)
[/code] in the future so that we can read your entire formulas (look at your post, some of the formulas have disapeared!)


Code:
=IF($C$2<F$2,IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,IF(($B6*SUM($E$4:E$4)+$C6)*FICA_A<FICA_C,(($B6*SUM($E$4:F$4)*FICA_A)-FICA_C)+(($B6*SUM($E$4:F$4))*FICA_M),($B6*F$4)*FICA_M)),IF($C$2=F$2,IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,(($B6*F$4)+C6)*FICA_R,IF(($B6*SUM($E$4:E$4))*FICA_A<FICA_C,((($B6*SUM($E$4:F$4)+$C6)*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4)+$C6)*FICA_M,(($B6*F$4)+$C6)*FICA_M)),IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,((($B6*SUM($E$4:F$4))*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4))*FICA_M)))

This may be it, but you might want to do a bit of testing to make sure its right.

When I am writing huge pieces of code like this (well I don't like to since it makes it hard to change something later or for coworkers to double check or to follow... its probably best to use multiple columns and then hide the ones that only this depends on) I would right each statement out correctly (with all ")" and "(") like this
Code:
IF($C$2<F$2,0,0)
Then I will write and test the TRUE statement in a different cell, and when I get it right I copy the formula and paste it over the first "0" in the first formula so that I know that my TRUE statement is complete and has its ")" and "(" in all the right places.


HTH,
~Gold Fish
 

decklun

Board Regular
Joined
Dec 4, 2006
Messages
126
Hi Goldfish and Hotpepper. Thanks for pointing it out that I have to put formulas in the "code" format. Here is the entire formula (that is not working)

Code:
=IF($C$2=F$2,IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,(($B6*F$4)+$C6)*FICA_R,IF(($B6*SUM($E$4:E$4))*FICA_A<FICA_C,((($B6*SUM($E$4:F$4)+$C6)*FICA_A)-FICA_C)+(($B6*SUM($E$4:F$4)+$C6)*FICA_M),(($B6*F$4)+$C6)*FICA_M)),if($C$2=F$2,IF(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,if(($B6*SUM($E$4:E$4))*FICA_A<FICA_C,((($B6*SUM($E$4:F$4)+$C6)*FICA_A-FICA_C)+(($B6*SUM($E$4:F$4)+$C6)*FICA_M)),(($B6*F$4)+$C6)*FICA_M)if(($B6*SUM($E$4:F$4))*FICA_A<FICA_C,($B6*F$4)*FICA_R,((($B6*SUM($E$4:F$4))*FICA_A)-FICA_C)+($B6*SUM($E$4:F$4))*FICA_M))))

I will play around with all of the individual formulas until I can combine. Thanks for the advice and I will post back if I am still having problems.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Is there not too many logical results being looked at?
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

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
Top