Mileage log Trouble

user_15661250 0
Viewed 24

I am trying to create a formula that finds the monthly mileage for a vehicle. The simple =max(array)-min(array) will not work because there are blanks/0 cell values due to a vehicle not being driven. This causes the minimum to be zero. I have tried to use the small(array,2) but if there are multiple cells, it counts every blank as a separate value. Is there a simple way to express this? All cells are pulling the mileage from a separate workbook so even the blank cells have a formula. I want to write a formula for the following expression, "the cell with the maximum mileage minus the cell with the minimum mileage but the minimum cell can not be zero or blank, if it is skip/do not use".

Example for a week I know the maximum for the first box is 40532 - minimum is 40285= 247 The maximum for the second box is 13281 - minimum is 13154= 127

difference excel-formula excel
1 Answers

You can use MAXIFS()for this: it calculates a maximum, in case of criteria.
MAXIFS() is described in this URL.

Edit for more explanation

I've just created the following worksheet:

     A   B   C
1: 100 200 300
2:   0 100 300
3: 400  50 200
4:   0 100 100

I have entered this formula:

=MINIFS(A1:C4;A1:C4;">0")

The first A1:C4 is the range, where the minimum should be calculated.
The second A1:C4 is the range, where the criterion should be validated.
">0" is the criterion itself.

The result was 50, as it should be.

user_42791550
Related