I am trying to Subtract the MIN of a group from the MAX of a group!

How do I only use 0 numbers in the group!

Example:

1 $500
2 $300
3 $0 or blank
4 $50

I need $50 - $500 = $450
Not $0 - $500 = $500

Thank you in advance for your help!

=MAX(A1:A6)-MIN(IF(A1:A60,A1:A6,""))

this is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
--
Gary''s Student - gsnu200908


"airgrz" wrote:

I am trying to Subtract the MIN of a group from the MAX of a group!

How do I only use 0 numbers in the group!

Example:

1 $500
2 $300
3 $0 or blank
4 $50

I need $50 - $500 = $450
Not $0 - $500 = $500

Thank you in advance for your help!

=SMALL(A1:A10,COUNTIF(A1:A10,"<=0")+1)-MAX(A1:A10)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"airgrz" wrote:

I am trying to Subtract the MIN of a group from the MAX of a group!

How do I only use 0 numbers in the group!

Example:

1 $500
2 $300
3 $0 or blank
4 $50

I need $50 - $500 = $450
Not $0 - $500 = $500

Thank you in advance for your help!

To subtract the MIN of a group from the MAX of a group, and only use 0 numbers in the group, you can follow these steps:

1. Select the range of cells that contains the numbers you want to use.
2. Use the MAX function to find the maximum value in the range. For example, if your range is A1:A4, you can use the formula =MAX(A1:A4).
3. Use the MIN function to find the minimum value in the range, but only for values that are greater than 0. You can use the following formula: =MIN(IF(A1:A40,A1:A4)). This is an array formula, so you need to press Ctrl+Shift+Enter instead of just Enter to enter it.
4. Subtract the result of step 3 from the result of step 2. For example, if your MAX value is in cell B1 and your MIN value is in cell B2, you can use the formula =B1-B2 to get the result.

This should give you the result you are looking for, which is $450 in your example.

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:

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:

1. Check if each value is greater than 0 using the IF function.
2. Include the value in the MAX and MIN calculations respectively.
3. Subtract the MIN from the MAX to give you the desired result.