Rank a number

Closed
Lacra - Aug 7, 2010 at 03:04 PM
rizvisa1
Posts
4479
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.

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
0