Remember Me?

#1
August 11th 20, 05:22 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2020 Posts: 1
creating Multiple If Statements depending on specific cell entry

Help (Again)

I have spreadsheet that i want to use to track the scores in respects to people predictions for a competition we run each year, however as opposed to updating everyone prediction scores manually i want the sheet to do it. However having trouble working out how to the if statements as there are lots of them. Anyway this is what I have.

Basically i want to compare the predicted score against the actual score and award points i.e.

6 points for an accurate result
4 points for a correct result & score difference
2 Points for a correct result

Now I have split it into 3 separate bits for now i.e. HW (indicating a Home Win), AW (Indicating an Away Win) & SD (Indicating a Score Draw) and as such can get the formulas to work independently but struggling to combine them all, anyway for each one I have the following formulas.

HW - =IF(F\$3="HW",IF(AND(D6=D\$3,E6=E\$3),6,IF((D6-E6)=(D\$3-E\$3),4,IF(D6E6,2,0))))

AW - =IF(J\$3="AW",IF(AND(H6=H\$3,I6=I\$3),6,IF((I6-H6)=(I\$3-H\$3),4,IF(I6H6,2,0))))

SD - =IF(N\$3="SD",IF(AND(L6=L\$3,M6=M\$3),6,IF((M6-L6)=(M\$3-L\$3),4,0)))

However what I want is them all into one column and the cell to pick the right formula dependant on which has been selected for that result.

Anyway Spreadsheet as yet look likes the following, bearing in mind there might be up to 50 lines of entries all with different predictions

Result F A F A F A
2 1 HW 1 2 AW 1 1 SD

F A Points F A Points F A Points
Prediction 1 2 1 6 2 1 0 2 1 0
Prediction 2 3 1 2 1 2 6 1 2 0
Prediction 3 2 0 2 1 1 0 1 1 6
Prediction 4 3 2 4 2 3 4 2 2 4
Prediction 5 3 1 2 3 1 0 3 1 0
Prediction 6 1 1 0 1 4 2 3 2 0
Prediction 7 1 2 0 0 2

Anyway any help in getting it into one stack were the formulas are selected dependant on what type of results it is would be greatly appreciated, if not clear can send a copy of the test spreadsheet i have been working on.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post harwookf Excel Worksheet Functions 9 November 3rd 07 01:38 AM njuneardave Excel Discussion (Misc queries) 1 June 21st 06 04:33 PM Andyd74 Excel Worksheet Functions 5 May 25th 06 11:12 AM CrimsonPlague29 Excel Programming 0 May 10th 06 11:05 AM monkey harry Excel Programming 3 May 2nd 06 04:45 PM

All times are GMT +1. The time now is 02:50 PM.