Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change colour of cells depending on entry in one cell harwookf Excel Worksheet Functions 9 November 3rd 07 01:38 AM
if statements depending on multiple VLOOKUP functions njuneardave Excel Discussion (Misc queries) 1 June 21st 06 04:33 PM
function to count the amount of cells depending on part of the cell entry Andyd74 Excel Worksheet Functions 5 May 25th 06 11:12 AM
Locking Data in a specific cell depending on selection of another CrimsonPlague29 Excel Programming 0 May 10th 06 11:05 AM
Open specific sheet depending on cell contents monkey harry Excel Programming 3 May 2nd 06 04:45 PM


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

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017