1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Ignoring blank cells in excel

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

Thread Status:
Not open for further replies.
Advertisement
  1. j_rees101

    j_rees101 Thread Starter

    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

    Thanks for any help you can give me,

    J
     
  2. Keebellah

    Keebellah Trusted Advisor

    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

    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)))}
     
  4. j_rees101

    j_rees101 Thread Starter

    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
     
  5. j_rees101

    j_rees101 Thread Starter

    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?
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1126313

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice