Rank a number

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

My name is Lacramioara and I encounter some problems with Excel functions.

What I want is to rank a difference between two cells within the differences between values in the coresponding columns.


More specifically, in column D, I want to rank B3-C3 within all differences between values in columns B and C, without calculating first the differences in column D and only afterwords to rank the values in column E.


B C
Man Woman
1 2,34 3,65
3 3,40 4,76
3 4,00 3,54
4 4,50 3,45
5 3,90 2,68
5 2,10 4,31

Sorry if you find this question very simple, but I just don't know how to do it.

Please answer if there is any solution.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Hi Rizvisa,

After looking at Lacra's workbook I was wondering why this formula works:
=((B3:B22)-(C3:C22)).
Aren't these array's, so shouldn't give excel an error?

I'm curious how you are going to solve this since it looks like the Rank function needs a column to determine it's rank.

I would put the column with the differences (column D) out of sight or make the text color white or hide the entire column.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am still stumped on this one Trowa. As for formula, I dont think it is working as per intension. It is just using the first member, basically B3 - C3. I think the hope was that ;(B3:B22)-(C3:C22) would return and array and one can find rank for it. For some weird reason, I am thinking some how, some where sumproduct function is in the picture to resolve this issue without any additional column. Just dont know the answer yet