AVERAGEIF a range of cells based on a specific value in adjacent cell but ignore empty cells?

browncoat · Jun 28th 2020

Code

=AVERAGE(AVERAGEIF(Standards['[35S']GTPyS],[@Nominal],Standards[Rep1]),AVERAGEIF(Standards['[35S']GTPyS],[@Nominal],Standards[Rep2]),AVERAGEIF(Standards['[35S']GTPyS],[@Nominal],Standards[Rep3]))

I have three values I'd like to average based on the adjacent cell (@NOMINAL) in a table and it works if all the cells have values but if one of them is empty it returns #DIV/0. Is there a way I can have it average only the two values if the third is empty?

Thanks

sobi_256 · Jun 28th 2020

can u please attach a sample workbook please

browncoat · Jun 28th 2020

Sure, sorry

**royUK** · Jun 28th 2020

The AVERAGE, AVERAGEIF, and AVERAGEIFS function all automatically ignore blank cells and cells that contain text values.

What are you actually trying to average?

sobi_256 · Jun 28th 2020

if a averageif range is empty then u will get div/0 error, like if rep3 range has no values for nominal -10 it will give an error

sobi_256 · Jun 28th 2020

i can only come up with the attached solution,had to insert some columns, thr might be a way to get the desired answer without inserting anything, but i do not know that

browncoat · Jun 29th 2020

Quote from sobi_256

i can only come up with the attached solution,had to insert some columns, thr might be a way to get the desired answer without inserting anything, but i do not know that

Thanks Sobi

AVERAGEIF a range of cells based on a specific value in adjacent cell but ignore empty cells?

Participate now!

Share