Aggregate Function based on an If Statement???

[mack_76]

Hi

I'm trying to work out the avergare of column AE which contains some #NA's but I only want the average if P matches a specifc criteria, in this case "T". I have tried various ways in order to get this but I'm failing,

AGGREGATE(IF(P2:P134,"t"),1,6,AE2:AE134) Result = #VALUE!

IF(P2:P134,"t",AGGREGATE(1,6,AE2:AE134)) Result = #VALUE!

IF(AGGREGATE(1,6,AE2:AE134),P2:P134,"T") Result = F

Can anyone help PLEASE!!!

[oeldere]

Have you tried this one?

AverageIF(P2:P134,"t")

[mack_76]

Hi

Column P only contains one of the following, T, F, WB, NWB or M. AverageIF(P2:P134,"t") will not work because I haven't tied in any of the actual values which are in AE?

Any other suggestions?

[XOR LX]

Hi,

=AVERAGEIF(P2:P134,"t",AE2:AE134)

Regards

[mack_76]

Thanks but this doesn't work either due to the #NA's within AE. I have tried to use ISNUMBER as well but wasn't successful...

[XOR LX]

Apologies - didn't read your question properly.

You will need an array formula**:

=AVERAGE(IF(ISNUMBER(AE2:AE134),IF(P2:P134="t",AE2:AE134)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[mack_76]

Thank you, thank you, thank you - it worked!!!

[XOR LX]

You're welcome!

[Saarang84]

Hi,

Can u post a workbook with sample data (remove sensitive information, but not the #N/A's) so that we can work out a solution?

[mack_76]

Hi Sarang

Thank you for your assistance but XOR LX has resolved the problem for me.

Thanks

[mack_76]

Hi Sarang

Thank you for your assistance but XOR LX has resolved the problem for me.

Thanks

[Saarang84]

Try,

Please Login or Register  to view this content.

Extend worksheet ranges as necessary

[XOR LX]

Try,

Please Login or Register  to view this content.

Extend worksheet ranges as necessary

Have you tested this yourself? What results did you get?

Are you trying to array-enter an AVERAGEIF formula?

Do you know that the ranges in this formula must be actual worksheet references, and not the results of formulas?

Regards

[Saarang84]

Hi XOR,

This does work well.. I tested it before posting it here. Refer workbook attached. However, this formula doesn't seem to work when paste in the row where #N/A is found. Wonder why is it so? Do you know the reason?

[XOR LX]

Hi XOR,

This does work well.. I tested it before posting it here. Refer workbook attached. However, this formula doesn't seem to work when paste in the row where #N/A is found. Wonder why is it so? Do you know the reason?

Go through your formula with the Evaluate Formula tool. See what happens when it gets to:

IF(ISNUMBER($A$2:$A$11)

Only the first element of that range (A2) is passed to the function, since nothing is coercing it to operate on the entire array.

Regards

[Saarang84]

Hi XOR,

After evaluating the entire formula, I get this...

=AVERAGEIF({"F";"T";"WB";"NWB";0;0;"F";"T";"WB";"NWB"},"WB",{10;32;35;21;0;0;30;28;15;19})

But it does return the correct average value.. How does this work?

[XOR LX]

You haven't used the Evaluate Formula tool. You've pressed F9 repeatedly in the formula bar, which is a dangerous thing to do if you're not fully aware of how this feature operates, since there arrays are "evaluated" even if the actual formula in question would not otherwise evaluate them as such, as in your case.

Regards