# "IF" formula with 7 logical tests

#### decklun

##### Board Regular
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!

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
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!

decklun

#### goldfish

##### Well-known Member
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
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
Is there not too many logical results being looked at?

Replies
13
Views
267
Replies
1
Views
190
Replies
19
Views
573
Replies
0
Views
185
Replies
5
Views
182

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.

### Which adblocker are you using?

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

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