Average with condition

[Khalil Handal]

Hi,
Cells E21, F21, G21 has numbers, Cell H21 is the aveage of Range E21:G21
I want to see the average in cell H21 only if ANY TWO cells from the range
has numbers in it.

In other words:
Nothing will be shown in cell H21 if there is ONE number in the range.

 

[Nick Hodge]

Khalil

If for numbers you mean anything then

=IF(COUNTA($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"")

Will work

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Bob Phillips]

I think you mean

=IF(COUNT($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"")

Mr H

 

[Nick Hodge]

Ooops...still awake in Dorset? Good job! ;-)

Thanks Bob

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Khalil Handal]

Hi again,
To make it more clear:

With regular numbers it works well, BUT my sheet is actualy more complicated
and the vlues in the range are from an other sheet.
My formula that I concluded from yours is as follows:

=IF(COUNTA(E21:G21)>1,AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0)),"")

and it stil gives the answer when only ONE number is there!!
any other suggestions???

 

[Nick Hodge]

Khalil

I guess that means the cells return zero from the other sheet. As it is
only three cells, why not use an IF in them to return Null

=IF(MyOtherSheetValue=0,"",MyOtherSheetValue)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Khalil Handal]

The other sheet (Marks) has three values (marks) in three successive cells.
I will try to figure out how to write the formula replacing
"MyOtherSheetValue" and see if I manage!

This looks more rsonable and ogical for me.

 

[Khalil Handal]

Hi,
the idea worked fine for me and this is the formula I have:

=IF(COUNT(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0))<2,"",AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0)))

Thanks to all of you

 

[Nick Hodge]

Khalil

Thanks for getting back, glad you got it working

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/