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 |
#11
|
|||
|
|||
Sumproduct is returning irregular values
Thank you very much Tom. I am good to go now.
-- Thanks Marlaine "Tom-S" wrote: Sorry for delay in replying. I found this out from the link (posted earlier) and have tested to make sure it works. Toward the end of the link are some notes and note number 1 shows how to test for 2 or more values from the same range. So if you wanted to test for "Q1" or "Q3" from $A$2:$A$7500 it would be: =SUMPRODUCT((Actuals!$A$2:$A$7500={"Q1","Q3"})*(.. .)) I believe the curly brackets { } are called 'braces', and they're typically found above the square brackets [ ] on the keyboard. Regards, Tom "Marlaine" wrote: Thank you Tom. Great info. I understand your answer to question 2. How then would I format the formula if I wanted to sum up Q1 and Q3? I expect you would need an or. Something like =SUMPRODUCT(OR(Actuals!$A$2:$A$7500="Q1"),(Actuals !$A$2:$A$7500="Q3"))*(..... -- Thanks Marlaine "Tom-S" wrote: I haven't tried the sumifs myself yet as I'm still fairly attached to Excel 2003, which doesn't have sumifs, but I hear what you're saying about the isna's. Your other 2 questions: 1) It's the use of the LEFT function, which creates a text string as its result, so you have to use "50" rather than 50 in the sumproduct function. (A simple test: On a new sheet, format cells B2 and C2 to numbers with 2 d.p.; type a number, say 810 in cell C2; type this formula in cell B2 =LEFT(C2,2) and this formula in cell B4 =IF(ISTEXT(B2),"txt","num") cell B2 will display 81 (with no decimal places) and cell B4 will display 'txt' (because the content of cell B2 is a text string); if you then type the number 81 directly into cell B2, you should see cell B4 display 'num' instead.) 2) Actually, the sumproduct function here was working as it should by returning zero as the answer. Say you were only looking at data in A1:A5 and the data was Q1,Q2,Q2,Q1,Q2; the first condition (=Q1) would return an array {1,0,0,1,0} and the second condition (=Q2) would return an array {0,1,1,0,1}; when these arrays are multiplied together the result is zero; this result was due to the particular conditions set in the formula rather than the 'lookup' referring to the same column of data as such. Hope that helps. Let me know if there's more. Regards, Tom "Marlaine" wrote: Sorry about the confusion earlier. I have read the link and it explains things very well. I like sumproduct better then sumifs because it seems like you dont have to add error conditions, eg isna - dont understand those so not sure if that statement made sense. I am getting the expected values in my formulas so thank you. There are still two things I do not understand though. 1) If I am looking up the value 50 in column B, it needs to be in quotes. If I am looking up the value 6938 in column F, I do not need quotes. Why the difference? Column B is calculated from Column F by the Left formula 2) I originally had =SUMPRODUCT((Actuals!$A$2:$A$7500="Q1")*(Actuals!$ A$2:$A$7500="Q2"..... but it would return zero. Can you only have one lookup per column in the formula? -- Thanks Marlaine "Tom-S" wrote: I've been using Sumproduct quite a lot lately and would have used the 50 without quotes (provided it refers to numeric data), so I think the problem may be with one of the other conditions - have you tried variations of these e.g. ="Q4" for condition 1 instead of "Q4" (assuming A2:A7500 contains Q4-type alphnumeric data, rather than Q4 being a cell reference). Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html If you'd like to send a copy of your workbook, let me know. Regards, Tom-S "Marlaine" wrote: Hi there I am developing a skookum s/s where I am returning values based on various criteria and am using sumproduct. The issue seems to be with the format of a cell. Here is one of my calculations. =SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500) It is returning zero. However, if I put the 50 in quotes, it will return the correct value. My issue tho is that some of the calculations don't need quotes and some do. Eg, if I put "6531" in the following equation it will return 0. =SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500) I have not formatted the cells at all but they are exported from a home grown system. I've played around with changing the format of the columns to Number and general with no change. Any help would be great as I do not trust my formulas as yet -- Thanks Marlaine |
#12
|
|||
|
|||
Sumproduct is returning irregular values
Thanks Max. I did see that early and have changed my calculated values from
Q1, Q2 etc to 1, 2, 3 4 so that the equation will always work. I just kept copying and pasting my original formula during the queries. The replies in this forum are extremely helpful and educational! -- Thanks Marlaine "Max" wrote: Your original test, this term: (Actuals!$A$2:$A$7500"Q4") was meaningless because "" is an operator meant for numbers, not text. "Q4" is text, and unfortunately, because of the way that Excel evaluates it as clarified in my response, the results can be misleading. Just a parting thought here for you. . |
|
Thread Tools | |
Display Modes | |
|
|