i was wondering if there was a way i can combine these two formulas into one large one
=IF(AF8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($L$3:$L$103)-ROW($L$3)+1)/($L$3:$L$103=AF8),COUNTIF($AF$8:AF8,AF8))))
=IF(AG8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($M$3:$M$103)-ROW($M$3)+1)/($M$3:$M$103=AG8),COUNTIF($AG$8:AG8,AG8))))
cant seem to get my head around it thanks.
Last edited by taker418; 06-06-2013 at 11:19 PM.
Hi and welcome to the forum
Apart from this part...INDEX($A$3:$A$102,AGGREGATE(15,6,...they are unrelated? Under what conditions would which part "fire? what if both AF8 and AG8 contain data...or both empty?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
these two formulas are from a previous post i was working on i would like for the L3 formula to fire then the M3 formula you can look here for the file im working on now to see what im talking about
http://www.excelforum.com/excel-form...-function.html
I have 2007 and it doesnt appear to have the aggregate() function. But I *think* I can make out what you are doing. Not sure if this will help you along the way or not...
I added a helper column in Q to break the ties in M (6 and 81 both have 185 points, there are probably other duplicates), and used this, copied down...
=M3+(COUNTIF($M$3:M3,M3)/1000)
Then for U8, copied down, I used this...
=INDEX(Table14[TEAMS],MATCH(LARGE(Table14[Column1],ROW(A1)),Table14[Column1],0),1)
(I was able to validate most of the returns, but now all your formulas in U just say #name
You need to apply the same principle to AF and AG, and also note that if you are going to use the =large() function like you did, you need to absolute the range...
=IF(T8="","",LARGE($L$3:$L$102,T8))
else it will keep adjusting as it is copied down (like yours currently does)
hmm interesting not really working for me you know anyone with 2010?
whats not working?
could you show me (here is fine) what answers you got in U? I got....
Team # 13
Team # 99
Team # 10
Team # 100
Team # 88
Team # 24
Team # 67
Team # 81
Team # 06
Team # 57
Team # 52
Team # 26
Team # 54
Team # 62
Team # 02
the number of total points for the teams is not right the supposed to match
you are missing names from U.
Where is 81 (185 points)?...6 and 81 both have 185 points, you have 6 but not 81
Where is 26 (170 points)?...you have 52 (171 pts) and 54 (166 pts) but not 26?
If you apply my suggestion to extract the team names, then you can use the simple...
=INDEX(Table14[R(s) WON],MATCH(R8,Table14[TEAMS],0),1)
for the wins and
=INDEX(Table14[TOTAL PTS],MATCH(R8,Table14[TEAMS],0),1)
for the points
when i replace this formula =IF(T8="","",LARGE($M$3:$M$102,T8)) with yours i get n/a
team 34 has 107 points but in the other table it has 482 points
take a look at the attached
thank you so much it works now just one question is the formula for the teams relying on the column 1 or can it be deleted cause i would like to have it user friendly or her thank you so much for your help
i was playing around with the numbers and its still not calculating correctly for instance
Team # 13 1 482
Team # 100 1 321
Team # 99 4 237
Team # 10 0 226
Team # 88 2 196
Team # 24 3 187
Team # 67 3 186
Team # 81 3 185
Team # 06 3 185
Team # 57 3 183
Team # 52 2 171
Team # 26 3 170
Team # 54 3 166
Team # 62 3 164
Team # 02 3 157
Team # 98 2 154
Team # 63 2 154
then
Team # 13 1 482
Team # 100 1 321
Team # 99 4 237
Team # 10 0 226
team 10 with 0 rounds won should not even be in the top 4 its supposed to consider number of rounds won first then number of points including ties.
OK I see that. Change the formula in Q to this...
=(L3&M3)*1+(COUNTIF($M$3:M3,M3)/1000)
And I modified the formula in U so you can remove the numbers in T if you want...
=IF(T8="","",INDEX(Table14[TEAMS],MATCH(LARGE(Table14[Column1],ROW(A1)),Table14[Column1],0),1))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks