Need a little help with MIN formula and formulas in range.

[jspoon14]

I need a little help with the MIN formula.

I am trying to pull the lowest value from a range of values. The range of values are pulled from another worksheet with a formula. The range does not have a value in ever cell but does have a formula in each cell. When I use the MIN formula to get the lowest value is comes up blank. It appears that the formulas without a value is causing the MIN formula to pull the cell with no value but a formula. I want it to ignore the empty cells and only look at the ones with a value.

Does anyone have an idea how I can do this?


Thanks for your time.

[daffodil11]

You could try an array.

Where E10:E14 = your range

=MIN(IF(E10:E14<>"",E10:E14))

or

=MIN(IF(ISNUMBER(E10:E14),E10:E14))

Both entered using Ctrl+Shift+Enter

[jspoon14]

Neither of those seem to be working, I entered the formula as you stated it just with the actrual range I have and I got the "Value" message for the first one and nothing in the second.

[daffodil11]

If you are able to attach a sample of your work or can fabricate something similar, I will gladly take a look at it and repost it with any solutions I come up with.

In all circumstances I am familiar with, a cell with a blank values will be automatically be omitted from a MIN function. If I can examine your work, I may be able to figure out what has gone awry.

[jspoon14]

I am working in the "Analysis Matrix" tab. You can see at the bottom of the matrix there is a row where I want the MIN value to appear from the range of values in the column above. Column E is the one I have been testing. The MAX formula works fine.

[hemesh]

hello jspoon14 try this MIN(IF(E4:E33<>0,E4:E33)) enter as array (hold control and shift then press enter)

as in your range 0 is the lowest number thus formula was returning 0 always

[hemesh]

Just have a look at this

[daffodil11]

I see the cause of the issue. Your formulas are returning a zero instead of blank values, whereas had you used for each =IF(ISBLANK(REFERENCE),"",REFERENCE) you would have gotten actual blanks.

Re-linking all the data would be a pretty big pain considering the source format, so to omit the zeroes, use can use an array formula to narrow the range:

E35 would look like this

=MIN(IF(E4:E33>0,E4:E33))

This formula needs to be confirmed with Ctrl+Shift+Enter instead of exiting the cell by only hitting Enter, which will apply { } to the formula. (It's not the same if you type them by hand)

[hemesh]

I see the cause of the issue. Your formulas are returning a zero instead of blank values, whereas had you used for each =IF(ISBLANK(REFERENCE),"",REFERENCE) you would have gotten actual blanks.

Re-linking all the data would be a pretty big pain considering the source format, so to omit the zeroes, use can use an array formula to narrow the range:

E35 would look like this

=MIN(IF(E4:E33>0,E4:E33))

This formula needs to be confirmed with Ctrl+Shift+Enter instead of exiting the cell by only hitting Enter, which will apply { } to the formula. (It's not the same if you type them by hand)

What if any cell has value in negative then =MIN(IF(E4:E33>0,E4:E33)) would fail

[jspoon14]

Cool, that worked. Thanks for your help, I did not know the Ctrl+Shift+Enter command.

[hemesh]

Glad WE could Help! Please mark thread as solved go to thread tools mark thread as solved.

[jspoon14]

That is good to know but there wont be to many incidents with a negative number. If i do have a negative how do I fix the formula?

[daffodil11]

If the cost for building those houses have negative values, I want one.

You can work around negatives with <>0 instead of >0

[hemesh]

If the cost for building those houses have negative values, I want one.

You can work around negatives with <>0 instead of >0

Yes jspoon you can use =min(if(E4:E33<>0,E4:E33) and btw my suggestion was just for incase if there is a negative value then jspoon can use other method