+ Reply to Thread
Results 1 to 3 of 3

How to delete data within a table and ignore Natural log formula errors

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    How to delete data within a table and ignore Natural log formula errors

    I have a list of data that needs to be used in a lognormdist function. I have arranged a table with a formula in cell B20: Average(ln(B2),ln(B3),ln(B4),ln(B5)....etc) and one in B21: stdev(ln(B2),ln(B3),ln(B4),ln(B5)....etc). I have it arranged like this so a table on another sheet will update as the data in the table is changed. The problem I am having is I want to delete the lowest and highest amounts from each row of data, but deleting the data makes the LN function give an error since the natural log of 0 is undefined. Is there a way to ignore this error and only include the cells with data in them into the formula without havig to exclude the cells I want to delete? I have attached an example document. In the document say I want to delete the entire first row or delete individual data from different cells within the table, how do you make the formula give an answer by only using the undeleted data and ignoring the LN error? You will notice there is a normdist formula in the file as well, there is no error with this function just the lognormdist one. Sorry if there's any confusion let me know if I need to make it more clear.
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to delete data within a table and ignore Natural log formula errors

    I honestly have no idea what log and standard deviation is, but here's a solution that works.

    I used a helper column after each Category. I applied LN to each number, and then used formulas against them.

    Assuming Column C = LN for each value in B, then Log Mean:
    = AVERAGEIF(C2:C16,">0")



    Then the Standard Deviation is:
    =STDEV.P(IF(NOT(ISERROR(C2:C16)),C2:C16))

    Note, this is 2nd formula is entered as an array formula using Ctrl+Shift+Enter to confirm, instead of exiting the cell with just Enter.

    a log with stds.xlsx
    Last edited by daffodil11; 09-10-2013 at 04:05 PM. Reason: attachment issues

  3. #3
    Registered User
    Join Date
    08-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to delete data within a table and ignore Natural log formula errors

    Thank you for your response. I was hoping for something a little different but your solution was able to help me arrange the table in a different way to make everything work. Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] AVERAGE formula & ignore errors
    By SChapman in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-29-2013, 02:26 PM
  2. Nested index formula - need to ignore errors
    By Fursmanm in forum Excel General
    Replies: 0
    Last Post: 09-20-2012, 09:50 AM
  3. Charting: How to Ignore Data points With Errors
    By mickeypikey in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-11-2011, 09:10 AM
  4. How to Ignore "Web query returned no data" errors during macro execution
    By ScottSutherland in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2008, 03:36 PM
  5. How do i make a sum formula ignore #div/0! errors in the range
    By shat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-22-2006, 09:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1