+ Reply to Thread
Results 1 to 14 of 14

Sumproduct DIV/0 Error

  1. #1
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Sumproduct DIV/0 Error

    I am trying to avoid the divide by 0 error when doing a sum product formula. All criteria involved has blanks. I know that sumproduct turns blanks into 0's. I just want to to avoid that issue. I used this formula among others: =SUMPRODUCT(ISNUMBER(B2:B6/A2:A6)*1)/COUNTA(B2:B6)-1. The base formula is=SUMPRODUCT(B2:B6/A2:A6)/COUNTA(B2:B6)-1. See attached file.
    Attached Files Attached Files
    Last edited by Excelperson1992; 07-30-2019 at 03:17 PM. Reason: Need to change attachment

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,336

    Re: Sumproduct DIV/0 Error

    I don't see the issue but to avoid the div/0 issue add iferror to the front and ,0 or ,"" to the end...
    like this... =IFERROR(SUMPRODUCT(ISNUMBER(B2:B6/A2:A6)*1)/COUNTA(B2:B6)-1,"")

    EDIT: oh, and you can change the formulas in col C to this too... =IFERROR(B2/A2-1,"")
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Re: Sumproduct DIV/0 Error

    Thank you for the quick response. I attached a different version of the initial document. I am sorry for the inconvenience. The formula I used in my initial request gives a wrong answer. I need to get the answer 1.61 or 161%. Under manual formula in the document is the formula I used, which now gives a DIV/0 answer. Please see update and let me know if there are still problems.

    Thanks again for your help.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,336

    Re: Sumproduct DIV/0 Error

    I can't get to the number you want no matter how many permutations I try. So just so I understand, you want the average percent difference of the numbers in column B from the numbers in column A. But I get 142%, not 161% though I see how you got that by averaging the percent differences row by row.

  5. #5
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Re: Sumproduct DIV/0 Error

    Yes thats what I want. It's tough because normally I can find an answer online but today I haven't. What did you do to get 142?

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

    Re: Sumproduct DIV/0 Error

    I'm hesitant to tell you how I got the 142 yet because I'm not sure that is correct. I'm testing things with my own numbers to see if I can build a formula that gets what I know the answer with my own numbers is. For example, I put a series of 10s in one column and a series of 20s in the other so each is 100% increase and the average overall would be 100%. Now I'm trying to reverse engineer the formula to return that result with sums and counts or sumproduct and maybe count but so... in the mean time maybe someone will drop by and get it quicker than I will.

  7. #7
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Re: Sumproduct DIV/0 Error

    No problem. You've been very helpful so far!

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,336

    Re: Sumproduct DIV/0 Error

    this gets me the right answer on my experimental data even with blanks... =(SUM(B2:B6)/SUM(A2:A6))-1
    but it returns the 42% for your data set.

  9. #9
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Re: Sumproduct DIV/0 Error

    Thanks again for you help. This is a tricky one. It may not be possible.

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,336

    Re: Sumproduct DIV/0 Error

    keep in mind you are trying to average percent differences for a row by row basis, it may be that that is not the way to get an average of the overall percent difference between two columns.
    each time I apply that formula to my test data I get the correct percent average.
    are you absolutely sure the 161% is correct?
    if I take the sum of col A it is 19, the sum of col B is 27, taking (27-19)-19 gives me 42.1%
    I'll be interested in seeing what else is offered.

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,629

    Re: Sumproduct DIV/0 Error

    I'm sure it is possible. Is it necessary to do this all in one single cell with the sumproduct formula? I changed the C formula to something like =IFERROR(B2/A2-1,"text") and copied down. The correct result was shown in C7, because the AVERAGE() function would ignore the text string from the IFERROR() function which trapped the 0/0 error when the cells were blank.

    Will that approach work, or is it necessary to use a single cell SUMPRODUCT() formula?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  12. #12
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Re: Sumproduct DIV/0 Error

    Its correct. The reason I want the 161% method is because I am trying to determine the average % saved on individual deals. The sales themselves could have large dollar differences. For instance one deal may save $1000 at 1% change ($90000 and 80000) and the other might save $1000 at 11% change ($9000 and $8000). The 42% gives me the average % saved on all deals when combined. in the example total dollars saved is 2% using the 42% method but 6% for each individual deal using the 161% method. I want to focus on individual averages not totals. Hopefully that makes sense.

  13. #13
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Re: Sumproduct DIV/0 Error

    Its an option definitely. The way you're talking about was the original way I did it, but I feared it would take up too much space because I'm going to have 100s of rows utilizing that method across 50+ columns. With the single formula I would just be able to condense the document to utilize less formulas and less columns. But it would definitely work.

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,629

    Re: Sumproduct DIV/0 Error

    I feared it would take up too much space because I'm going to have 100s of rows utilizing that method across 50+ columns.
    I guess you have to decide for yourself how much space is too much. You describe using only 100 out of 1million available rows and only 50 or so out of 16000 available columns, so it seems to me that you have a lot of spreadsheet real estate to expand into. Certainly, at some point a spreadsheet gets large enough that save/load/calculate operations start to take a noticeable amount of time, but 100x50 doesn't seem like it should even be close to that point.

+ 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. Sumproduct Error
    By pauldaddyadams in forum Excel General
    Replies: 7
    Last Post: 06-23-2015, 01:20 PM
  2. [SOLVED] Sumproduct #N/A error
    By Blake 7 in forum Excel General
    Replies: 12
    Last Post: 11-12-2014, 11:12 PM
  3. IF with SUMPRODUCT error
    By ddub25 in forum Excel General
    Replies: 4
    Last Post: 02-16-2012, 08:47 AM
  4. Sumproduct and value error
    By bountifulgrace in forum Excel General
    Replies: 2
    Last Post: 10-26-2006, 07:33 AM
  5. Sumproduct Error
    By michael.card@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-18-2006, 04:00 PM
  6. [SOLVED] Error values:DIV/0! error in SumProduct formula with no division
    By Jerry W. Lewis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 PM
  7. SUMPRODUCT ERROR
    By Mestrella31 in forum Excel General
    Replies: 1
    Last Post: 01-26-2005, 04:06 PM

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