+ Reply to Thread
Results 1 to 8 of 8

DIV/0 error when referencing data from web in separate cell

  1. #1
    Registered User
    Join Date
    09-29-2020
    Location
    Salem, VA
    MS-Off Ver
    16.0.13127.20296
    Posts
    8

    Question DIV/0 error when referencing data from web in separate cell

    This may be a simple problem, but I can't find anything on the internet pertaining to my situation. On my excel sheet, I am using a website that takes recent auction prices of a product. My goal is to take the average price of them to spit out an estimated value of the product. On the cell I have selected in the photo, I am trying to use the AVERAGE function between a random set of numbers from the chart. It seems to completely disregard them as numbers and think they are all equal to zero, hints why it shows the error #DIV/0. This is driving me crazy because this is the last obstacle I have after working on this document for over a month.
    Attached Images Attached Images

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,815

    Re: DIV/0 error when referencing data from web in separate cell

    Those values have dollar signs in them so I suspect the cells are Text values, rather than being formatted numbers. I'd do a global replace of $ for nothing and then format all the cells as currency.

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: DIV/0 error when referencing data from web in separate cell

    are they numbers? use =isnumber(H10) and if it returns TRUE then it is but if it returns FALSE then it isn't and you'll have to convert it to a number.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Registered User
    Join Date
    09-29-2020
    Location
    Salem, VA
    MS-Off Ver
    16.0.13127.20296
    Posts
    8

    Re: DIV/0 error when referencing data from web in separate cell

    Okay so I ran =isnumber(H10) in a separate cell like Sam Capricci advised and it returned back FALSE. How do I format them as numbers while also making it so when the page refreshes it doesn't just go back to the way it was? Thank you! If you want I can upload the document if this isn't providing enough information.

  5. #5
    Registered User
    Join Date
    09-29-2020
    Location
    Salem, VA
    MS-Off Ver
    16.0.13127.20296
    Posts
    8

    Re: DIV/0 error when referencing data from web in separate cell

    Came back FALSE. So now I am trying to figure out how to change the formatting while also having it stay that way once I refresh the page. Thank you guys!

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: DIV/0 error when referencing data from web in separate cell

    you could try this... =AVERAGE(--H10:H18)
    that will convert what is in the list to numbers.

  7. #7
    Registered User
    Join Date
    09-29-2020
    Location
    Salem, VA
    MS-Off Ver
    16.0.13127.20296
    Posts
    8

    Re: DIV/0 error when referencing data from web in separate cell

    You're a lifesaver bro, thank you.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,815

    Re: DIV/0 error when referencing data from web in separate cell

    You're welcome. Thanks for the rep.


    Note: please include a sample workbook as well as the picture. Takes all the guesswork out of it.





    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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] Cell formula referencing file path contained in separate cell not working
    By BrokenDome in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2019, 08:17 AM
  2. Replies: 5
    Last Post: 01-25-2016, 07:59 AM
  3. Error referencing cell in another sheet
    By Pete291 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-26-2015, 01:19 PM
  4. [SOLVED] separate data from one cell into two separate cells question
    By lilsnoop in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2012, 05:53 PM
  5. Worksheets & Cell Referencing #REF! Error
    By ebbo in forum Excel General
    Replies: 10
    Last Post: 03-18-2009, 12:36 PM
  6. Cell Referencing Code Error?
    By MPS0525 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-17-2006, 11:45 AM
  7. Cell Referencing Error.
    By Mike in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2006, 09:45 AM

Tags for this Thread

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