IF formula problem

[palms]

Hi all. I'm relatively inexperienced in Excel so apologies if this is simple and the solution is obvious, anyways... I'm setting up a basic sheet for a predict the match attendance competition on a football (English) forum. Scoring is basic, spot on =100 points, within 5=50, 10=25, 20=20, 30=15, 40=10, within 50=5, more than 50 away = big fat zero.

An example of 4 cells, C2 to C5...
C2 - The actual match attendance
C3 - Player's prediction
C4 - Difference between prediction and attendance
C5 - Score for that prediction based on the difference

so for C4 to always show a positive number for difference, and to only display a number if a prediction has been made I have entered
=IF(C3="",,ABS(C2-C3))

and in C5 to give the correct score when the attendance and prediction have been entered into C2 and C3 I have
=IF(C4<1,"100",IF(C4<6,"50",IF(C4<11,"25",IF(C4<21,"20",IF(C4<31,"15",IF(C4<41,"10",IF(C4<51,"5",IF(C4>50,"0"))))))))

This works when required data is entered into C2 and C3, but my issue is that when no prediction and attendance have been entered in C2 and C3, then C5 displays 100 for a spot on prediction because C4 is '0'.

So... How do I make it so that C5 only displays a score when a prediction has been made, as I have done with C4??? By trying to do the same as I have in C4 with =IF(C3="",, at the start it throws up an error.

Any help would be much appreciated, thanks

[ARGK]

Hi
The issue you have is that you can only have a maximum of seven nested IF statements in a single formula and you have reached that.
Tony

[Ursul]

Hi,

have you thought about maybe using compound logical 'IF' - that is, use 'AND' or 'OR' e.g. =IF(OR(E2>3,AND(D2="Actual",C2>=10)),1000,0) I know this example has nothing to do with your numbers of cell references but it may help you so you don't use so many nested IF statements

Cheers

[rajuj]

i wish to point out the if nest function is for 7 ifs is only applicable to ms office 2003. For higher version, i think that it supports 64 ifs

[Ursul]

I have just read on another thread this response in reference to Nested IFs.

For ways around the limit have a look at

http://www.cpearson.com/excel/nested.htm

[ARGK]

Thanks for the correction - I had forgotten about the increase in 2007/2010.
Tony