+ Reply to Thread
Results 1 to 15 of 15

#DIV/0! Errors?

  1. #1
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Question #DIV/0! Errors?

    I have created a basic spreadsheets that show sales in column b and profit is column c and totals in column d I want it to divide profit by sales to give a gp% which appears in column d but when I have either nothing or a 0 in any of the bands b or c columns I get an error #DIV/0! in column d? I also have it averaging at the bottom of column d with the same results how can I stop this error appearing please because many weeks we have no figures yet so they are blank?

    Any advice would be appreciated.
    Last edited by Jamer; 01-06-2014 at 08:25 AM.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: #DIV/0! Errors?

    you could use an if construct to check either if there is 0 or "" or if there is an error, for example:
    =IF(ISERROR(C1/B1),"",C1/B1)
    or
    =IF(OR(B1="",B1=0),"",C1/B1)

  3. #3
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Re: #DIV/0! Errors?

    Thank you for your reply, please forgive me I am quite new to this. So do I place either one of these formulas in column d because I have just entered the first one suggested and it now since the change it just shows 0.00 in the column regardless of what is typed in columns b or d - Or do I still need to have the original formula inline too? What should I type into column d?

  4. #4
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Re: #DIV/0! Errors?

    Ah, I think I have got it now, I had not entered the correct column numbers What is the difference though between the two suggestion given?
    Last edited by Jamer; 01-06-2014 at 08:59 AM.

  5. #5
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Re: #DIV/0! Errors?

    Only issue I have now is the column D should average out the totals so what formula would I need to put in there please? it is currently -
    =AVERAGE(D7:D12)

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: #DIV/0! Errors?

    The two formulas are just different ways of acheiving what you want, one evaluates the equation to see if returns an error, one checks that the input to the equation is valid.

    I don't know what your table looks like so can't really comment on the average. I would assume that =average(D7:D12) would be correct, if D7:D12 is the cells you want to average.

  7. #7
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Re: #DIV/0! Errors?

    Yes and all in between but that total is giving the same error when all other cells are empty I just need to ignore 0's again in the total and I will be done

  8. #8
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: #DIV/0! Errors?

    Sorry, I don't understand. Please can you upload a sample of your workbook so I can see what is happening. If you press "go advanced" and then "attachments" you can attach a workbook to your reply.

  9. #9
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Re: #DIV/0! Errors?

    Quote Originally Posted by yudlugar View Post
    Sorry, I don't understand. Please can you upload a sample of your workbook so I can see what is happening. If you press "go advanced" and then "attachments" you can attach a workbook to your reply.

    I hope you can see this? The sales and profits add up fine now and with the formula you kindly provided it all works but the total average where the arrows are show errors.
    Attached Images Attached Images

  10. #10
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: #DIV/0! Errors?

    Hi - please upload the actual workbook, not a screenshot

  11. #11
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Re: #DIV/0! Errors?

    Can I email it to you rather than upload it onto the public forum?

  12. #12
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: #DIV/0! Errors?

    Just upload the part that we are talking about, get rid of any information which doesn't affect it and replace any sensitive data with dummy data

  13. #13
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Re: #DIV/0! Errors?

    Okay here you go
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: #DIV/0! Errors?

    okay, try:
    =IF(ISERROR(AVERAGE(D7:D12)),"",AVERAGE(D7:D12))

  15. #15
    Registered User
    Join Date
    01-04-2014
    Location
    Hereford England
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    14

    Re: #DIV/0! Errors?

    Quote Originally Posted by yudlugar View Post
    okay, try:
    =IF(ISERROR(AVERAGE(D7:D12)),"",AVERAGE(D7:D12))
    You are a star! Thanks you soooo much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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