Combining Excel Logical Tests

[mbaldw]

Hi folks,

does anyone know if it's possible to combine formulae functions in Excel?   I'm looking to see if I can combine the IF and SUMIF logical tests.   Here's why:

I have a table of data and the bottom of each column has a mean value.   However, what I'm trying to accomplish is to get the mean calculation to exclude any values below a certain threshold (in the example below, results less than 30) and present an average of only the cells (in column D in the example) containing values equal to or greater than 30.   I can do this using the SUMIF and COUNTIF functions:

=SUMIF(D1:D8,">=30")/COUNTIF(D1:D8,">=30")

However, I would also like to get Excel to put a text string of Too Low if the resulting average is below this same value of 30.   If I forget about the SUMIF/COUNTIF part of the equation and just use a basic average equation to get the mean instead, I can get the text to appear with the following IF function:

=IF((AVERAGE(D1:D8))<=30,"Too Low",AVERAGE(D1:D8))

I have tried combining the two functions to produce:

=IF((SUMIF(D1:D8,=>30)/COUNTIF(D1:D8,">=30"))<30,"Too Low",(SUMIF(D1:D8,=>30)/COUNTIF(D1:D8,">=30"))

but this doesn't work as Excel tells me there's an error with the logical test.   I presume the problem is that Excel doesn't like using IF and SUMIF in such close proximity?

I hope the above makes sense!   Any suggestions or advice would be very much appreciated.

Cheers,
Marc.

[Mark H]

Marc,

After fixing the errors in your formula, it should look like the following:

=IF(SUMIF(D1:D8,">=30")/COUNTIF(D1:D8,">=30")<30,"Too Low",(SUMIF(D1:D8,">=30")/COUNTIF(D1:D8,">=30")))

One thing I notice is that you are making it more complex than needed. If you sum all numbers greater than 30 and then divide by the count of all numbers greater than 30, your result will always be greater than 30. In a nutshell, you do not need the test since there is no way for an average of greater than 30 numbers to end up less than 30. Even if all the numbers are 30, if you add 8 of them together and divide by 8, you will get an average of 30.

I hope this helps.

Mark H

[mbaldw]

Thanks Mark.   I get what you mean about the averaging values over 30, but there will be times when all the data in the column are below 30 and in those cases, I want it to show "Too Low".   I will try your corrections and see if it works.

Cheers,
Marc.

[Mark H]

If all the data in the table is below 30, then your countif will be zero and you will get a dividing by zero error. You should change the formula to the following:

=IF(COUNTIF(D1:D8,">=30")=0,"Too Low",(SUMIF(D1:D8,">=30")/COUNTIF(D1:D8,">=30")))

This will result in "Too Low" being shown when none of the numbers are greater than 30. This will do the same thing you were trying to do with the other formula, except it eliminates a possible division by zero error.

Mark H

[mbaldw]

Spot on, Mark!   That did exactly what I wanted it to.   Thanks very much for your help.

Cheers,
Marc.