Calculating Difference between ranked values within random range
 

Good afternoon...

I am looking for a formula that will calculate the difference between consecutive values of random values within a range. I have a range of 10 values
(j2:j11) and they are in random order this order can change randomly. I need to subtract ranked (ordered) value 9 from ranked value 10, 8 from 9, 7
from 8, etc. (and return negative values - see desired outcome)

Range Desired Descending
Value Outcome Rank
3271 -400 9
3735 -85 7
4217 -38 2
3270 -1 10
4255 4255 1
3671 -64 8
4158 -59 3
3859 -115 5
3974 -184 4
3820 -39 6

I have used =max, =count and =rank commands - yet I am not getting my anticipated outcome.

Any help would be appreciated. TIA

Bravejoe

"There's fodder for the cannons and the guilty ones can all sleep safely." Andy Partridge XTC
"That's my opinion. I could be wrong." Dennis Miller
"It Doesn't Matter!" Bravejoe

remove the "t1","us" from my email address to reply privately.

Calculating Difference between ranked values within random range
 

Put your first column data in column A
In B1 enter:
=RANK(A1,A$1:A$10) and copy down

In C1 enter:
=A1-OFFSET($A$1,MATCH(B1-1,$B$1:$B$10,0)-1,0) and copy down

We see:

3271 9 -400
3735 7 -85
4217 2 -38
3270 10 -1
4255 1 #N/A
3671 8 -64
4158 3 -59
3859 5 -115
3974 4 -184
3820 6 -39

--
Gary''s Student - gsnu2007j


"Joe Chromy, Jr" wrote:

Good afternoon...

I am looking for a formula that will calculate the difference between consecutive values of random values within a range. I have a range of 10 values
(j2:j11) and they are in random order this order can change randomly. I need to subtract ranked (ordered) value 9 from ranked value 10, 8 from 9, 7
from 8, etc. (and return negative values - see desired outcome)

Range Desired Descending
Value Outcome Rank
3271 -400 9
3735 -85 7
4217 -38 2
3270 -1 10
4255 4255 1
3671 -64 8
4158 -59 3
3859 -115 5
3974 -184 4
3820 -39 6

I have used =max, =count and =rank commands - yet I am not getting my anticipated outcome.

Any help would be appreciated. TIA

Bravejoe

"There's fodder for the cannons and the guilty ones can all sleep safely." Andy Partridge XTC
"That's my opinion. I could be wrong." Dennis Miller
"It Doesn't Matter!" Bravejoe

remove the "t1","us" from my email address to reply privately.