Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sudden "0" output on Sumproduct formulas
Hello there!
I have two worksheets in a notebook: One is the data source, the other is a lot of "sumproduct" formulas tabulating totals. Everything seemed to be working fine, then... One of the rows in the tabulating worksheet started outputting zeros instead of the totals it was giving me before. Any idea what is happening? (Below I state the problem in a different way:) My tabulating worksheet looks like this (Column 'A' is labels, B and C columns have formulas): A B C --|--------------------- 1| P1 F1a F2a 2| P2 F1b F2b 3| P3 F1c F2c 4| P4 F1d F2d This shows that the column B (sumproduct) formulas are exactly the same for every 'P' except for one condition (i.e. factor). Also, column C (sumproduct) formulas are exactly the same, except for one condition/factor. The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's F1 formula is the same factor that distinguishes P1's F2 formula from P2's F2 formula. I did a sort on a section of data (that corresponding to P2) on the source data worksheet, and upon calculating, all formulas in that row outputted "0". I have tried reentering them, creating a new column, checked the source data... I can't figure it out. It seems specifically to have to do with the factor/condition in those formulas, although it was working fine previously. Any leads are greatly appreciated! Thanks! --Rachel. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sudden "0" output on Sumproduct formulas
Rachel wrote:
Everything seemed to be working fine, then... One of the rows in the tabulating worksheet started outputting zeros instead of the totals it was giving me before. Any idea what is happening? [....] I did a sort on a section of data (that corresponding to P2) on the source data worksheet, and upon calculating, all formulas in that row outputted "0". That was my guess! Sort does not update references to cells within the sorted range. Sometimes you get lucky. But generally, you need to find all the references (before the sort!) and update them after the sort :-(. At least, that is what I have done. Perhaps someone can suggest a better alternative. At this point, it might be very tedious to do. If you are fortunate enough to have a recent-enough back-up, I would go back to that, then work forward. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sudden "0" output on Sumproduct formulas
What's the offending formula Rachel, and one that still works?
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Rachel" wrote in message ... Hello there! I have two worksheets in a notebook: One is the data source, the other is a lot of "sumproduct" formulas tabulating totals. Everything seemed to be working fine, then... One of the rows in the tabulating worksheet started outputting zeros instead of the totals it was giving me before. Any idea what is happening? (Below I state the problem in a different way:) My tabulating worksheet looks like this (Column 'A' is labels, B and C columns have formulas): A B C --|--------------------- 1| P1 F1a F2a 2| P2 F1b F2b 3| P3 F1c F2c 4| P4 F1d F2d This shows that the column B (sumproduct) formulas are exactly the same for every 'P' except for one condition (i.e. factor). Also, column C (sumproduct) formulas are exactly the same, except for one condition/factor. The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's F1 formula is the same factor that distinguishes P1's F2 formula from P2's F2 formula. I did a sort on a section of data (that corresponding to P2) on the source data worksheet, and upon calculating, all formulas in that row outputted "0". I have tried reentering them, creating a new column, checked the source data... I can't figure it out. It seems specifically to have to do with the factor/condition in those formulas, although it was working fine previously. Any leads are greatly appreciated! Thanks! --Rachel. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sudden "0" output on Sumproduct formulas
An example of the formula that is returning "0" is:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*(ELI!$U$3:$U $1699=0)) "012U" corresponds to "P2" in the simplified example I gave before... it's a person's 'name'... ELI! is my data source worksheet in the same notebook. The same formula works when instead of "012U" I have "010U" or "102P" or any other person's 'name' in that slot. The same formula also works when I sort sections of data relevant to "010U", "102P", etc... All similar sumproduct formulas with "012U" [such as: =SUMPRODUCT((ELI!$D$3:$D$1699="012U")*((ELI!$S$3:$ S$1699=12)+(ELI!$S$3:$S$1699=14))*(ELI!$V$3:$V$169 9=11)*(ELI!$U$3:$U$1699=0))] also don't work... Looking forward to your response. "Bob Phillips" wrote: What's the offending formula Rachel, and one that still works? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Rachel" wrote in message ... Hello there! I have two worksheets in a notebook: One is the data source, the other is a lot of "sumproduct" formulas tabulating totals. Everything seemed to be working fine, then... One of the rows in the tabulating worksheet started outputting zeros instead of the totals it was giving me before. Any idea what is happening? (Below I state the problem in a different way:) My tabulating worksheet looks like this (Column 'A' is labels, B and C columns have formulas): A B C --|--------------------- 1| P1 F1a F2a 2| P2 F1b F2b 3| P3 F1c F2c 4| P4 F1d F2d This shows that the column B (sumproduct) formulas are exactly the same for every 'P' except for one condition (i.e. factor). Also, column C (sumproduct) formulas are exactly the same, except for one condition/factor. The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's F1 formula is the same factor that distinguishes P1's F2 formula from P2's F2 formula. I did a sort on a section of data (that corresponding to P2) on the source data worksheet, and upon calculating, all formulas in that row outputted "0". I have tried reentering them, creating a new column, checked the source data... I can't figure it out. It seems specifically to have to do with the factor/condition in those formulas, although it was working fine previously. Any leads are greatly appreciated! Thanks! --Rachel. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sudden "0" output on Sumproduct formulas
Any chance that it's just a typo. Maybe it's a zero/oh thing in either the
formula or the data??? If you just used =countif() to test the stuff in column D, do you get something bigger than 0? If you apply data|filter|autofilter to that range. Filter to show 012U and then =subtotal(9,u3:u1699), what's returned? (I'd guess data problems--not formula.) And check for leading/trailing spaces and text that looks like numbers. Rachel wrote: An example of the formula that is returning "0" is: =SUMPRODUCT((ELI!$D$3:$D$1699="012U")*(ELI!$U$3:$U $1699=0)) "012U" corresponds to "P2" in the simplified example I gave before... it's a person's 'name'... ELI! is my data source worksheet in the same notebook. The same formula works when instead of "012U" I have "010U" or "102P" or any other person's 'name' in that slot. The same formula also works when I sort sections of data relevant to "010U", "102P", etc... All similar sumproduct formulas with "012U" [such as: =SUMPRODUCT((ELI!$D$3:$D$1699="012U")*((ELI!$S$3:$ S$1699=12)+(ELI!$S$3:$S$1699=14))*(ELI!$V$3:$V$169 9=11)*(ELI!$U$3:$U$1699=0))] also don't work... Looking forward to your response. "Bob Phillips" wrote: What's the offending formula Rachel, and one that still works? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Rachel" wrote in message ... Hello there! I have two worksheets in a notebook: One is the data source, the other is a lot of "sumproduct" formulas tabulating totals. Everything seemed to be working fine, then... One of the rows in the tabulating worksheet started outputting zeros instead of the totals it was giving me before. Any idea what is happening? (Below I state the problem in a different way:) My tabulating worksheet looks like this (Column 'A' is labels, B and C columns have formulas): A B C --|--------------------- 1| P1 F1a F2a 2| P2 F1b F2b 3| P3 F1c F2c 4| P4 F1d F2d This shows that the column B (sumproduct) formulas are exactly the same for every 'P' except for one condition (i.e. factor). Also, column C (sumproduct) formulas are exactly the same, except for one condition/factor. The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's F1 formula is the same factor that distinguishes P1's F2 formula from P2's F2 formula. I did a sort on a section of data (that corresponding to P2) on the source data worksheet, and upon calculating, all formulas in that row outputted "0". I have tried reentering them, creating a new column, checked the source data... I can't figure it out. It seems specifically to have to do with the factor/condition in those formulas, although it was working fine previously. Any leads are greatly appreciated! Thanks! --Rachel. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sudden "0" output on Sumproduct formulas
I considered and checked on the the zero/oh thing....the data is entered
correctly for sure. Also, I'm pretty sure the formula is entered correctly because it is exactly the same formula (copied and pasted) as I used in another cell the only difference being that in the original cell instead of "012U", I have "010U". Anyway, the formula was working (giving good output) until I did a sort on the source data. The strange thing is that this sort only affected formulas corresponding to this ONE person, containing the factor (ELI!$D$3:$D$1699="012U"). Finally, when I do a COUNTIF, the "correct" value is being returned, so it doesn't seem to be an error in the data source.... I'm not sure how to do a filter at the moment... maybe you could another method of counting entries with the same two criteria? I haven't been able to make nested if/countif's work for me, although others have suggested it. --Rachel. "Dave Peterson" wrote: Any chance that it's just a typo. Maybe it's a zero/oh thing in either the formula or the data??? If you just used =countif() to test the stuff in column D, do you get something bigger than 0? If you apply data|filter|autofilter to that range. Filter to show 012U and then =subtotal(9,u3:u1699), what's returned? (I'd guess data problems--not formula.) And check for leading/trailing spaces and text that looks like numbers. Rachel wrote: An example of the formula that is returning "0" is: =SUMPRODUCT((ELI!$D$3:$D$1699="012U")*(ELI!$U$3:$U $1699=0)) "012U" corresponds to "P2" in the simplified example I gave before... it's a person's 'name'... ELI! is my data source worksheet in the same notebook. The same formula works when instead of "012U" I have "010U" or "102P" or any other person's 'name' in that slot. The same formula also works when I sort sections of data relevant to "010U", "102P", etc... All similar sumproduct formulas with "012U" [such as: =SUMPRODUCT((ELI!$D$3:$D$1699="012U")*((ELI!$S$3:$ S$1699=12)+(ELI!$S$3:$S$1699=14))*(ELI!$V$3:$V$169 9=11)*(ELI!$U$3:$U$1699=0))] also don't work... Looking forward to your response. "Bob Phillips" wrote: What's the offending formula Rachel, and one that still works? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Rachel" wrote in message ... Hello there! I have two worksheets in a notebook: One is the data source, the other is a lot of "sumproduct" formulas tabulating totals. Everything seemed to be working fine, then... One of the rows in the tabulating worksheet started outputting zeros instead of the totals it was giving me before. Any idea what is happening? (Below I state the problem in a different way:) My tabulating worksheet looks like this (Column 'A' is labels, B and C columns have formulas): A B C --|--------------------- 1| P1 F1a F2a 2| P2 F1b F2b 3| P3 F1c F2c 4| P4 F1d F2d This shows that the column B (sumproduct) formulas are exactly the same for every 'P' except for one condition (i.e. factor). Also, column C (sumproduct) formulas are exactly the same, except for one condition/factor. The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's F1 formula is the same factor that distinguishes P1's F2 formula from P2's F2 formula. I did a sort on a section of data (that corresponding to P2) on the source data worksheet, and upon calculating, all formulas in that row outputted "0". I have tried reentering them, creating a new column, checked the source data... I can't figure it out. It seems specifically to have to do with the factor/condition in those formulas, although it was working fine previously. Any leads are greatly appreciated! Thanks! --Rachel. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sudden "0" output on Sumproduct formulas
You could insert another column in your data:
=if(d3="012U",u3,"") then drag down. Then use =count() and =sum() to determine how many numbers you returned for that column and what they added to. Rachel wrote: I considered and checked on the the zero/oh thing....the data is entered correctly for sure. Also, I'm pretty sure the formula is entered correctly because it is exactly the same formula (copied and pasted) as I used in another cell the only difference being that in the original cell instead of "012U", I have "010U". Anyway, the formula was working (giving good output) until I did a sort on the source data. The strange thing is that this sort only affected formulas corresponding to this ONE person, containing the factor (ELI!$D$3:$D$1699="012U"). Finally, when I do a COUNTIF, the "correct" value is being returned, so it doesn't seem to be an error in the data source.... I'm not sure how to do a filter at the moment... maybe you could another method of counting entries with the same two criteria? I haven't been able to make nested if/countif's work for me, although others have suggested it. --Rachel. "Dave Peterson" wrote: Any chance that it's just a typo. Maybe it's a zero/oh thing in either the formula or the data??? If you just used =countif() to test the stuff in column D, do you get something bigger than 0? If you apply data|filter|autofilter to that range. Filter to show 012U and then =subtotal(9,u3:u1699), what's returned? (I'd guess data problems--not formula.) And check for leading/trailing spaces and text that looks like numbers. Rachel wrote: An example of the formula that is returning "0" is: =SUMPRODUCT((ELI!$D$3:$D$1699="012U")*(ELI!$U$3:$U $1699=0)) "012U" corresponds to "P2" in the simplified example I gave before... it's a person's 'name'... ELI! is my data source worksheet in the same notebook. The same formula works when instead of "012U" I have "010U" or "102P" or any other person's 'name' in that slot. The same formula also works when I sort sections of data relevant to "010U", "102P", etc... All similar sumproduct formulas with "012U" [such as: =SUMPRODUCT((ELI!$D$3:$D$1699="012U")*((ELI!$S$3:$ S$1699=12)+(ELI!$S$3:$S$1699=14))*(ELI!$V$3:$V$169 9=11)*(ELI!$U$3:$U$1699=0))] also don't work... Looking forward to your response. "Bob Phillips" wrote: What's the offending formula Rachel, and one that still works? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Rachel" wrote in message ... Hello there! I have two worksheets in a notebook: One is the data source, the other is a lot of "sumproduct" formulas tabulating totals. Everything seemed to be working fine, then... One of the rows in the tabulating worksheet started outputting zeros instead of the totals it was giving me before. Any idea what is happening? (Below I state the problem in a different way:) My tabulating worksheet looks like this (Column 'A' is labels, B and C columns have formulas): A B C --|--------------------- 1| P1 F1a F2a 2| P2 F1b F2b 3| P3 F1c F2c 4| P4 F1d F2d This shows that the column B (sumproduct) formulas are exactly the same for every 'P' except for one condition (i.e. factor). Also, column C (sumproduct) formulas are exactly the same, except for one condition/factor. The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's F1 formula is the same factor that distinguishes P1's F2 formula from P2's F2 formula. I did a sort on a section of data (that corresponding to P2) on the source data worksheet, and upon calculating, all formulas in that row outputted "0". I have tried reentering them, creating a new column, checked the source data... I can't figure it out. It seems specifically to have to do with the factor/condition in those formulas, although it was working fine previously. Any leads are greatly appreciated! Thanks! --Rachel. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with named formula's | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |