Display output as blanks with average and large value set

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
 
T

Trevor Williams

Hi ims

Try extending the column width to see if the number shows correctly

Trevor
 
T

Trevor Williams

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

Need Help with Refreshing Querytables. 8
Problem with SET & REF function 0
Problem with formula 5
Formula equation 6
Trouble with conflicting IsBlank() and CountBlank() results 4
average hourly output for date, result shown by day 5
To get an average of a column of numbers not zero/blank cells 4
macro to calculate average on varying sized rows with missing valu 4

Top