| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COMPLEX ARRAY
Win XP Pro sp3
MS office 2003 I am using this formula in an Excel workbook: IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN49 ,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) What I want to do is find how many games of 25 or more played by each player, then if over 25 then take the "best" 25 and give an aggregate. Within this workbook there is a SS linked to show player with: a) 25 or more games played. b) If more than 25 played then aggregate the highest 25. what I have at present is several players played more than 25 games but in the linked SS the names are mixed up if more than one player has the same aggregate. Sorry but it is difficult trying to explain as I am not too familiar with mixed formulae. My formulae came from this newsgroup originally. Please ask if you require more information etc. Regards Terry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COMPLEX ARRAY
IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN4 9,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"",""))
Not sure what you're trying to do. That formula has a couple of things wrong with it. If AU49 is a number =25 then sum the 4th through the 51st largest numbers (a total of 48 numbers) in the range A49:AN49. The problem is that the range A49:AN49 is only 40 cells so there can't be 48 numbers to sum. ???? -- Biff Microsoft Excel MVP "Terry" wrote in message ... Win XP Pro sp3 MS office 2003 I am using this formula in an Excel workbook: IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN49 ,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) What I want to do is find how many games of 25 or more played by each player, then if over 25 then take the "best" 25 and give an aggregate. Within this workbook there is a SS linked to show player with: a) 25 or more games played. b) If more than 25 played then aggregate the highest 25. what I have at present is several players played more than 25 games but in the linked SS the names are mixed up if more than one player has the same aggregate. Sorry but it is difficult trying to explain as I am not too familiar with mixed formulae. My formulae came from this newsgroup originally. Please ask if you require more information etc. Regards Terry |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COMPLEX ARRAY
OK
Altered range A49:AN49, to A49:AT49, WHICH GOES TO THE END OF THE SEASON.Reason not incl. previous as there were no scores in the "missing" cols. which works on dates played. AW Col. is: IF(ISNUMBER(AV4),RANK(AV4,$AV$4:$AV$51),"") If I may show the formula for the link SS table as follows: 5 columns.. Col. 1: fixed input of places from 1st thro' 8th. Col. 2: INDEX(Main!$A$4:$A$51,MATCH(D5,Main!$AV$4:$AV$51,0 )) Col. 3: LARGE(Main!$AV$4:$AV$51,ROW(1:1)) Col. 4: LOOKUP(C5,Main!$A$4:$A$51,Main!$AU$4:$AU$51) Col. 5: LOOKUP(C5,Main!$A$4:$A$51,Main!$AY$4:$AY$51) AY Col. is number of wins. HTH Regards Terry "T. Valko" wrote in message ... IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN4 9,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) Not sure what you're trying to do. That formula has a couple of things wrong with it. If AU49 is a number =25 then sum the 4th through the 51st largest numbers (a total of 48 numbers) in the range A49:AN49. The problem is that the range A49:AN49 is only 40 cells so there can't be 48 numbers to sum. ???? -- Biff Microsoft Excel MVP "Terry" wrote in message ... Win XP Pro sp3 MS office 2003 I am using this formula in an Excel workbook: IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN49 ,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) What I want to do is find how many games of 25 or more played by each player, then if over 25 then take the "best" 25 and give an aggregate. Within this workbook there is a SS linked to show player with: a) 25 or more games played. b) If more than 25 played then aggregate the highest 25. what I have at present is several players played more than 25 games but in the linked SS the names are mixed up if more than one player has the same aggregate. Sorry but it is difficult trying to explain as I am not too familiar with mixed formulae. My formulae came from this newsgroup originally. Please ask if you require more information etc. Regards Terry |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COMPLEX ARRAY
I'd need to see your file to be able to figure out what you're trying to do.
If your file isn't too big, less than 1mb, and you want me to take a look at it let me know how to contact you. -- Biff Microsoft Excel MVP "Terry" wrote in message ... OK Altered range A49:AN49, to A49:AT49, WHICH GOES TO THE END OF THE SEASON.Reason not incl. previous as there were no scores in the "missing" cols. which works on dates played. AW Col. is: IF(ISNUMBER(AV4),RANK(AV4,$AV$4:$AV$51),"") If I may show the formula for the link SS table as follows: 5 columns.. Col. 1: fixed input of places from 1st thro' 8th. Col. 2: INDEX(Main!$A$4:$A$51,MATCH(D5,Main!$AV$4:$AV$51,0 )) Col. 3: LARGE(Main!$AV$4:$AV$51,ROW(1:1)) Col. 4: LOOKUP(C5,Main!$A$4:$A$51,Main!$AU$4:$AU$51) Col. 5: LOOKUP(C5,Main!$A$4:$A$51,Main!$AY$4:$AY$51) AY Col. is number of wins. HTH Regards Terry "T. Valko" wrote in message ... IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN4 9,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) Not sure what you're trying to do. That formula has a couple of things wrong with it. If AU49 is a number =25 then sum the 4th through the 51st largest numbers (a total of 48 numbers) in the range A49:AN49. The problem is that the range A49:AN49 is only 40 cells so there can't be 48 numbers to sum. ???? -- Biff Microsoft Excel MVP "Terry" wrote in message ... Win XP Pro sp3 MS office 2003 I am using this formula in an Excel workbook: IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN49 ,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) What I want to do is find how many games of 25 or more played by each player, then if over 25 then take the "best" 25 and give an aggregate. Within this workbook there is a SS linked to show player with: a) 25 or more games played. b) If more than 25 played then aggregate the highest 25. what I have at present is several players played more than 25 games but in the linked SS the names are mixed up if more than one player has the same aggregate. Sorry but it is difficult trying to explain as I am not too familiar with mixed formulae. My formulae came from this newsgroup originally. Please ask if you require more information etc. Regards Terry |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COMPLEX ARRAY
Thank you.
The Excel file is no more than 84KB. The file is for recording bowls drives at our voluntry run shortmat bowls club. The aim is to finish up with 4 players with the highest aggregate of their best 25 games, at the end of the season...shown on main page.(dates) Appreciate if you can look at it please. Can I attach the file in an email to you?...fully checked by my antivirus program. Regards Terry "T. Valko" wrote in message ... I'd need to see your file to be able to figure out what you're trying to do. If your file isn't too big, less than 1mb, and you want me to take a look at it let me know how to contact you. -- Biff Microsoft Excel MVP "Terry" wrote in message ... OK Altered range A49:AN49, to A49:AT49, WHICH GOES TO THE END OF THE SEASON.Reason not incl. previous as there were no scores in the "missing" cols. which works on dates played. AW Col. is: IF(ISNUMBER(AV4),RANK(AV4,$AV$4:$AV$51),"") If I may show the formula for the link SS table as follows: 5 columns.. Col. 1: fixed input of places from 1st thro' 8th. Col. 2: INDEX(Main!$A$4:$A$51,MATCH(D5,Main!$AV$4:$AV$51,0 )) Col. 3: LARGE(Main!$AV$4:$AV$51,ROW(1:1)) Col. 4: LOOKUP(C5,Main!$A$4:$A$51,Main!$AU$4:$AU$51) Col. 5: LOOKUP(C5,Main!$A$4:$A$51,Main!$AY$4:$AY$51) AY Col. is number of wins. HTH Regards Terry "T. Valko" wrote in message ... IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN4 9,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) Not sure what you're trying to do. That formula has a couple of things wrong with it. If AU49 is a number =25 then sum the 4th through the 51st largest numbers (a total of 48 numbers) in the range A49:AN49. The problem is that the range A49:AN49 is only 40 cells so there can't be 48 numbers to sum. ???? -- Biff Microsoft Excel MVP "Terry" wrote in message ... Win XP Pro sp3 MS office 2003 I am using this formula in an Excel workbook: IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN49 ,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) What I want to do is find how many games of 25 or more played by each player, then if over 25 then take the "best" 25 and give an aggregate. Within this workbook there is a SS linked to show player with: a) 25 or more games played. b) If more than 25 played then aggregate the highest 25. what I have at present is several players played more than 25 games but in the linked SS the names are mixed up if more than one player has the same aggregate. Sorry but it is difficult trying to explain as I am not too familiar with mixed formulae. My formulae came from this newsgroup originally. Please ask if you require more information etc. Regards Terry |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COMPLEX ARRAY
The Excel file is no more than 84KB.
Can I attach the file in an email to you?... Yes. Let me know how to contact you. The email address you see in my profile is bogus. -- Biff Microsoft Excel MVP "Terry" wrote in message ... Thank you. The Excel file is no more than 84KB. The file is for recording bowls drives at our voluntry run shortmat bowls club. The aim is to finish up with 4 players with the highest aggregate of their best 25 games, at the end of the season...shown on main page.(dates) Appreciate if you can look at it please. Can I attach the file in an email to you?...fully checked by my antivirus program. Regards Terry "T. Valko" wrote in message ... I'd need to see your file to be able to figure out what you're trying to do. If your file isn't too big, less than 1mb, and you want me to take a look at it let me know how to contact you. -- Biff Microsoft Excel MVP "Terry" wrote in message ... OK Altered range A49:AN49, to A49:AT49, WHICH GOES TO THE END OF THE SEASON.Reason not incl. previous as there were no scores in the "missing" cols. which works on dates played. AW Col. is: IF(ISNUMBER(AV4),RANK(AV4,$AV$4:$AV$51),"") If I may show the formula for the link SS table as follows: 5 columns.. Col. 1: fixed input of places from 1st thro' 8th. Col. 2: INDEX(Main!$A$4:$A$51,MATCH(D5,Main!$AV$4:$AV$51,0 )) Col. 3: LARGE(Main!$AV$4:$AV$51,ROW(1:1)) Col. 4: LOOKUP(C5,Main!$A$4:$A$51,Main!$AU$4:$AU$51) Col. 5: LOOKUP(C5,Main!$A$4:$A$51,Main!$AY$4:$AY$51) AY Col. is number of wins. HTH Regards Terry "T. Valko" wrote in message ... IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN4 9,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) Not sure what you're trying to do. That formula has a couple of things wrong with it. If AU49 is a number =25 then sum the 4th through the 51st largest numbers (a total of 48 numbers) in the range A49:AN49. The problem is that the range A49:AN49 is only 40 cells so there can't be 48 numbers to sum. ???? -- Biff Microsoft Excel MVP "Terry" wrote in message ... Win XP Pro sp3 MS office 2003 I am using this formula in an Excel workbook: IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN49 ,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) What I want to do is find how many games of 25 or more played by each player, then if over 25 then take the "best" 25 and give an aggregate. Within this workbook there is a SS linked to show player with: a) 25 or more games played. b) If more than 25 played then aggregate the highest 25. what I have at present is several players played more than 25 games but in the linked SS the names are mixed up if more than one player has the same aggregate. Sorry but it is difficult trying to explain as I am not too familiar with mixed formulae. My formulae came from this newsgroup originally. Please ask if you require more information etc. Regards Terry |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COMPLEX ARRAY
Sorry group I am unable to reply to group, unless this does??
Terry "T. Valko" wrote in message ... The Excel file is no more than 84KB. Can I attach the file in an email to you?... Yes. Let me know how to contact you. The email address you see in my profile is bogus. -- Biff Microsoft Excel MVP "Terry" wrote in message ... Thank you. The Excel file is no more than 84KB. The file is for recording bowls drives at our voluntry run shortmat bowls club. The aim is to finish up with 4 players with the highest aggregate of their best 25 games, at the end of the season...shown on main page.(dates) Appreciate if you can look at it please. Can I attach the file in an email to you?...fully checked by my antivirus program. Regards Terry "T. Valko" wrote in message ... I'd need to see your file to be able to figure out what you're trying to do. If your file isn't too big, less than 1mb, and you want me to take a look at it let me know how to contact you. -- Biff Microsoft Excel MVP "Terry" wrote in message ... OK Altered range A49:AN49, to A49:AT49, WHICH GOES TO THE END OF THE SEASON.Reason not incl. previous as there were no scores in the "missing" cols. which works on dates played. AW Col. is: IF(ISNUMBER(AV4),RANK(AV4,$AV$4:$AV$51),"") If I may show the formula for the link SS table as follows: 5 columns.. Col. 1: fixed input of places from 1st thro' 8th. Col. 2: INDEX(Main!$A$4:$A$51,MATCH(D5,Main!$AV$4:$AV$51,0 )) Col. 3: LARGE(Main!$AV$4:$AV$51,ROW(1:1)) Col. 4: LOOKUP(C5,Main!$A$4:$A$51,Main!$AU$4:$AU$51) Col. 5: LOOKUP(C5,Main!$A$4:$A$51,Main!$AY$4:$AY$51) AY Col. is number of wins. HTH Regards Terry "T. Valko" wrote in message ... IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN4 9,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) Not sure what you're trying to do. That formula has a couple of things wrong with it. If AU49 is a number =25 then sum the 4th through the 51st largest numbers (a total of 48 numbers) in the range A49:AN49. The problem is that the range A49:AN49 is only 40 cells so there can't be 48 numbers to sum. ???? -- Biff Microsoft Excel MVP "Terry" wrote in message ... Win XP Pro sp3 MS office 2003 I am using this formula in an Excel workbook: IF(AND(ISNUMBER(AU49),AU49=25),SUM(LARGE(A49:AN49 ,ROW($A$4:$A$51))),IF(ISNUMBER(AU49),"","")) What I want to do is find how many games of 25 or more played by each player, then if over 25 then take the "best" 25 and give an aggregate. Within this workbook there is a SS linked to show player with: a) 25 or more games played. b) If more than 25 played then aggregate the highest 25. what I have at present is several players played more than 25 games but in the linked SS the names are mixed up if more than one player has the same aggregate. Sorry but it is difficult trying to explain as I am not too familiar with mixed formulae. My formulae came from this newsgroup originally. Please ask if you require more information etc. Regards Terry |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
| Complex lookup array with 4 variables | Excel Worksheet Functions | |||
| Complex Array sorting issue | Excel Worksheet Functions | |||
| Complex countif of array elements | Excel Worksheet Functions | |||
| Help with complex index array issue | Excel Worksheet Functions | |||
Linear Mode
