Problem with array result 1

[philb1341]

I am looking for the MAX value in an array(B2:H8), and wish to know the row and column numbers where it is.
I have figured out how to get the row and coulmn numbers of MAX using the following formula:
=IF(ISERROR(MIN(IF(B2:H8=MAX(B2:H8),ROW(B2:H8)+(COLUMN(B2:H8)/1000),65537))),"",MIN(IF(B2:H8=MAX(B2:H8),ROW(B2:H8)+(COLUMN(B2:H8)/1000),65537)))
The left number is row and the number following the decimal is column.
My problem is that if the array is totally empty, I get the number representing the uper left cell. if I populate one or any cells with numbers it works correctly.
Any idea how I can get the result to be blank if all cells are blank?
Help is appreciated!

Thanks,

philb1341

 

[SkipVought]

Hi,

What you need to do in your formula is in an outer IF(), test the array for the presence of valid values. If so then return the data, otherwise return "".

 

[philb1341]

Skip, if what you mean is put brackets (cntrl-shift-enter)around it, I have. I also added an =IF(ORIGINAL FORMULA), and received an error about typing other than a formula. Perhaps I don't get what you mean. I'll keep playing with it.

Thanks so much for the help.

Philb1341

 

[SkipVought]

in a separate cell
[tt]
=IF(Count(B2:H8)=0,"",A1)
[/tt]
where A1 is where your array formula is
[tt]
A1: =MIN(IF(B2:H8=MAX(B2:H8),ROW(B2:H8)+(COLUMN(B2:H8)/1000),65537))
[/tt]
assuming that none of the values in your B2:H8 is a formula. Otherwise you need some other technique to determine if the range is empty.

 

[philb1341]

This worked! Thank you very much for your help. Your expertise is appreciated!

Philb1341

 

[Andrzejek]

A question out of curiosity…
Wouldn’t be easier to create a little (or not so little) VBA Function and put all that MIN / MAX /1000 / 65537 etc. logic into it? All with comments and such for easy read and modifications. Just pass the range (B2:H8) and return whatever you want. And reuse it if you need for other ranges.

Just an idea.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.