Thread: Rank with conditions

I have done some searching on the subject, and can't quite get to the kind of conditional rank I need. I've got a row of numbers I want ranked, with the ranking displayed in the next row. The numbers that are the same will get the same ranking. I'd like to be able to change that based on a test of two other numbers. I'll give you an example with expected results:

........D...E....F....G....H .....
..8...24
..9...22
10........45...45...46...49 .....
11........13...12...11...13 ..... <--- rank based on this row (descending)
12.........1.....3.....4 ....2 ..... <--- rank

The basic rank would be E12=RANK(E11,$E11:$Z11,0), copied across in row 12.

I want to compare the numbers in row 10 with the sum of D8:D9. Whichever is closer (has the least difference) gets the lower rank. If the difference is the same, then they will keep the same rank, as RANK would have originally done without conditions.

I started the test with something like this:
IF(ABS($D8+$D9-E10)<ABS($D8+$D9-H10), rank lower, rank higher)

This is a case where I don't know the numbers up front, so would want to keep it general, and copied across (so that E10 and H10 would have to be the range of row 10, or something like that). That way, as numbers are updated, the rank updates.

TIA

Bump for a little help .

Let me add that the numbers in row 10 won't change. The numbers in column D and row 11 can change. So the ranking would need to test every time those numbers are updated, and test against the other columns with the same row 11 numbers. So let's say row D8:D9 changes to 26,24, then column H gets the rank of 1, column E gets the rank of 2, and the other two stay the same. From what I can tell, a formula may not be able to do this. A macro may be in order. But I don't know enough about macros to make that happen.

Thanks.