+ Reply to Thread
Results 1 to 18 of 18

sumproduct division returning div/0

  1. #1
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    sumproduct division returning div/0

    I am trying to divide values from column B by values in column D (some of which are blank cells), then values in column B by values in column E, and so on. I am also trying to then divide the total for each column by the number of non-empty cells in the column.

    So I am trying to do this B1/D1+B2/D2+B3/D3.../count(d1:d48); B1/E1+B2/E2+B3/E3.../count(e1:e48)..etc..

    Despite numerous attempts I continue to get a #div/0 result. Note that column B will always have a value -- it is columns D, E, F...R which may include empty cells.

    =SUMPRODUCT(($B$1:$B$48)/(D1:D48))/COUNT(D1:D48)

    --example---
    row | column B | column D | column E | column F....R
    1 .25 1 3 1
    2 .28 1 2
    3 .38 3 5
    4 .12 2 4
    5 .17 4
    6 .33 1 2 4

    Can someone please help me with this, I have spent the last several hours trying to figure this out. I would sincerely appreciate any help...

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: sumproduct division returning div/0

    You can first check is your count equal to 0

    =IF(COUNT(D1:D48)>0, SUMPRODUCT(($B$1:$B$48)/(D1:D48))/COUNT(D1:D48), "")

  3. #3
    Registered User
    Join Date
    02-25-2010
    Location
    Abu dhabi
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2013
    Posts
    24

    Re: sumproduct division returning div/0

    Hi,

    Instead of Count use Counta Function.

    it Counts the number of cells in a range that are not empty.

    I hope this is the answer to your question.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sumproduct division returning div/0

    Where D, E etc are blank are you excluding B from the calculation or do you wish to assume 1 as divisor ?

    edit: I think you're excluding...

    Please Login or Register  to view this content.
    If you want to perform the calculation for all columns in one calculation / formula let us know.
    Last edited by DonkeyOte; 02-19-2011 at 04:47 AM. Reason: forgot the COUNTIF divisor

  5. #5
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: sumproduct division returning div/0

    still getting div/0... in error checking: "the formula or function used is dividing by zero or empty cells".

    when i evaluate the formula: it generically states: if(TRUE, #div/0/counta(d1:d48), "")

    does the ">0" in the if-statement address blank cells or just "0"? i have some blank cells in columns C,D,E,etc...

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sumproduct division returning div/0

    @flizzn, you can't use the SUMPRODUCTs provided thus far given the 0/blanks in Column D.

    I would suggest you use an Array for a variety of reasons (hence prior suggestion).
    Performance wise there is no real difference - and in fact the Array is arguably more efficient in this instance.

  7. #7
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: sumproduct division returning div/0

    so at least this fix has addressed the #div/0 issue! the problem is that it is now giving me an incorrect result. when i evaluate the formula, it looks like the "max" argument at the beginning of the formula is driving this.

    basically, it looks like "max" is pulling the highest number ("4" in the case of column D) and then disregarding everything less than "4". so while it addresses the #div/0 issue, it is also excluding #1-3 as well (which are essential to the calculation).

    from the formula evaluation: if(4, AVERAGE....)

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sumproduct division returning div/0

    Post a sample file with some data and the formula you have tried to use.

    Presumably you entered as an Array as outlined ? (ie with CTRL + SHIFT + ENTER - enter alone will not suffice unlike "normal" formula)

    If the array was inserted correctly the formula will appear encased within { }
    note: these brackets can not be entered manually

  9. #9
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: sumproduct division returning div/0

    @donkeyote,

    sorry, i didnt realize the msg board didnt link comments to the poster i was responding to.

    my previous message was in response to your suggestion to use an array. it got rid of the #div/0 issue (glad to finally see a result in the cell instead of #div/0), but the "max" seems to have resulted in the formula not counting any number lower than the highest within the column...which ends up giving me the incorrect result.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sumproduct division returning div/0

    The MAX has no effect on the Array other than to determine whether or not it needs be processed in the first instance.

    In native XL numbers can be used as Booleans; only 0 is FALSE - any other number is TRUE

    Please Login or Register  to view this content.
    will return "A" as 2 is TRUE

    Please Login or Register  to view this content.
    will return "B" as 0 is FALSE

    So in the context of your question:

    Please Login or Register  to view this content.
    the MAX is simply being used to determine if the Array should be calculated and has no bearing on the Array itself.

    Given the above I ask you post a sample file with the formula suggested in place.

  11. #11
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: sumproduct division returning div/0

    i have attached a redacted copy of the xls. you will see D8 is populated with the formula you gave me, but it results in a .01, which is incorrect.

    the goal is to get B to be divided by D, one-by-one (not including empty cells), the results aggregated, then divided by the total number of cells in column D which are not empty cells, the result then being populated into D8.
    Attached Files Attached Files

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sumproduct division returning div/0

    OK - error on my part - the COUNTIF divisor is not required/desired

    Please Login or Register  to view this content.
    which would generate 0.1394...

  13. #13
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: sumproduct division returning div/0

    that is the right answer... but i typed this in and now i am getting a "FALSE" answer...?? I am using ctl+shift+enter....?

  14. #14
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: sumproduct division returning div/0

    ok, and now i put your solution into the xls that i sent you (redacted version) and it works... so your answer is obviously good to go. but i didnt change anything on the version i sent you except for delete data which is not necessary for this calculation. do you have any idea why this would happen and return a "FALSE" response on my original xls?

  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sumproduct division returning div/0

    You would get FALSE only where the Array is not being calculated (you can add a Null false argument if preferred)

    Rather than to & fro further see attached.

    You mention "original" sample - obviously we've only seen one - but if you get a Null where you expect an answer check that the source values in D are numbers and not numbers stored as text (MAX of which would be 0)
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: sumproduct division returning div/0

    @donkeyote

    i ensured that all the data was set as numbers. i also added in your null check... but it is still doing it: "FALSE".

    i am just sending you the entire xls. can you please look at this and tell me why it is coming up as false?

    thanks
    Attached Files Attached Files

  17. #17
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sumproduct division returning div/0

    Select D8 and hit F2 to go into enter mode... notice anything

    For whatever reason you have the Array repeated five times in the cell and the syntax is such that you will get a FALSE output.

    Obviously you should have the array present once only so remove all but the last and re-set the Array.

  18. #18
    Registered User
    Join Date
    02-19-2011
    Location
    houston, tx
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: sumproduct division returning div/0

    oh my gosh...i cant believe it -- it works!!

    DonkeyOte, thank you so much!!! you can only imagine how long i have been struggling with this thing.

    i really really appreciate all your help!!!

+ 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