How to ignore #N/A when getting an average

[Muse83]

Hi guys, I'm setting up a spreadsheet so that I can send it to my manager for inputting various data. I want to make one section be able to calculate an average of specific indicators for the month but ignore when one of those cells may have an #N/A. Here's my current formula, thank you!

=AVERAGE(Data!C6,Data!C11,Data!C16,Data!C21,Data!C26,Data!C31,Data!C36,Data!C41,Data!C46,Data!C51,Data!C56,)

[Jonmo1]

Is there anything 'common' in an adjacent column that indicates which rows need to be averaged, like the word Total or something?

Can you attach a sample book?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Muse83]

see attachment
Thank you

[Jonmo1]

Try this in the Overall Tab, C6 and filled right.

=AVERAGEIFS(Data!C:C,Data!$B:$B,"Rate",Data!C:C,"<>#N/A")

[Muse83]

Sir that worked like a charm! Thank you!!

[AlKey]

Maybe also adding IFERROR with NA()

Please Login or Register  to view this content.

[Jonmo1]

You're welcome.

[Jonmo1]

I was just going to say you might be better off correcting the #N/A errors in the first place
Then your original formula would work just fine.

But I see that you put the #N/A errors there on purpose with
=IFERROR(C4/C5,#N/A)

I would assume that is to hide the #Div/0! errors when C5 is blank.

But why hide 1 error by just creating a different error?

Why not do this instead?
=IFERROR(C4/C5,"")

The cells will just appear blank when C5 is blank, and then your original average function will work just fine.

[Muse83]

That's how I originally had it but they want to see when a month simply doesn't have any data as opposed to there being 0% on the graphs I had set up per department (wasn't shown in example)

[Jonmo1]

OK, then

=IFERROR(C4/C5,"N/A")

This is now just a TEXT string N/A, instead of an ERROR #N/A
Your original average will still ignore it.

[Muse83]

Just tried and while it works fine on the data sheet it still comes up as 0% in the graph

[Jonmo1]

OK, so the reason for the #N/A error is so that the graph will ignore it.

Fair enough.

I was just curious.