Average sum

[samiroma]

Hello,
I have a problem with averaging out some numbers.

The average formula im using is not calculating as it should consider also if N/A

=IF(AND(G6,G12,G18,G24,G30,G36="N/A"),"N/A",AVERAGE(G6,G12,G18,G24,G30,G36))

If I use only AVERAGE(G6,G12,G18,G24,G30,G36)) formula the problem is that if the fields are all N/A then it will give me a DIV/0! and I cant have that as it will mess up the formula in another sheet I will be adding.

Please see the attached sheet. I'm trying to fix cell G50 Formula.

Thanks for your help!
Final upload should be the correct one.audittool1.xlsx

[Special-K]

For a start you're using the wrong syntax. This

AND(G6,G12,G18,G24,G30,G36="N/A")

would need to be

AND(G6="N/A",G12="N/A",G18="N/A",G24="N/A",G30="N/A",G36="N/A")

[FDibbins]

Form what I can make out, you want G6 etc to show NA if ANY values in G4:G5 are NA?

Also, you have a few things not quite right there.

1. You seem to over-complicate your formulas
If D is always a %, then you dont need to calc that as a weight, it already is that
H4=IF(G4="N/A", "N/A", G4 * D4 / SUMIF($G$4:$G$5, "<>"&"N/A", $D$4:$D$5))
just needs to be...
=IF(G5="N/A", "N/A", G5 * D5)
Copied down

Instead of just N/A - which excel sees as just text, would you consider using #N/A - which excel sees as an error?

G6=IF(AND(H4:H5="N/A"),"N/A",SUM(H4:H5))
You have this ARRAY entered (wrapped in {}, this is not necessary, and could slow your file down if used a lot.

(working on more, for you)

[FDibbins]

G6=IF(AND(H4:H5="N/A"),"N/A",SUM(H4:H5))
could be...
=SUMPRODUCT(D4:D5*G4:G5)

[samiroma]

Dear All thanks for your replies.
I have reviewed your replies however my problem is currently in Cell G50. All other cells are fine and calculating well. However just a quick reply since you
took time to reply I tried your solutions to improve my sheet:

Special-K, correcting the syntax didn't resolve the issue, now I get a #value! error.
FDibbins if I dont use the array to calculate the total in G6 I will get a #Value error no matter what is displayed in G4 or G5
FDibbins =SUMPRODUCT(D4:D5*G4:G5) will give #Value! error if in G4 or G5 there is N/A value

Again my issue is with formula in cell G50 or G52

The average formula im using (below) is not calculating the averages of G6,G12,G18,G24,G30,G36,G42,G48 as it should consider also N/A. This is need to fix.

G50=IF(AND(G6,G12,G18,G24,G30,G36="N/A"),"N/A",AVERAGE(G6,G12,G18,G24,G30,G36,G42,G48))
(by the way should I change the value of G46 to give different result if G48 then the G50 formula will work as a charm!! But its only doing it for G48 I dont know why)

If I use only G52=AVERAGE(G6,G12,G18,G24,G30,G36,G42,G48) formula the problem is that if the fields are all N/A then it will give me a DIV/0! and I cant have that as it will mess up the formula in another sheet I will be adding.

Thank you all for your efforts!
Added updated version with all columns unhidden.
audittool2.xlsx

[Phuocam]

Try this ...
=IFERROR(AVERAGE(G6,G12,G18,G24,G30,G36,G42,G48),"NA")

[JohnTopley]

In G50

=IF(AND(G6="N/A",G12="N/A",G18="N/A",G24="N/A",G30="N/A",G36="N/A",G42="N/A",G48="N/A"),"N/A",AVERAGE(G6,G12,G18,G24,G30,G36,G42,G48))