averageifs #DIV/0!

[Vally 88]

Hello Guys,

I'm working on a AVERAGEIFS formula, please find below:

=AVERAGEIFS('Arabic Pacing'!$J$32:$J$61,'Arabic Pacing'!$E$32:$E$61,Trends!$A4,'Arabic Pacing'!$J$32:$J$61,">0")
Practically I want to have an average when same day of the week and higher than 0.

Looking inside the formula, where first bit is the metric I want to average and it's formulas (NO ACTUAL NUMBERS), first criteria range is again formulas, 1st criteria is a string, 2nd criteria range is again formulas (it's the same range of the average). I have tried to run the 2 criteria separately and they work but when combining the 2 the results is #DIV/0!. I think that the problem is that the actual criteria aren't numbers but formulas and this is stopping it from working. Could be this the reason? If so there is any way to work around this?

Again thank you very much for all your help!

Valeria

 

[steve the fish]

You can only have an average of numbers.

 

[Joe4]

Looking inside the formula, where first bit is the metric I want to average and it's formulas (NO ACTUAL NUMBERS)

Just to clarify, if your formulas are returning valid numbers, you can average those.
It doesn't matter whether those numbers are hard-coded or returned by formulas.

 

[Vally 88]

Hello,

My formulas are returning valid numbers but the actual average is returning #DIV/0!. I find strange I can't average numbers returned by formulas because I can if a use just one condition.

 

[Vally 88]

To be clear: the below are working:
=AVERAGEIFS('Arabic Pacing'!$J$32:$J$61,'Arabic Pacing'!$E$32:$E$61,Trends!$A4)
=AVERAGEIFS('Arabic Pacing'!$J$32:$J$61,'Arabic Pacing'!$J$32:$J$61,">0")

but the integrated isn't working:
=AVERAGEIFS('Arabic Pacing'!$J$32:$J$61,'Arabic Pacing'!$E$32:$E$61,Trends!$A4,'Arabic Pacing'!$J$32:$J$61,">0")

 

[Joe4]

MI find strange I can't average numbers returned by formulas because I can if a use just one condition.

As I mentioned, you can!

What the #DIV/0 is telling you that there are no values to average!
Are you sure that your values are being returned as numbers and not as text?
One easy way to check is with the ISNUMBER formula.
Pick one of your cells returning a number (let's say it is C1), and enter this formula anywhere on your sheet.
=ISNUMBER(C1)
If it returns FALSE, your formulas are returning Text and not Numbers (Text that looks like Numbers is still Text!).
You need to modify your formulas so that they return numeric values.

EDIT:

but the integrated isn't working:
=AVERAGEIFS('Arabic Pacing'!$J$32:$J$61,'Arabic Pacing'!$E$32:$E$61,Trends!$A4,'Arabic Pacing'!$J$32:$J$61,">0")

It could be that no records match your criteria, so it is not returning any values to average. That also results in a #DIV/0 error.

 

[steve the fish]

Ah ok. It will mean that no entry matches your criterias.

 

[Vally 88]

Hi,

they are numbers and valid numbers, even =isnumber returns me TRUE. So this is not the problem, what else can be?

 

[Vally 88]

they are because the 2 formulas work perfectly separately. so all the criteria are met.

 

[steve the fish]

If those formulas work seperately it doesnt mean the criteria is met when looked at together.