AverageIfs returns #div/0 even when all there are no blanks...why?

[wiseone]

Hi all,

I have the following data:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12345[/TD]
[TD]Parent[/TD]
[TD]10[/TD]
[TD]#Div/0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345A[/TD]
[TD]Child[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12345B[/TD]
[TD]Child[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12345C[/TD]
[TD]Child[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to write a formula for column D which takes the average of the child SKUs, however, I keep getting #Div/0.

Formula in column D:
=IF(B1="Parent",AVERAGEIFS(C:C,A:A,left(A1,5),B:B,"Child"),C1)

All data in Column A is TEXT and must remain so.

Some troubleshooting has shown that the "A:A,Left(A1,5)" criteria is causing the error, but I can't understand why......

Any thoughts on what is going on here and how to fix?

Thanks

 

[T. Valko]

The reason you're getting the error is because this:

A:A,left(A1,5)

Is being evaluated as:

A:A = "12345"

There are no rows that meet both conditions:

A:A = "12345" *AND* B:B = "Child"

 

[wiseone]

I think I figured this out......
Left (a1,5) is the criteria, and it is not looking at the left 5 charaters of all the cells in A:A so it doesn't find anything that meets the criteria.

One solution is to add a helper column which removes the A/B/Cs. =Left(A1,5).

Does anyone know how to do this without a helper column?

 

[T. Valko]

Maybe this array formula**:

=IF(B1="Parent",AVERAGE(IF(LEFT(A2:A4,5)=LEFT(A1,5),IF(B2:B4="Child",C2:C4))),C1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You should avoid using entire columns as range references in array formulas. Use smaller specific ranges.

 

[Marcelo Branco]

Or maybe this regular formula
=IF(B1="Parent",AVERAGEIFS(C:C,A:A,LEFT(A1,5)&"*",B:B,"Child"),C1)

M.

 

[wiseone]

=IF(B1="Parent",AVERAGEIFS(C:C,A:A,LEFT(A1,5)&"*",B:B,"Child"),C1) didn't work because of same issue. Target reference A:A is not looking at left 5 characters.

The dataset changes size so do not want to use array formulas or dynamic named ranges (file is already slow, trying to minimize using volatile functions).

Is there any way to do Left(A:A, 5) in a formula which references a range?

 

[Marcelo Branco]

@Tony

Thank you for the like. I think you deserve a KISS

M.

 

[Marcelo Branco]

=IF(B1="Parent",AVERAGEIFS(C:C,A:A,LEFT(A1,5)&"*",B:B,"Child"),C1) didn't work because of same issue. Target reference A:A is not looking at left 5 characters.

It worked perfectly for me

M.

 

[T. Valko]

It worked perfectly for me

Also worked for me.

 

[wiseone]

hmm I will try again, thanks!