Excel General

[Irina]

Hi people,

Could I (just using functions) get addresses of:

A1 - max(b1:b6, b10:b16) (for example max is 25)
A2 - next max(b1:b6, b10:b16) (for example next max is 20)
....

or should I use a macro.

If macro who can give me a small hint how to create an array from som
range of sheet?

Thank you, thank u, thank...

Irina

 

[Frank Kabel]

Hi
not really sure but maybe the function LARGE is what you're looking
for?

 

[Kurt]

Yep, Large is the ticket here, it will return largest, 2nd largest, etc...

 

[Dave Hawley]

Hi Irina

To get the address of the cell try somethinf like

=ADDRESS(MATCH(MAX(B1:B6, B10:B16),B1:B16,0),2)



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

Hi Irina

To get the address of the cell try something like

=ADDRESS(MATCH(MAX(B1:B6, B10:B16),B1:B16,0),2)



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[JE McGimpsey]

Note that using that function, if the Max value is in B10:B16, and the
same value is in B7:B9, the reference to B7:B9 will be returned. That
may not be an issue. If it is, use:

=IF(MAX(B1:B6)>=MAX(B10:B16),ADDRESS(MATCH(MAX(B1:B6),B1:B6,0),2),
ADDRESS(9+MATCH(MAX(B10:B16),B10:B16,0),2))

In either case, also note that if there are two cells with the max
value, only the first one will be returned.