IF and AND statements then average the sum

[andyporter1975]

Hi everyone,

Hopefully somebody can help me with this one

I'm using Excel 2013 on Office 365

Column A Contains the 'Check Type' - this can be Core or Zone
Column B contains the department - this can be 'ambient', 'chilled', or 'frozen'
Column C contains time (00:00:00)

I need a formula that deals with IF and AND statements and then returns an average of the sum e.g. it will look up all of the Core Ambient times and return an average of the total


Thank you in advance

[BadlySpelledBuoy]

You wont need IF and AND for that. AVERAGEIFS is your friend here.

For example:

Please Login or Register  to view this content.

See yellow cells in the attached.

BSB

[andyporter1975]

Many thanks for your quick reply - I'll try it now and let you know how I get on!

[BadlySpelledBuoy]

I've added an example workbook above now to demonstrate it.
Should be easy enough to port over to your real workbook but shout if you need more help.

BSB

[andyporter1975]

I can't get it to work when I use it on my actual spreadsheet as it's returning a #DIV/0 error. I've amended the formula to take into account that the ranges start at row 16 and go through to row 2000 - will this cause the error?

=AVERAGEIFS($AB16:$AB2000,$I16:$I2000,$AE$5,$J16:$J2000,$AF$4)

[BadlySpelledBuoy]

A #DIV/0 error means the result would be dividing 0 by 0, which is of course mathematically impossible.

Filter the data so column I equals the value in AE5 and column J equals the value in AF4. Would the sum of column AB = 0 in that scenario?

BSB

[andyporter1975]

I've applied the filters and despite there being data in column AB, the SUM does in fact = 0. The format of the cells in AB is Time, is this causing the issue?

[BadlySpelledBuoy]

The issue is purely that the answer is zero.

What the formula is doing is summing the values in AB for all rows with the same value as AE5 in column I and the same value as AF4 in column J. Then dividing that number by the number of rows those two conditions are met on. Although there are rows that meet those criteria, the sum of AB for those row is zero and you cannot divide that.

Amend the formula to:

Please Login or Register  to view this content.

This will simply show a zero in these scenarios, rather than the DIV error.

Or swap ,0) at the end for ,"") if you'd rather the cell stayed blank.

BSB

[andyporter1975]

I understand what you're saying, but even when I try to just add up the times in column AB as a simple =SUM() the answer is still 0 despite there being plenty of data in the column.

[BadlySpelledBuoy]

Are the times stored as text by any chance?

Could we see a small (desensitized) sample workbook?

BSB

[andyporter1975]

Despite the formatting of the cells saying that they were 'Time' it appears that yes indeed, the times were stored as text. I've resolved this by doing the following:

Highlight the range of text to be converted.
Go to Data, Text to Columns.
Click Finish. The numbers are no longer considered numbers stored as text.

Thank you for your help today, its greatly appreciated.

[BadlySpelledBuoy]

Happy to help

BSB