Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: #DIV/0! Problem With Formula

    Formula is =(E38-B38)/B38 where E38 value is 0.

    I did look this up on MS support and Googled it also but nothing on this particular formula applied.

    And what if B38 valus is 0 as sometimes happens.

    See the page where I use this formula a lot at RSMRlty.com/Market Activity and select either of the two Median Sale Price menu items.

    Any ideas?

    Thanks much.

    Rick

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    What do you want to be displayed if B38 is equal to zero?

    Usually we use an IF() worksheet function constuction like this:

    =IF(B38,(E38-B38)/B38,0)

    Change the 0 to whatever you want to be displayed instead of the error you are currently getting.

    Hope that helps...

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Colin - that worked great!.

    Now - what if the #DIV/0! is E38 in stead of B-38?

    Somehow it needs to be =IF(B38 OR E38,(E38-B38)/B38 or E38,0)

    If you look at my MEDIAN page on my internet site you will see that the data is sometimes in the second set of values.

    Gosh I hope I'm explaining this correctly.

    Thanks much Colin.

    Rick

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Rick,

    I think ideally you would want to amend the formula in E38 so it doesn't return a #DIV/0! error either. To do this you would use a similar IF() formula construction to the one already mentioned.



    But, if you want an error to displayed in E38 then you could cater for it by using a formula like:

    =IF(AND(B38<>0,ISNUMBER(E38)),(E38-B38)/B38,0)


    Hope that helps...

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    DIV error

    Colin - that did the trick. Excellent!

    Thanks much.

    Rick

Posting Permissions

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