Thread: Need help with a nested if function

I have been struggling with a function using a nested IF statement. Here is what I am trying to do.

I have a row of 16 values in my spreadsheet (C5:R5) that represents the results of a series of weekly scores. If the number of values (scores) in the row equals 10 (U5), then deduct the lowest value in the row from the sum of the weekly values. If the number of weekly values =11, then deduct the 2 lowest. If the number of weekly values >=12, then deduct the 3 lowest.

This is the nested IF function I am trying to use, but it is returning an error:
=IF(U5=10,S5-SMALL(C5:R5,1),IF(U5=11,S5-((SMALL(C5:R5,1)+(SMALL(C5:R5,2)),IF(U5>=12,-(((SMALL(C5:R5,1)+(SMALL(C5:R5,2)+(SMALL(C5:R5,3)))))))))))

I can get the first two IF statements to run but when I add the third I get the error. Parenthesis error? Can anyone help?

Thank you!

Originally Posted by Paul

Hi Double Eagle, welcome to the forum.

Looks like some parenthesis errors, and a missing S5. Perhaps try one of these:

=IF(U5=10,S5-SMALL(C5:R5,1),IF(U5=11,S5-SMALL(C5:R5,1)-SMALL(C5:R5,2),IF(U5>=12,S5-SMALL(C5:R5,1)-SMALL(C5:R5,2)-SMALL(C5:R5,3),S5)))

=IF(U5=10,S5-SMALL(C5:R5,1),IF(U5=11,S5-SUM(SMALL(C5:R5,{1,2})),IF(U5>=12,S5-SUM(SMALL(C5:R5,{1,2,3})),S5)))

Thank you very much! I didn't realize you had replied to my question until today. I must have not set up my notifications correctly.

Again, many thanks!