| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
Hi All,
I haven't a bit of problem displaying a blank cell in D15 as empty when the others cells are empty. The value that display is ######. Anyone there that can help me! Here is the formula: - =IF(COUNTBLANK(D16  43)=29,"",AVERAGE(LARGE(D1643,{1;2;3;4})))The formula works fine and display the average of the largest set of 4. The only problem is displaying D15 as empty. Many thanks ims |
|
|
|
|||
|
|
| |
|
Guest
Posts: n/a
|
Hi ims
Try extending the column width to see if the number shows correctly Trevor "(E-Mail Removed)" wrote: > Hi All, > > I haven't a bit of problem displaying a blank cell in D15 as empty when > the others cells are empty. The value that display is ######. > > Anyone there that can help me! > > Here is the formula: - > > =IF(COUNTBLANK(D16 43)=29,"",AVERAGE(LARGE(D1643,{1;2;3;4})))> > The formula works fine and display the average of the largest set of 4. > The only problem is displaying D15 as empty. > > Many thanks > > ims > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Hi Trevor,
The column is now staying: #NUM! Cheers ims On 26 Jan, 12:36, Trevor Williams <TrevorWilli...@discussions.microsoft.com> wrote: > Hi ims > > Try extending the column width to see if the number shows correctly > > Trevor > > > > "ims12...@gmail.com" wrote: > > Hi All, > > > I haven't a bit of problem displaying a blank cell in D15 as empty when > > the others cells are empty. The value that display is ######. > > > Anyone there that can help me! > > > Here is the formula: - > > > =IF(COUNTBLANK(D16 43)=29,"",AVERAGE(LARGE(D1643,{1;2;3;4})))> > > The formula works fine and display the average of the largest set of 4. > > The only problem is displaying D15 as empty. > > > Many thanks > > > ims- Hide quoted text -- Show quoted text - |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Hi ims,
Just realised you were having a problem when there is less than 4 values in the range. Replace your formula with this one. =IF(COUNTBLANK(E16:E43)=29,"",IF(ISERROR(AVERAGE(LARGE(E16:E43,{1;2;3;4}))),"",AVERAGE(LARGE(E16:E43,{1;2;3;4})))) Trevor "(E-Mail Removed)" wrote: > Hi All, > > I haven't a bit of problem displaying a blank cell in D15 as empty when > the others cells are empty. The value that display is ######. > > Anyone there that can help me! > > Here is the formula: - > > =IF(COUNTBLANK(D16 43)=29,"",AVERAGE(LARGE(D1643,{1;2;3;4})))> > The formula works fine and display the average of the largest set of 4. > The only problem is displaying D15 as empty. > > Many thanks > > ims > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Any other ideas that can help! cheers ims On 26 Jan, 12:40, ims12...@gmail.com wrote: > Hi Trevor, > > The column is now staying: #NUM! > > Cheers > > ims > > On 26 Jan, 12:36, Trevor Williams > > > > <TrevorWilli...@discussions.microsoft.com> wrote: > > Hi ims > > > Try extending the column width to see if the number shows correctly > > > Trevor > > > "ims12...@gmail.com" wrote: > > > Hi All, > > > > I haven't a bit of problem displaying a blank cell in D15 as empty when > > > the others cells are empty. The value that display is ######. > > > > Anyone there that can help me! > > > > Here is the formula: - > > > > =IF(COUNTBLANK(D16 43)=29,"",AVERAGE(LARGE(D1643,{1;2;3;4})))> > > > The formula works fine and display the average of the largest set of 4. > > > The only problem is displaying D15 as empty. > > > > Many thanks > > > > ims- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Trevor,
many thanks it all working fine Cheers ims On 26 Jan, 12:52, ims12...@gmail.com wrote: > Any other ideas that can help! > > cheers > > ims > > On 26 Jan, 12:40, ims12...@gmail.com wrote: > > > > > Hi Trevor, > > > The column is now staying: #NUM! > > > Cheers > > > ims > > > On 26 Jan, 12:36, Trevor Williams > > > <TrevorWilli...@discussions.microsoft.com> wrote: > > > Hi ims > > > > Try extending the column width to see if the number shows correctly > > > > Trevor > > > > "ims12...@gmail.com" wrote: > > > > Hi All, > > > > > I haven't a bit of problem displaying a blank cell in D15 as empty when > > > > the others cells are empty. The value that display is ######. > > > > > Anyone there that can help me! > > > > > Here is the formula: - > > > > > =IF(COUNTBLANK(D16 43)=29,"",AVERAGE(LARGE(D1643,{1;2;3;4})))> > > > > The formula works fine and display the average of the largest set of 4. > > > > The only problem is displaying D15 as empty. > > > > > Many thanks > > > > > ims- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| average in non-continuous set, excluding blanks | JJ | Microsoft Excel Worksheet Functions | 6 | 8th Oct 2008 05:30 PM |
| Moving Average with Blanks | E.Q. | Microsoft Excel Misc | 3 | 17th Jun 2008 09:46 PM |
| Formula for average of cells that have blanks | Security Dave | Microsoft Excel Worksheet Functions | 1 | 14th May 2008 03:59 PM |
| Average not including Zeros/Blanks | =?Utf-8?B?RGFT?= | Microsoft Excel Worksheet Functions | 8 | 17th Oct 2007 06:29 PM |
| Weighed Average of a weiged average when there are blanks | krl - ExcelForums.com | Microsoft Excel Misc | 1 | 6th Jul 2005 07:37 PM |
