How to remove a cell from a formula with a formula

I'm kinda new to excel but not so much to programming however, I have run into a snag trying to complete a formula to remove cells from a different formula if they equal zero. The data in these cells change month to month and instead of removing the cells from the equation manually I'm trying to set up a formula to do it automatically.

So here is the original formula:

=MIN(M19:N49)

Simple right. Find the lowest number in the dataset. The issue is that cells that return 0 mean that no data was collected for that day. To give an example in February this year there is 29 days. That means days 30 and 31 will return 0. M19/N19 = day 1 and M49/N49 = day 31. So I tried to create a formula to fix this issue automatically by looking at the dataset for 0's and then removing them from the equation to give me a real minimum number.

This formula is where I'm at:

=IF(M19:N49=0,M50-(M19:N49=0))

I tried to amend the original formula ~=MIN(M19:N49)~ as well to include the removal of the cells that equal zero but I'm honestly at a loss. Can someone help me?

Originally Posted by sweep

Hi,

Try MINIFS...

=MINIFS(M19:N49,M19:N49,">0")

This returned with a #NAME?

But looking at the formula you provided I'm not sure how this would work.

test: M19:N49
true: M19:N49
false: ">0"

That doesn't seem quite right.

Originally Posted by hemesh

I don't think Minifs is available with EXCEL 2010 . Copy paste below then hold control and shift together then hit enter to make it array formula

Formula:

Please Login or Register  to view this content.

Array formulas are not available for merged cells.

And it returned with a #Value!


Thank you for helping me with this

=AVERAGEIF(D19:D48,"<>0")

Is there a minimum value equivalent to this above?

This works for averaging.

Here is a sample

The following doesn't require array formula entry, but it performs additional calculations.

=MIN(INDEX(M19:N49+1E300*(M19:N49<=0),0))

This works by adding a huge number to nonpositive values. INDEX(array,0) eliminates the need for array formula entry as long as the array term uses little more than arithmetic or string operators.