| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
I think you mean
=IF(COUNT($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"") Mr H "Nick Hodge" <(E-Mail Removed)> wrote in message news  EB0C873-1194-4FEA-8D88-(E-Mail Removed)...> 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 Removed)DTHIS > web: www.nickhodge.co.uk > blog (non-tech): www.nickhodge.co.uk/blog/ > "Khalil Handal" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> 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. >> >> > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Ooops...still awake in Dorset? Good job! ;-)
Thanks Bob -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England (E-Mail Removed)DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Bob Phillips" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)... >I think you mean > > =IF(COUNT($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"") > > Mr H > > > > "Nick Hodge" <(E-Mail Removed)> wrote in message > news EB0C873-1194-4FEA-8D88-(E-Mail Removed)...>> 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 Removed)DTHIS >> web: www.nickhodge.co.uk >> blog (non-tech): www.nickhodge.co.uk/blog/ >> "Khalil Handal" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >>> 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. >>> >>> >> > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:AE62167F-FDCD-4616-84B6-(E-Mail Removed)... > Ooops...still awake in Dorset? Good job! ;-) > > Thanks Bob > > -- > HTH > Nick Hodge > Microsoft MVP - Excel > Southampton, England > (E-Mail Removed)DTHIS > web: www.nickhodge.co.uk > blog (non-tech): www.nickhodge.co.uk/blog/ > "Bob Phillips" <(E-Mail Removed)> wrote in message > news:%(E-Mail Removed)... >>I think you mean >> >> =IF(COUNT($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"") >> >> Mr H >> >> >> >> "Nick Hodge" <(E-Mail Removed)> wrote in message >> news EB0C873-1194-4FEA-8D88-(E-Mail Removed)...>>> 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 Removed)DTHIS >>> web: www.nickhodge.co.uk >>> blog (non-tech): www.nickhodge.co.uk/blog/ >>> "Khalil Handal" <(E-Mail Removed)> wrote in message >>> news:(E-Mail Removed)... >>>> 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. >>>> >>>> >>> >> >> > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 Removed)DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Khalil Handal" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message > news:AE62167F-FDCD-4616-84B6-(E-Mail Removed)... >> Ooops...still awake in Dorset? Good job! ;-) >> >> Thanks Bob >> >> -- >> HTH >> Nick Hodge >> Microsoft MVP - Excel >> Southampton, England >> (E-Mail Removed)DTHIS >> web: www.nickhodge.co.uk >> blog (non-tech): www.nickhodge.co.uk/blog/ >> "Bob Phillips" <(E-Mail Removed)> wrote in message >> news:%(E-Mail Removed)... >>>I think you mean >>> >>> =IF(COUNT($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"") >>> >>> Mr H >>> >>> >>> >>> "Nick Hodge" <(E-Mail Removed)> wrote in message >>> news EB0C873-1194-4FEA-8D88-(E-Mail Removed)...>>>> 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 Removed)DTHIS >>>> web: www.nickhodge.co.uk >>>> blog (non-tech): www.nickhodge.co.uk/blog/ >>>> "Khalil Handal" <(E-Mail Removed)> wrote in message >>>> news:(E-Mail Removed)... >>>>> 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. >>>>> >>>>> >>>> >>> >>> >> > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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. "Nick Hodge" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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 Removed)DTHIS > web: www.nickhodge.co.uk > blog (non-tech): www.nickhodge.co.uk/blog/ > "Khalil Handal" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> 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" <(E-Mail Removed)> wrote in message >> news:AE62167F-FDCD-4616-84B6-(E-Mail Removed)... >>> Ooops...still awake in Dorset? Good job! ;-) >>> >>> Thanks Bob >>> >>> -- >>> HTH >>> Nick Hodge >>> Microsoft MVP - Excel >>> Southampton, England >>> (E-Mail Removed)DTHIS >>> web: www.nickhodge.co.uk >>> blog (non-tech): www.nickhodge.co.uk/blog/ >>> "Bob Phillips" <(E-Mail Removed)> wrote in message >>> news:%(E-Mail Removed)... >>>>I think you mean >>>> >>>> =IF(COUNT($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"") >>>> >>>> Mr H >>>> >>>> >>>> >>>> "Nick Hodge" <(E-Mail Removed)> wrote in message >>>> news EB0C873-1194-4FEA-8D88-(E-Mail Removed)...>>>>> 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 Removed)DTHIS >>>>> web: www.nickhodge.co.uk >>>>> blog (non-tech): www.nickhodge.co.uk/blog/ >>>>> "Khalil Handal" <(E-Mail Removed)> wrote in message >>>>> news:(E-Mail Removed)... >>>>>> 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. >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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 Removed)DTHIS > web: www.nickhodge.co.uk > blog (non-tech): www.nickhodge.co.uk/blog/ > "Khalil Handal" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> 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" <(E-Mail Removed)> wrote in message >> news:AE62167F-FDCD-4616-84B6-(E-Mail Removed)... >>> Ooops...still awake in Dorset? Good job! ;-) >>> >>> Thanks Bob >>> >>> -- >>> HTH >>> Nick Hodge >>> Microsoft MVP - Excel >>> Southampton, England >>> (E-Mail Removed)DTHIS >>> web: www.nickhodge.co.uk >>> blog (non-tech): www.nickhodge.co.uk/blog/ >>> "Bob Phillips" <(E-Mail Removed)> wrote in message >>> news:%(E-Mail Removed)... >>>>I think you mean >>>> >>>> =IF(COUNT($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"") >>>> >>>> Mr H >>>> >>>> >>>> >>>> "Nick Hodge" <(E-Mail Removed)> wrote in message >>>> news EB0C873-1194-4FEA-8D88-(E-Mail Removed)...>>>>> 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 Removed)DTHIS >>>>> web: www.nickhodge.co.uk >>>>> blog (non-tech): www.nickhodge.co.uk/blog/ >>>>> "Khalil Handal" <(E-Mail Removed)> wrote in message >>>>> news:(E-Mail Removed)... >>>>>> 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. >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Khalil
Thanks for getting back, glad you got it working -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England (E-Mail Removed)DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Khalil Handal" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> 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 Removed)DTHIS >> web: www.nickhodge.co.uk >> blog (non-tech): www.nickhodge.co.uk/blog/ >> "Khalil Handal" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >>> 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" <(E-Mail Removed)> wrote in message >>> news:AE62167F-FDCD-4616-84B6-(E-Mail Removed)... >>>> Ooops...still awake in Dorset? Good job! ;-) >>>> >>>> Thanks Bob >>>> >>>> -- >>>> HTH >>>> Nick Hodge >>>> Microsoft MVP - Excel >>>> Southampton, England >>>> (E-Mail Removed)DTHIS >>>> web: www.nickhodge.co.uk >>>> blog (non-tech): www.nickhodge.co.uk/blog/ >>>> "Bob Phillips" <(E-Mail Removed)> wrote in message >>>> news:%(E-Mail Removed)... >>>>>I think you mean >>>>> >>>>> =IF(COUNT($E$21:$G$21)>1,AVERAGE($E$21:$G$21),"") >>>>> >>>>> Mr H >>>>> >>>>> >>>>> >>>>> "Nick Hodge" <(E-Mail Removed)> wrote in >>>>> message news EB0C873-1194-4FEA-8D88-(E-Mail Removed)...>>>>>> 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 Removed)DTHIS >>>>>> web: www.nickhodge.co.uk >>>>>> blog (non-tech): www.nickhodge.co.uk/blog/ >>>>>> "Khalil Handal" <(E-Mail Removed)> wrote in message >>>>>> news:(E-Mail Removed)... >>>>>>> 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. >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Average with Condition | Curtis | Microsoft Excel Worksheet Functions | 4 | 18th Mar 2010 06:48 PM |
| Can I sum or average a range with more than 1 condition? | BobT | Microsoft Excel Misc | 4 | 14th Feb 2005 07:28 PM |
| VBA AVERAGE/IF condition | mickey | Microsoft Excel Programming | 1 | 19th Jan 2004 11:42 AM |
| Urgent: Vba Average/if Condition | mickey | Microsoft Excel Worksheet Functions | 0 | 17th Jan 2004 04:13 PM |
| Average for condition in different column | Tony R | Microsoft Excel Worksheet Functions | 2 | 21st Nov 2003 12:01 PM |
