Exclude Zero's from Min Function nested inside an index+Match... all within CELL funtion

Originally Posted by Bernie Deitrick

Array enter:

=CELL("address",INDEX(PercentWorksheet!E13:E500,MATCH(MIN(IF(LineItemTotalRange>0,LineItemTotalRange)),PercentWorksheet!E13:E500,0)))

Thanks for the reply Bernie.

if by "array enter" you mean to enter the formula and rather than hitting enter, I should hit CONTROL+SHIFT+ENTER - then if I do that, it returns a #VALUE! error.

Originally Posted by daddylonglegs

Are these two ranges the same thing?

PercentWorksheet!E13:E500

LineItemTotalRange

?

They are the same column....but not the same range.

As I understand INDEX/Match....

Formula:

Please Login or Register  to view this content.

Additional Information:
The formula worked before added zeros into my range.

If I wanted the MAX value, the orignal formula works great if I change the "MIN" to "MAX." So I have to believe the syntx is correct.... ?

Originally Posted by Bernie Deitrick

Array enter:

=CELL("address",INDEX(PercentWorksheet!E13:E500,MATCH(MIN(IF(LineItemTotalRange>0,LineItemTotalRange)),PercentWorksheet!E13:E500,0)))

This works for me.

If that returns #Value! for you, that indicates the formula has not been correctly array entered.
You'll know it's correctly entered when the formula is enclosed in {brackets}

05-Aug 04.50.57 PM.jpg

Screenshot attached.

I have brackets around it. Still returns #Value! for me.

Perhaps you could attach a sample XL file.
Pictures don't really tell the whole story.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Sample File attached. Did the best I could to replicate all of the above.

Sample.xlsx

Originally Posted by daddylonglegs

I think the problem is related to what I suggested - LineItemTotalRange is not a continuous range, so that won't work in this sort of formula, why not just use the same range all the way through?

=CELL("address",INDEX(PercentWorksheet!E13:E500,MATCH(MIN(IF(PercentWorksheet!E13:E500>0,PercentWorksheet!E13:E500)),PercentWorksheet!E13:E500,0)))

That gives me $E$30 as required

I guess I was making it more difficult than I should. I know why I excluded thinking that...because that logic doesnt work on MAX because within that range are subtotals and its possible for a subtotal to be greater than a single line item thus not returning a true MAX value. So using the same range on max wouldn't work....but I do think in this instance it works on MIN since a subtotal would never be considered a min value. thanks for the help!