Aggregate with Average VS IF & ISERROR help

[Shruder]

when I drag this formula down

Please Login or Register  to view this content.

I get this results. Why is the aggregate not picking up the #VALUE!?

aggregate.PNG

vs using a IF statement and having to add in a ISERROR

Please Login or Register  to view this content.

iserror.PNG

I would like to nest that

Please Login or Register  to view this content.

[ben_hensel]

I'm not seeing a #VALUE! error in the data, so there's no error for the AGGREGATE to pick up in the first place?

Can you attach an example spreadsheet instead of posting pix?

[FlameRetired]

Agreed.

This goes much faster with an actual workbook uploaded ... saves retyping data and guessing.

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[Shruder]

Hi @FlameRetired & ben_hensel ,

I attached a sample workbook for you both. Thank you

[José Augusto]

I suppose you want this.

Try this in S4 and copy down

Please Login or Register  to view this content.

[Shruder]

Hi Jose,

thanks for the response. I was wondering if there was a way to use the Aggregate-Average-ignore errors- instead of the IFERROR function?

[ben_hensel]

It looks like you're trying to feed AGGREGATE information for an AVERAGEIF, but it doesn't have that functionality.

Let's look at how it's interpreting the function:

Please Login or Register  to view this content.

AGG inputs This is telling it to use "Function 1" (which is average) and "Option 6" (which is "ignore errors").
First Range this is the first range input.
Second Range This is second range input, not a logical condition for AVERAGEIF.

So it's assessing the range as {L6:N6, "<>0"} like that, as a 3-cells range of numbers plus another value, a text string. So rather than a conditional, you are feeding it a text string as the fourth input, which freaks it out (because you're ordering it to do math with text) and results in throwing a #VALUE! error.

(Also, BTW, using AGGREGATE / SUBTOTAL on horizontal ranges is not recommended, because they definitely won't handle hidden rows correctly, and could screw up in other ways too).

Try this instead:

Please Login or Register  to view this content.

Rather than wrapping with an IFERROR as an ugly brute force solution, use the COUNTIF function to prevent the AVERAGEIF from running in the case where you would get the #DIV/0! problem.

[José Augusto]

Hi @Shruder

The aggregate function do not work in that way.

If your data has a error (eg. L4:N4 has no error) the option 6 say to function mean to ignore them

Please Login or Register  to view this content.

is correct but

Please Login or Register  to view this content.

is not correct

[Shruder]

Hi Ben & Jose,

thank you for your explanations on why the Aggregate is not the best solution. You are both right and I am utilizing the IF(AND(COUNT(AVERAGEIF

I had initially used the COUNTIF <> 0 to get the numbers, then was trying to using the Average Aggregate based off that. But as you both have pointed out, that will not take care of the averages that have a "0".

The formula

Please Login or Register  to view this content.

does however work and I will use that.

thank you both for your valuable input.