AVG or Return 1 value from either of 2 cells if one is blank

[Diddy]

Hi everyone,

I'm using the following formula to average the values of 2 cells. If either
is empty, I want it to return the value in the non-blank cell.

=IF(AND(E8>0,H8>0),AVERAGE(E8,H8),SUM(E8,H8))

This works fine but is there a better way to do it?

Cheers

 

[Pete_UK]

Here's another way (avoiding the IF):

=SUM(E8,H8)/COUNT(E8,H8)

Hope this helps.

Pete

 

[Mike H]

Hi,

Average ignores blanks cell so

=AVERAGE(E8,H8)

does what you want.

Mike

 

[Diddy]

Hi Mike,

Sorry Mike, I've misled you! The data is coming in from worksheets prepared
by different people from a database. I've no control over what is put in the
db fields, so in some cases the missing data may be left blank or may be a
zero.

The formula I have does work but I wondered if there was a more efficient way.

Cheers

 

[Diddy]

Hi Pete,

Sorry MY fault. I did not phrase the question or the subject line correctly.

E8 or H8 can be either a value >0, 0 or blank. If both are a value then I
need the avg. If either one is blank or 0 then I need the other value
returned.

Thanks to both you and Mike for replying and sorry again

 

[Mike H]

Hi,

It's irrelevant where the data are coming from. If your formula works well
then put my formula alongside yours is a cell and try and make them give
different answers and you'll fail to do so because they will always return
the same one. Including #DIV/0 of both cells are empty.

You asked for a simpler way and as of yet the one I posted is the simplest
and meets all of the requirements


Best regards,

Mike

 

[Diddy]

Hi Mike,

I seem to have lost the gift of communication today. Not meaning to be rude
at all and very grateful for reponses.

What I meant to convey (and not succeeding at all) is that in the original Q
and the subject line, I've asked for ways to deal with blanks but the value
can be >0 if there is a score for the test, or 0 or blank.

0 or blank is if the test was missed (what I was trying to say when waffling
about data being from DB, was that if we could control the input at the db
stage then that would be a good solution).

Sorry again

 

[Mike H]

Hi,

Now that's a different question. Not necessarily better then your but
another way

=AVERAGE(IF(ISNUMBER(MATCH(COLUMN(E8:H8),{5,8},0))*(E8:H8>0),E8:H8))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Diddy]

Hi Mike,

Yes that does the trick!

I'll probably stick with the original but learning alternative ways helps
more than you will know

Thank you