To subtract the MIN of a group from the MAX of a group and only use 0 numbers in the group, you can use the following formula:
Formula:
=MAX(IF(A1:A40,A1:A4))-MIN(IF(A1:A40,A1:A4))
Assuming your data is in cells A1:A4, this formula will first check if each value is greater than 0 using the
IF function. If the value is greater than 0, it will include it in the
MAX and
MIN calculations respectively. Finally, it will subtract the
MIN from the
MAX to give you the desired result.
Note that this is an array formula, so you will need to press
Ctrl+Shift+Enter instead of just Enter when entering the formula into the cell. This will add curly braces around the formula to indicate that it is an array formula.
Steps:- Check if each value is greater than 0 using the IF function.
- Include the value in the MAX and MIN calculations respectively.
- Subtract the MIN from the MAX to give you the desired result.