Search code examples
excelformula

Average forumula in excel with multiple columns and sample sizes

I try to average two columns where each row is the result of different amounts of samples. I can do this fine, but as soon as one row has no score due to 0 sample, I get #VALUE!. How can I get around this?

Example: (column names are the top letters for reference)

Example

The #DIV/0! comes from the impossibility to calculate when it's no sample. I tried adding an IFERROR to get NO result there instead, but the end result of the image above was the same

The formula for Overall Average (for the orange one):

=(
IFERROR((L1*N1);"")
+IFERROR((L2*N2);"")
+IFERROR((L3*N3);"")
+IFERROR((L4*N4);"")
+IFERROR((L5*N5);"")
+IFERROR((L6*N6);"")
+IFERROR((L7*N7);"")
+IFERROR((L8*N8);"")
+IFERROR((L9*N9);"")
+IFERROR((L10*N10);"")
+IFERROR((L11*N11);"")
+IFERROR((L12*N12);"")
)
/
(
IFERROR(N1;"")
+IFERROR(N2;"")
+IFERROR(N3;"")
+IFERROR(N4;"")
+IFERROR(N5;"")
+IFERROR(N6;"")
+IFERROR(N7;"")
+IFERROR(N8;"")
+IFERROR(N9;"")
+IFERROR(N10;"")
+IFERROR(N11;"")
+IFERROR(N12;"")
)

NOTE:

  1. I added the IFERROR's when I first got the #VALUE!. Now it doesn't seem like it's needed / the right solution of course. So it could be trimmed.

  2. The reason I'm not using a pivot table from the source data that these two tables are based on, is that with forumlas, I get a file about 150kb instead of a couple MB. The file is also much faster to open than when I used multiple pivots.

Solution

  • I solved it with adding this IFERROR to the column L forumla: =IFERROR(AVERAGEIFS(INDIRECT("Rawdata["&K2&"]");Rawdata[Spec‌​ific column];"Specific value");) Notice how there's nothing in the end where "value if error" should be.