Rank a number

Closed
Lacra - Aug 7, 2010 at 03:04 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 10, 2010 at 11:40 AM
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.

Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 7, 2010 at 07:22 PM
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.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 10, 2010 at 11:40 AM
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