If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Sumproduct issues
Hi Harlan,
I want to thank you for being detailed and taking the time to explain SUMPRODUCT, data types, coercion etc. I learned a lot reading your posts in this thread. It has been educational. I'll take the following advice to heart. --x is guaranteed to convert text representations of numbers to their numeric values (with possible floating point rounding/truncation error) while leaving numeric values unchanged, and x&"" is guaranteed to convert numeric values to their text representations while leaving text as-is. In my opinion, the fact that you are meticulous/precise on terminology is a reflection of your expertise. I always believe that programmers do well in Excel because writing a formula is like writing a line of code in a program. A sense of perfection is an important quality of a good programmer. I also want to thank you for explaining #N/A (wildcard) to me the other day. Last but not least, I don't think you have insulted anybody. I am going to go over your posts again and if I have any questions, I'll start my own thread. If I do, I hope you have time to drop by. We are lucky to have you around sharing your expertise and *generosity*. Please don't give up on us. I hope you still come to this thread and can read this. Epinn "Harlan Grove" wrote in message oups.com... SteveDB1 wrote... Ok, Harlan, I tried the last two equations you mentioned, and the first one does not work. It only returns a zero. (--Map!$E$4:$E$30=$A3) or (Map!$E$4:$E$30=$A3&"") Simply put, I don't believe you. If you can get the result you claim to want using formulas like =1*Map!E4 in a different range of 27 rows by 1 column and using that range rather than Map!E4:E30 in your SUMPRODUCT formula, then Excel would give the same result using --Map!E4:E30 in the SUMPRODUCT formula. I've NEVER seen an exception to that, and I'm going to be skeptical about claims otherwise until I see a live example. Maybe you meant that Map!E4:E30 is numeric but A3 isn't. In that case use (Map!$E$4:$E$30=--$A3) or (Map!$E$4:$E$30&""=$A3) But to be as generic as possible, use (-Map!$E$4:$E$30=-$A3) or (Map!$E$4:$E$30&""=$A3&"") So, all of this still brings me back to the point of-- how do I, consistently, constantly, get it to work, without having to go through and find out why one equation works and the very next one does not work. There's NOTHING wrong with Excel's -- double operator or &"" or SUMPRODUCT. The problem lies entirely in probable data type mismatches between your ranges Map!E4:E30 and A3. It's entirely and exclusively up to YOU to ensure that you're comparing text to text or numbers to numbers. My whole point for coming in here to ask is that the equation is not consistently working, and I'm spending far too much time going through looking for small, inconspicuous reasons for its failure. I came looking to learn more about this equation because it did once work, and it sped up my work process by an undefinable amount of time. It's now taking 3 x's longer to figure out why it's not working. It's not an 'equation'. It's a formula. If your formulas appear to behave inconsistently, far & away the most likely answer is that there are problems with YOUR data. To repeat, it's up to YOU to ensure that you're comparing text to text or numbers to numbers. There are ways to do that: --x is guaranteed to convert text representations of numbers to their numeric values (with possible floating point rounding/truncation error) while leaving numeric values unchanged, and x&"" is guaranteed to convert numeric values to their text representations while leaving text as-is. You could also have trailing nonbreaking HTML spaces, and Roger Govier has already shown how to handle those. You're saying that one column could be being recognized as text strings, when the other column its comparing to could be being recognized as numeric values, and as such it returns a false because the text string is not recognized the same as a numeric value. Correct. This then takes me back to the point of WHY? Because that's how Excel works. Available data types include numbers (usually double precision floating point), text, boolean and error values. A value in one data type NEVER equals a value in another data type even if they share an identical text representation. There are some programming languages in which this isn't the case. VBA, for instance, in which 1234# = "1234" returns TRUE. But that's because VBA and those other languages apply implicit type conversion even to operands of comparison operators. Excel doesn't. Whether it should or not is a different matter, and purely academic because Microsoft is extremely unlikely to change this behavior lest it break other people's existing formulas. I don't care what the value is. I don't care that 12345 is numeric in one cell, and a text string in the other column. I just want it to be recognized as being the same, because regardless of the format/properties behind each, it is still just 12345. There you're wrong. It's text in one cell and numeric in the other. You may not appreciate the difference, and it's clear you don't want to have to do so, but if you want to create reliable Excel formulas you're going to have to break down and learn this distinction. To repeat from above, this behavior is unlikely to change. Your choices are either to learn this and accommodate how Excel works or use something other than Excel. I entered it as JUST 12345. I did not enter it as "12345" in one column, and 12345 in the other. Nor did I enter it as 12345, etc... ad infinitum. Which seems to me to be strictly a background/behind-the-scenes format issue. OK, but if one cell was formatted as Text while the other was formatted as General, then the first will be STORED as text and the other as numeric. At this point I have to say I suspect you imported the list of permit numbers from some other system. If so, it's likely that other system stored the permit numbers as text, and Excel is respecting that other system's data types. So, going back to my original point-- 1- is there anyway to consistently get it to work one way, without having to spend hours looking at why it doesn't? .... Only by always coercing both sides of your = or comparisons to numeric or text. 3- if no one here knows the answers that I seek, who can I speak with that will get me the correct way to handle this? No one who knows more about Excel that I do will give you a different answer than I have. To repeat, this is just how Excel works, and it's unlikely to change. |
#22
|
|||
|
|||
Sumproduct issues
|
#23
|
|||
|
|||
Sumproduct issues
Hi all. After intense discussion, and "debate" I'll consider this post answered for now. I have in fact tried all of Harlan's, and Roger's recommended solutions, and have come up with what appears to be a viable means of solving the problem. And as such has already been implimented in updating workbooks. However, one of my colleagues has encountered an issue that I've not encountered, and in fact, it has never been an issue for me. He's found that all works except when he uses a formula in the final source column-- i.e., the last "array" of the sumproduct, in our case: =sumproduct ((...)*(...)*(Map!$C$3:$C$n)) Where 'n' is the end of the row range, and the formula that I'm referencing in the C column is of the general form- (=B2*x/y)-- ignore the paren's; x, and y are some predetermined values-- you can pick any arbitrary numbers. I quite frankly can't think of any reason why it would not work, as mine has always worked for this portion, and never once been an issue. My issues, it turns out were cross-datatypes that were returned as false, when the values represented should've returned true. So, again-- thank you for everyone's assistance. Hope all have a great T-day-- for those on this side of the "pond". For those overseas... enjoy the rest of your week. See you all next time I need help. "SteveDB1" wrote: Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#24
|
|||
|
|||
Sumproduct issues
Hi Steve
Glad to hear that the explanations and formulae I sent you back having seen your workbook solved your problems. You don't say what problems you are now getting. What is the error that is being returned? Are you talking about your Acre-feet column like column D in the workbook you sent me, with formulae like =C2*200/50 ? On your workbook, that was accurately being calculated in column J of sheet Sum. If it is not working in another workbook, then there must be something wrong with one or more of the values in column B, giving rise to an error in the calculated result for column C. It will not be an error with Sumproduct, but an error with the source data that needs correcting. -- Regards Roger Govier "SteveDB1" wrote in message ... Hi all. After intense discussion, and "debate" I'll consider this post answered for now. I have in fact tried all of Harlan's, and Roger's recommended solutions, and have come up with what appears to be a viable means of solving the problem. And as such has already been implimented in updating workbooks. However, one of my colleagues has encountered an issue that I've not encountered, and in fact, it has never been an issue for me. He's found that all works except when he uses a formula in the final source column-- i.e., the last "array" of the sumproduct, in our case: =sumproduct ((...)*(...)*(Map!$C$3:$C$n)) Where 'n' is the end of the row range, and the formula that I'm referencing in the C column is of the general form- (=B2*x/y)-- ignore the paren's; x, and y are some predetermined values-- you can pick any arbitrary numbers. I quite frankly can't think of any reason why it would not work, as mine has always worked for this portion, and never once been an issue. My issues, it turns out were cross-datatypes that were returned as false, when the values represented should've returned true. So, again-- thank you for everyone's assistance. Hope all have a great T-day-- for those on this side of the "pond". For those overseas... enjoy the rest of your week. See you all next time I need help. "SteveDB1" wrote: Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#25
|
|||
|
|||
Sumproduct issues
"SteveDB1" wrote: Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#26
|
|||
|
|||
Sumproduct issues
For some reason, I'm not able to see the OP, but am reading this through
Robin's post. As in all cases, no information is passed to the group when you just say "not working"! You described the return of the unary version as a zero total, but you didn't describe your problem with the asterisk version, except that it "only works sometimes". What happens when it (asterisk version) doesn't work? Your zero total using the unary version leads me to believe that the data in your totaling range are *all* text values. This is partially substantiated by the fact that the asterisk version works "sometimes". The asterisk version will total numeric and text values, as long as they even look like numbers. The unary version will bypass anything that's text, and simply not calculate them, with no warning or error messages. The asterisk version, even though it will calculate almost anything that even looks like a number, will error out if there is any value in the totaling column that doesn't look like a number. This includes nulls ( "" , zero length strings) that may be the returns of formulas in the totaling column, or invisible characters that may have been imported with the numbers from a web site (notably Char(160)). SO, if you're "not working", in relation to the asterisk form means a #Value! error, look in your totaling column for non-numeric text, or web imported characters. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Robin McLean" Robin wrote in message ... "SteveDB1" wrote: Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
Thread Tools | |
Display Modes | |
|
|