Display output as blanks with average and large value set

  • Thread starter Thread starter ims121uk
  • Start date Start date
I

ims121uk

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:D43,{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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with an formula 9
Need Help with Refreshing Querytables. 8
Problem with formula 5
Formula equation 6
insert cell value into string 2
Excel Sumproduct 0
how do I average cells if a cell is at #div/0! 5
Cell values to an array, then array transpose to another workbook 8

Back
Top