Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel formula (2002)

    Hello,

    I am putting a formula into an entire column, however for the cekks where the formula does not work I receive A #div/0! does any one know how to get rid of this?

    Also in the same table, in one column, I have a title followed by three data points, then another title and another three data points and so on. If I want to use a filter how do I make it so all the data that relates to the title shows up when I use filter?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: excel formula (2002)

    Let's say that you have a formula that looks like
    <code>
    =A1/B1
    </code>
    This will return #DIV/0 if B1 is 0. To avoid this, change the formula to
    <code>
    =IF(B1=0,"",A1/B1)
    </code>
    Instead of placing the titles between the data, place them in a separate column. You can then easily filter for a specific title.
    Attached Images Attached Images
    • File Type: png x.png (1.5 KB, 1 views)

  3. #3
    New Lounger
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel formula (2002)

    Hello Hans, Thanks for the help again, but I think maybe I did not explain myself correctly.

    so I have three columns, A is Debt, B is Sales and C is A/B. my problem is when I drag down the columns it passes over text or blank spaces so there is no 0 for the if statement, i tried puting in =IF(C5="","",A5/B5) for example but that did not work either...


    Also for the filte situation I have to have it organized in this way. For every header there are 3 or 4 rows of information underneath and it has to be set up that way..is there a way to group them or something or it to work??

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: excel formula (2002)

    1) Try this version, it'll catch all situations in which A5/B5 is not valid:
    <code>
    =IF(ISERROR(A5/B5),"",A5/B5)
    </code>
    2) That isn't the way filters work. They expect a database-like table with column headers (field names) at the top row and only data in the rows below.
    With your data setup, you can't use filters, but you can use Edit | Find to search for a specific header.

Posting Permissions

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