Not sure what function, macro, etc to use

[Tony]

Here is a simplified version of my spreadsheet:

A B C D
1 Name Apples Oranges Bananas
2 Me 12 25 17
3 John 9 30 20
4 Fred 11 41 29
5 Tom 4 22 22
6 Bob 7 35 25


I’m trying to figure out what to use (functions, macros, etc.) to give me
specific results relative to ‘Me’:

Apples Oranges Bananas
Next Highest 30 20
Me 12 25 17
Next Lowest 11 22


These numbers will change daily so I can’t use an OFFSET function. Anyone
Know how to do this?

 

[ShaneDevenshire]

Hi Tony,

I'm not clear on where the title Next came from?

 

[Rick Rothstein \(MVP - VB\)]

1) Put =A1 in G1 and copy it across to J1.

2) Put "Next Highest" in Cell G2 and put this array-entered (see note)
formula in H2...

=IF(MIN(IF(B3:B100<=B2,"",B3:B100-B2)),B2+MIN(IF(B3:B100<=B2,"",B3:B100-B2)),"")

And copy it across to J2.

3) Put =B2 in G3 and copy it across to J3.

4) Finally, put "Next Lowest" in G4 and put this array-entered (see note)
formula in H4...

=IF(B2=MIN(IF(B3:B100>=B2,"",B2-B3:B100)),"",B2-MIN(IF(B3:B100>=B2,"",B2-B3:B100)))

Note 1: Commit both of the array-entered formulas by pressing
Ctrl+Shift+Enter at the same time instead of just Enter.

Note 2: Change the B100 references to a cell reference equal to, or larger
than, the largest row number you ever expect to use.

Rick