Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this be done using an array formula ?
Hi,
Apologies for the wordy posting, but it's only to give detail to the problem. It's quite a simple problem in theory, but it's giving me headaches! I have a spreadsheet as follows, which shows the position finished in 4 races; A B C D E F ------------------------------------------------------------------------------ 1| Race 1 Race 2 Race 3 Race 4 Total points 2| John 2 2 - 3 3| Phil - 1 2 4 4| Dave 1 3 - 1 5| Alan 3 - 1 2 6| 7| Competitors 3 3 2 4 In the above, any of the 4 competitors can compete in any of the races. e.g. in Race 1: John, Dave and Alan competed, but Phil did not. e.g. in Race 3: Phil and Alan competed. John and Dave did not. The competitors don't have to enter every race. Cells B2:E5 show the competitors finishing position. In row 7, the number of competitors per race is shown (using COUNT) Depending on the number of competitors (in B7, C7, etc.), the points (row F) are allocated as follows; points value = number of competitors - position + 1 So in Race 1, the points would be calculated as; number of competitors = 3 John's points = 3 (no.of competitors) - 2 (his position) + 1 giving 2 points Dave's points = 3 (no.of competitors) - 1 (his position) + 1 giving 3 points Alan's points = 3 (no.of competitors) - 3 (his position) + 1 giving 1 point. Phil didn't enter = 0 points in this race. In Race 3, the points would be calculated as; number of competitors = 2 Phil's points = 2 (no.of competitors) - 2 (his position) + 1 giving 1 point Alan's points = 2 (no.of competitors) - 1 (his position) + 1 giving 2 points. John and Dave didn't enter, so they get 0 points in this race. Taking this points scoring system, I've shown the number of points scored in brackets alongside each finishing position. A B C D E F -------------------------------------------------------------------------------------- 1| Race 1 Race 2 Race 3 Race 4 Total Points 2| John 2(2) 2(2) - 3(2) 6 3| Phil - 1(3) 2(1) 4(1) 5 4| Dave 1(3) 3(1) - 1(4) 8 5| Alan 3(1) - 1(2) 2(3) 6 6| 7| Competitors 3 3 2 4 So, in race 4; Dave was awarded 4 points because he came 1st. Alan was awarded 3 points because he came 2nd. John was awarded 2 points because he came 3rd. Phil was awarded 1 point because he came 4th. So, here's my question, trying to calculate column F. Is there any way in a single (array ?) formula of doing this calculation :- number of competitors - position + 1 ..... for each race they've entered, and put the total of those points into column F (expected result has been shown in the above mock up) Note. I think an ISBLANK would need to be used because people who didn't enter the race won't be awarded a position. I've tried to tackle this with an array formula but I'm not quite there. Maybe it can't be done with a traditional formula (?). Any thoughts would be appreciated. Mike (using XL 03). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this be done using an array formula ?
On Wed, 3 May 2006 23:20:29 +0100, "borntorun75"
wrote: Hi, Apologies for the wordy posting, but it's only to give detail to the problem. It's quite a simple problem in theory, but it's giving me headaches! I have a spreadsheet as follows, which shows the position finished in 4 races; A B C D E F ------------------------------------------------------------------------------ 1| Race 1 Race 2 Race 3 Race 4 Total points 2| John 2 2 - 3 3| Phil - 1 2 4 4| Dave 1 3 - 1 5| Alan 3 - 1 2 6| 7| Competitors 3 3 2 4 In the above, any of the 4 competitors can compete in any of the races. e.g. in Race 1: John, Dave and Alan competed, but Phil did not. e.g. in Race 3: Phil and Alan competed. John and Dave did not. The competitors don't have to enter every race. Cells B2:E5 show the competitors finishing position. In row 7, the number of competitors per race is shown (using COUNT) Depending on the number of competitors (in B7, C7, etc.), the points (row F) are allocated as follows; points value = number of competitors - position + 1 So in Race 1, the points would be calculated as; number of competitors = 3 John's points = 3 (no.of competitors) - 2 (his position) + 1 giving 2 points Dave's points = 3 (no.of competitors) - 1 (his position) + 1 giving 3 points Alan's points = 3 (no.of competitors) - 3 (his position) + 1 giving 1 point. Phil didn't enter = 0 points in this race. In Race 3, the points would be calculated as; number of competitors = 2 Phil's points = 2 (no.of competitors) - 2 (his position) + 1 giving 1 point Alan's points = 2 (no.of competitors) - 1 (his position) + 1 giving 2 points. John and Dave didn't enter, so they get 0 points in this race. Taking this points scoring system, I've shown the number of points scored in brackets alongside each finishing position. A B C D E F -------------------------------------------------------------------------------------- 1| Race 1 Race 2 Race 3 Race 4 Total Points 2| John 2(2) 2(2) - 3(2) 6 3| Phil - 1(3) 2(1) 4(1) 5 4| Dave 1(3) 3(1) - 1(4) 8 5| Alan 3(1) - 1(2) 2(3) 6 6| 7| Competitors 3 3 2 4 So, in race 4; Dave was awarded 4 points because he came 1st. Alan was awarded 3 points because he came 2nd. John was awarded 2 points because he came 3rd. Phil was awarded 1 point because he came 4th. So, here's my question, trying to calculate column F. Is there any way in a single (array ?) formula of doing this calculation :- number of competitors - position + 1 ..... for each race they've entered, and put the total of those points into column F (expected result has been shown in the above mock up) Note. I think an ISBLANK would need to be used because people who didn't enter the race won't be awarded a position. I've tried to tackle this with an array formula but I'm not quite there. Maybe it can't be done with a traditional formula (?). Any thoughts would be appreciated. Mike (using XL 03). The following in F2 and copied down seems to work for me. =SUMPRODUCT((B2:E2<"")*(B$7:E$7-B2:E2+1)) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this be done using an array formula ?
Superb Richard. That's exactly what I was after.
Not used SUMPRODUCT before. Just the job. Thanks ever so much. Mike (also in Cheshire, UK) "Richard Buttrey" wrote in message ... [ snip ] The following in F2 and copied down seems to work for me. =SUMPRODUCT((B2:E2<"")*(B$7:E$7-B2:E2+1)) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this be done using an array formula ?
Hi,
A couple of further points on this question. 1) The formatting of my question went a bit awry. Column; A shows the names. B shows Race 1 details C shows Race 2 details D shows Race 3 details E shows Race 4 details F shows Total Points. 2) The spreadsheet shows 4 races. Ultimately this would have to be able to handle more than 4 races and more than 4 competitors. Thanks again all. Mike. "borntorun75" wrote in message ... Hi, Apologies for the wordy posting, but it's only to give detail to the problem. It's quite a simple problem in theory, but it's giving me headaches! I have a spreadsheet as follows, which shows the position finished in 4 races; A B C D E F ------------------------------------------------------------------------------ 1| Race 1 Race 2 Race 3 Race 4 Total points 2| John 2 2 - 3 3| Phil - 1 2 4 4| Dave 1 3 - 1 5| Alan 3 - 1 2 6| 7| Competitors 3 3 2 4 In the above, any of the 4 competitors can compete in any of the races. e.g. in Race 1: John, Dave and Alan competed, but Phil did not. e.g. in Race 3: Phil and Alan competed. John and Dave did not. The competitors don't have to enter every race. Cells B2:E5 show the competitors finishing position. In row 7, the number of competitors per race is shown (using COUNT) Depending on the number of competitors (in B7, C7, etc.), the points (row F) are allocated as follows; points value = number of competitors - position + 1 So in Race 1, the points would be calculated as; number of competitors = 3 John's points = 3 (no.of competitors) - 2 (his position) + 1 giving 2 points Dave's points = 3 (no.of competitors) - 1 (his position) + 1 giving 3 points Alan's points = 3 (no.of competitors) - 3 (his position) + 1 giving 1 point. Phil didn't enter = 0 points in this race. In Race 3, the points would be calculated as; number of competitors = 2 Phil's points = 2 (no.of competitors) - 2 (his position) + 1 giving 1 point Alan's points = 2 (no.of competitors) - 1 (his position) + 1 giving 2 points. John and Dave didn't enter, so they get 0 points in this race. Taking this points scoring system, I've shown the number of points scored in brackets alongside each finishing position. A B C D E F -------------------------------------------------------------------------------------- 1| Race 1 Race 2 Race 3 Race 4 Total Points 2| John 2(2) 2(2) - 3(2) 6 3| Phil - 1(3) 2(1) 4(1) 5 4| Dave 1(3) 3(1) - 1(4) 8 5| Alan 3(1) - 1(2) 2(3) 6 6| 7| Competitors 3 3 2 4 So, in race 4; Dave was awarded 4 points because he came 1st. Alan was awarded 3 points because he came 2nd. John was awarded 2 points because he came 3rd. Phil was awarded 1 point because he came 4th. So, here's my question, trying to calculate column F. Is there any way in a single (array ?) formula of doing this calculation :- number of competitors - position + 1 ..... for each race they've entered, and put the total of those points into column F (expected result has been shown in the above mock up) Note. I think an ISBLANK would need to be used because people who didn't enter the race won't be awarded a position. I've tried to tackle this with an array formula but I'm not quite there. Maybe it can't be done with a traditional formula (?). Any thoughts would be appreciated. Mike (using XL 03). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this be done using an array formula ?
It's not an array, and it's not pretty... ...but it works. =IF(ISBLANK(B5),0,MAX($B$2:$B$5)-B5+1)+IF(ISBLANK(C5),0,MAX($C$2:$C$5)-C5+1)+IF(ISBLANK(D5),0,MAX($D$2:$D$5)-D5+1)+IF(ISBLANK(E5),0,MAX($E$2:$E$5)-E5+1) -- MattShoreson ------------------------------------------------------------------------ MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472 View this thread: http://www.excelforum.com/showthread...hreadid=538680 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
problem with Array Formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |