"RANKIF" Problem; Unique Rank in One Column, If Value in another Column is Greater than x?
Hi Guys,
Subject probably not the best, but was only way I could think of to describe the problem I am having!
What I want is, a formula, which, if the value in column A is greater than the value of cell C1, then give that value a unique rank (descending) against only the other values that are also above the value of cell C1?
Example Spreadsheet attached, any help you could be would be greatly appreciated!
Questions? Please do let me know!
Chris
Try this formula in row 2 copied down
=IF(A2="","",IF(A2>D$2,RANK(A2,A$2:A$92),""))
or if you want different ranks when you have duplicates in column A you can use this version
=IF(A2="","",IF(A2>D$2,RANK(A2,A$2:A$92)+COUNTIF(A$2:A2,A2)-1,""))
You don't have any such duplicates so those formulas will give the same results for your example
Last edited by daddylonglegs; 09-26-2014 at 11:31 AM.
Audere est facere
Hi,
See attached.
You may feel the Pivot Table alone is sufficient for you since it lists the relevant values in ascending order. However I've added a formula in the original data in column C which will return the ranked value.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
....
late edit, missed the requirement for rank in descending order.
Change the C2 formula to
Formula:
Please Login or Register to view this content.
and copy down.
Re: "RANKIF" Problem; Unique Rank in One Column, If Value in another Column is Greater tha
Hi All!
Very Helpful, Thank you!
One thing I somehow completley forgot to include when I asked you all before is how to do similar to what you just instructed, but instead, if value in Column A is Greater than the control value cell, then rank that value against the others which are also over?
Spreadsheet V2 attached for your assistance!
Chris
Description in post not very clear upon re-reading, explanation within spreadsheet is much clearer! Thanks!
Chris
Hi,
Just adapt DDL's formula in D2 and copied down.
Formula:Please Login or Register to view this content.
Hi Richard,
Firstly, Thank You!
Secondly though, although the values do seem to now be ranked on their PTS assuming their MINS are higher than E2, however, there are ranks omitted.
By this I mean, The Ranks currently go 1, 4, 5, 6, 9 etc. 2, 3, 5, and 8 are missing from this excerpt of the list? Whereas they should really be going 1, 2, 3, 4, 5 because I want a rank of the PTS against each other, assuming the MINS are greater than E2?
Does this make sense?
Chris
Try this formula in D2 copied down
=IF(A2="","",IF(A2>E$2,COUNTIFS(A$2:A$92,">"&E$2,C$2:C$92,">"&C2)+1,""))
Note: none of my suggestions use the helper column, so you don't really need that
Amazing!
That's the one! Yeah the helper column was more to help demonstrate my desires, so that's okay.
Very Quick Last question on this...for now anyway!
How would I reverse the rank on that formula to change from ascending to descending etc?
Chris
If you still want those that are > E2 but to rank the points for those in ascending order just reverse the second >, i.e.
=IF(A2="","",IF(A2>E$2,COUNTIFS(A$2:A$92,">"&E$2,C$2:C$92,"<"&C2)+1,""))
Amazing!
You've been a great help!
Chris!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks