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
--
Diddy
"Mike H" wrote:
> 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" wrote:
>
> > 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 :-)
> > --
> > Diddy
> >
> >
> > "Mike H" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Mike H" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Average ignores blanks cell so
> > > > >
> > > > > =AVERAGE(E8,H8)
> > > > >
> > > > > does what you want.
> > > > >
> > > > > Mike
> > > > >
> > > > > "Diddy" wrote:
> > > > >
> > > > > > 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
> > > > > > --
> > > > > > Diddy
|
Similar Threads
