# Ignoring blank cells in excel

Discussion in 'Business Applications' started by j_rees101, May 20, 2014.

Not open for further replies.

Joined:
May 20, 2014
Messages:
3
Hi guys,

Can anybody give some advice as to why this formula is not working:

=10*LOG(AVERAGE(10^((IF(NOT(ISBLANK(C2:C297))))/10)))

I'm trying to analyse a bunch of data but I want to ignore the blank cells in the data series, without hiding/deleting them

J

Joined:
Mar 27, 2008
Messages:
6,571
First Name:
Hans
Maybe adding an extra and countif for blanks, you just want to divide the number of rows that contain values.
If you use one cell in row 1 that holds the number of rows containg values and use this ?

3. ### CodeLexicon

Joined:
Oct 15, 2013
Messages:
503
If you break the formula down ...

10*LOG(A)

A = AVERAGE(B)
B = 10^(C)
C = (D)/10
D = IF(NOT(ISBLANK(C2:C297)))

I think you just need to sort the D part (Excel ignores the blanks when averaging) and make it an array formula

Here's the formula I think it should be =10*LOG(AVERAGE(10^((C2:C297)/10)))

Paste it into your formula bar DON'T Hit Enter but hit Ctrl+Shft+Enter instead

it should look like this (note the curly brackets)

{=10*LOG(AVERAGE(10^((C2:C297)/10)))}

Joined:
May 20, 2014
Messages:
3
Thanks for replying, I've tried this but it doesn't seem to work using an array formula with a logarithmic equation. It still counts all of the blank cells as zero

Joined:
May 20, 2014
Messages:
3

Sorry, not sure that I follow this. Do you mean that I could rearrange the equation so that it averages over the number of unblank cells rather than all of the cells in the range?

As Seen On