| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display output as blanks with average and large value set
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:D43)=29,"",AVERAGE(LARGE(D16:D4 3,{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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display output as blanks with average and large value set
Hi ims
Try extending the column width to see if the number shows correctly Trevor " 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:D43)=29,"",AVERAGE(LARGE(D16:D4 3,{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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display output as blanks with average and large value set
Hi Trevor,
The column is now staying: #NUM! Cheers ims On 26 Jan, 12:36, Trevor Williams wrote: Hi ims Try extending the column width to see if the number shows correctly Trevor " 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:D43)=29,"",AVERAGE(LARGE(D16:D4 3,{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 - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display output as blanks with average and large value set
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(L ARGE(E16:E43,{1;2;3;4}))),"",AVERAGE(LARGE(E16:E43 ,{1;2;3;4})))) Trevor " 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:D43)=29,"",AVERAGE(LARGE(D16:D4 3,{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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display output as blanks with average and large value set
Any other ideas that can help! cheers ims On 26 Jan, 12:40, wrote: Hi Trevor, The column is now staying: #NUM! Cheers ims On 26 Jan, 12:36, Trevor Williams wrote: Hi ims Try extending the column width to see if the number shows correctly Trevor " 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:D43)=29,"",AVERAGE(LARGE(D16:D4 3,{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 - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display output as blanks with average and large value set
Trevor,
many thanks it all working fine Cheers ims On 26 Jan, 12:52, wrote: Any other ideas that can help! cheers ims On 26 Jan, 12:40, wrote: Hi Trevor, The column is now staying: #NUM! Cheers ims On 26 Jan, 12:36, Trevor Williams wrote: Hi ims Try extending the column width to see if the number shows correctly Trevor " 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:D43)=29,"",AVERAGE(LARGE(D16:D4 3,{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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average with out zeros or blanks | Excel Discussion (Misc queries) | |||
| average in non-continuous set, excluding blanks | Excel Worksheet Functions | |||
| Moving Average with Blanks | Excel Discussion (Misc queries) | |||
| Formula for average of cells that have blanks | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
Linear Mode
