[Solved] Ignoring #DIV/0! Errors with AVERAGEIF.

[lawlore]

Hi all. Been toying with this one for a while now, and not managed to come up with a solution- any help much appreciated.

Column A is for days of the week.
Column B has names of staff, which are entered as they work shifts- one name can appear several times due to them working on different days.
Column C has their sales of regular size of Product A.
Column D has their sales of large size of Product A.
Column E has =D4/(C4+D4) to work out a daily percentage of large size of Product A sold.

Columns CDE repeat for two other products in columns FGH and IJK- these can largely be ignored for the purposes of this explanation, as it's just a repetition of the same issue. But so far, so straightforward.

What I'm trying to do is put together a separate table with a formula to pull together the weekly averages of the percentages for each individual staff members for a particular product, with the Staff Member names listed in Column M. At present it looks like this, in Column N:

=AVERAGEIF($B$4:$B$51;M5;$E$4:$E$51)

As long as there's data, the formula works OK. The problem arises when there's no data for a staff member- when they've sold neither regular nor large, giving the #DIV/0! error in column E. This leads to the error being included when the average is calculated, giving a repeat of the same error in Column N regardless of other, valid, entries.

At first I considered a simple IF function in Column E to test whether the result was greater than zero, and produce " " if it was not:

=IF(D4/(C4+D4)>0;D4/(C4+D4);" ")

This didn't work, and in fact took me a step away from it, as 0 could potentially be a valid result (selling one regular and no large size, giving 0%), but was now being represented as " ", leading to the AVERAGEIF to exclude it altogether in calculation and produce a result from the other data. As such, although I had a close look at this thread, it didn't quite give me what I needed.

I've also tried modifying the AVERAGEIF formula, even replacing it with LOOKUP and COUNTIF functions, adding extra columns to try and work through it step-by-step, but tied myself in knots- I just cannot seem to find a way of excluding error results without also excluding valid zero value results. I get the impression the solution lies somewhere in performing a TRUE/FALSE test to check whether a cell contains a valid value or an error, but have to admit I'm learning as I go here and don't have a great deal of familiarity with the functions required to do that.

Any assistance would be much appreciated- happy to attach the doc/screenshots if requested.

[Zizi64]

try the

IF(ISBLANK(range);"";calculated value)

function

[Alex1]

Isn't it better to calculate the percentages from the weekly totals?
Sum of regular sizes:

Code: Select all

=SUMIF($B$4:$B$51;M5;$C$4:$C$51)

Sum of large sizes:

Code: Select all

=SUMIF($B$4:$B$51;M5;$D$4:$D$51)

Then calculate the percentage from these results. You will get different, but more meaningful results.
The chance of dividing by zero will be much smaller.

[Bald Eagle]

I'm sure there are a number of different strategies and methodologies each with its pros and cons of elegance, readability, and effectiveness.

I use if(isblank()) a lot, but it may be more meaningful to use if(iserror()) because you're looking for errors not blanks.

Just for future reference, if you're also filtering calculation results that have a formula that returns "", you can't use isblank() because the cell has a formula, not a blank.
In that case you can use if (A1 <> ""; Result1; Result2)

[keme]

If you post a file with sample data you help us to help you. Use your original file if it doesn't contain any confidential info, or a copy with names mangled and other irrelevant info removed.

We can always guess why the errors might have occurred, but real data beats guessing every time.

[lawlore]

Alex1 makes a very valid point about taking the average for overall sales for the week, rather than the average of each of the daily percentages, so I've modified to the following:

=(SUMIF($B$4:$B$51;M5;$D$4:$D$51))/((SUMIF($B$4:$B$51;M5;$C$4:$C$51))+(SUMIF($B$4:$B$51;M5;$D$4:$D$51)))

I've attached the spreadsheet as it stands- there's nothing confidential on there. With the new formula it does take into account valid 0% entries (as is the case for Lee's 0% sales), meaning the only remaining issue is hiding the #DIV/0! errors when there is no data at all (e.g. Beth for PM).

[Bald Eagle]

=(SUMIF($B$4:$B$51;M5;$D$4:$D$51))/((SUMIF($B$4:$B$51;M5;$C$4:$C$51))+(SUMIF($B$4:$B$51;M5;$D$4:$D$51)))

So, you can simply test for an error:

Code: Select all

if (
iserror(=(SUMIF($B$4:$B$51;M5;$D$4:$D$51))/((SUMIF($B$4:$B$51;M5;$C$4:$C$51))+(SUMIF($B$4:$B$51;M5;$D$4:$D$51))));
"";
=(SUMIF($B$4:$B$51;M5;$D$4:$D$51))/((SUMIF($B$4:$B$51;M5;$C$4:$C$51))+(SUMIF($B$4:$B$51;M5;$D$4:$D$51)))
)

[Villeroy]

A matter of structured simplicity. WIth the right table layout you don't need a single formula.
And as always, a database would be far easier to use.