LinkBack URL
About LinkBacks
Find attached , I know how the rank works
But I want the rank formula to rank formula
to rank average without computing average
Formula required on a2 to drag down
Find attached , I know how the rank works
But I want the rank formula to rank formula
to rank average without computing average
Formula required on a2 to drag down
I do not understand what you are trying to do. Can you try to explain again in more detail?
EDIT: I figured it out. I am not sure how easy this will be to do, in one step.
Last edited by Glenn Kennedy; 08-26-2014 at 12:05 PM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I want the formula to compute average that is column i divide column j ,
You see seller08 has sold 2 months and not 3 months , person on leave
Then rank , all this within one formula
I do not want an extra column of first finding the average
Yep. That's clear. I just don't know how to do it.... yet (if it's possible)
Maybe
Put in A2 and dragged down
=AVERAGE(RANK(I2,$I$2:$I$12,0),RANK(I2,$I$2:$I$12,0)+COUNTIF($I$2:$I$12,$I$2)-1)
Last edited by azumi; 08-26-2014 at 12:37 PM.
Edit Nevermind.
Edit again, Azumi, your formula appears to be doing the same as the formula that was already in place. the rank of the averages I believe is what the OP is trying to achieve, which would be:
1
3
11
10
6
4
2
5
9
8
7
Last edited by Speshul; 08-26-2014 at 12:44 PM.
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
Yes - Speshul - that's where I had got to (1, 3, 11, 10, etc) before my little grey cells fried. I have my doubts if this is possible... Certainly I dont see how. Rep to whoever solves this
I can get the averages in what I was thinking would be an array I could sort by and do a match on to get the rank, but I can't do anything to this array, it even gives bad results when you IFERROR it?!!
Either of these (use in all rows, CSE!)
=AVERAGE(INDIRECT("C"&ROW()&":"&"H"&ROW()))
=AVERAGE(OFFSET($C$1:$H$1,ROW()-1,0))
Large gives me an error, SUM gives me bad numbers, and iferror gives those same bad numbers (except it replaces errors with 1641829.78 which is the "sum" of the last number in the array?!)
Try playing with those, idk.
Last edited by Speshul; 08-26-2014 at 01:15 PM.
You can use this formula in A2
=SUM(IF(SUBTOTAL(1,OFFSET(C$2:H$12,ROW(C$2:H$12)-ROW(C$2),0,1))>AVERAGE(C2:H2),1))+1
confirm with CTRL+SHIFT+ENTER and copy down
Audere est facere
daddylonglegs
It works , I will pick the formula with trying to understand
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks