Min/Max/Average Arrays not working - HELP!

[kylc3]

I have several sets of data that I'd like to be able to use the same array formulas for but I'm having an issue if the data set is not complete, meaning the range in the array formula is longer than the available data in the data set. I'm pulling in data from a larger table into several smaller data set tables. When the table has a complete data set, the formulas work, but if the data available is not complete, I'm getting errors. Here's an example of two small data sets that are pulling in data from a larger table.

* Please note that data populating both DATA SETS is being pulled in via INDEX MATCH formulas from a larger data table. In DATA SET 2, rows 8,9, & 10 are not technically blank. They are pulling in "" from another table.

DATA SET 1:
___A: B:
1: 2 1
2: 4 3
3: 6 5
4: 8 7
5: 10 9
6: 12 11
7: 14 13
8: 16 15
9: 18 17
10: 20 19

DATA SET 2:
___A: B:
1: 2 1
2: 4 3
3: 6 5
4: 8 7
5: 10 9
6: 12 11
7: 14 13
8:
9:
10:

Here are the formulas I'm using:

{=MAX(A1:A10+B1:B10)}
{=MIN(A1:A10+B1:B10)}
{=AVERAGE(A1:A10+B1:B10)}

DATA SET 1 works fine, but the issue is DATA SET 2, which is returning #VALUE! for each array formula. If I manually change the range in the arrays or manually delete the INDEX MATCH formulas pulling the blank data in to cells it works, but I'm working with too much data to manually change things.

Does anybody know why the array formulas won't work? Any suggestions? Thanks for the help!!!

 

[63falcondude]

Try including a condition of not blank.

{=MAX(IF(A1:B10<>"",A1:A10+B1:B10))}
{=MIN(IF(A1:B10<>"",A1:A10+B1:B10))}
{=AVERAGE(IF(A1:B10<>"",A1:A10+B1:B10))}

 

[Scott T]

The blank you are returning from your Index match formula is not a number so when your MAX/MIN/Average formula attempts to add it gives an error. If you return zero instead of "" it should work.

 

[dreid1011]

The blank you are returning from your Index match formula is not a number so when your MAX/MIN/Average formula attempts to add it gives an error. If you return zero instead of "" it should work.

0 will skew the AVERAGE and MIN formulas though.

Another possible solution:

{=MAX(IFERROR(A1:A10+B1:B10,""))}
{=MIN(IFERROR(A1:A10+B1:B10,""))}
{=AVERAGE(IFERROR(A1:A10+B1:B10,""))}

 

[kylc3]

0 will skew the AVERAGE and MIN formulas though.

Another possible solution:

{=MAX(IFERROR(A1:A10+B1:B10,""))}
{=MIN(IFERROR(A1:A10+B1:B10,""))}
{=AVERAGE(IFERROR(A1:A10+B1:B10,""))}

dreid1011, you nailed it! Thanks so much!

 

[dreid1011]

dreid1011, you nailed it! Thanks so much!

You're welcome.

Try including a condition of not blank.

{=MAX(IF(A1:B10<>"",A1:A10+B1:B10))}
{=MIN(IF(A1:B10<>"",A1:A10+B1:B10))}
{=AVERAGE(IF(A1:B10<>"",A1:A10+B1:B10))}

Just a note, 63falcondude's solution will work just as well, and he beat me to the punch

 

[63falcondude]

Just a note, 63falcondude's solution will work just as well, and he beat me to the punch