Not sure of why certain things happen with a sumproduct formula?

[Jennifre]

Hi, thanks so much for all the help I have received here to my [very dorky] queries here. Thank you!! (This is long, sorry! Hopefully someone will have enough patience to get through it.)

I'm using a sumproduct formula, and when I change one part of it, it changes my data by 100%. I'm wondering why, and what to do to avoid that because I sometimes will need to use the "troublesome" formula.

What I am using this for is to get stats about survey responses. Here is the sumproduct formula I use to match some data between two columns, and if that matches, give me the percentage responding with certain responses.

So, if I use the formula as shown below, gives me 50% of the responses I've received. The last line of the formula + the 2nd-to-last line gives me the percentage of total responses by adding the folks who responded greater than or = to 1, and less than or equal to 8 (8 possible responses).

So to increase the responses to 100% of those I received for the question, I find is reached if I delete the last line, or the "($DK$2:$DK$1364<=8)".

However, for SOME questions, there is a "decline" option in the last response, so I will NEED to use this >= and the <= but I'm worried using both of these will alter my total responses.

Any way to avoid this happening? And, why is it happening anyway? It doesn't make any sense to me. Thank you so much for your help!!

=SUMPRODUCT(
--($DG$2:$DG$1364=$HD3),
--ISNUMBER(MATCH($DK$2:$DK$1364,{5,6,7,8},0)))/
SUMPRODUCT(
--($DG$2:$DG$1364=$HD3),
--($DK$2:$DK$1364>=1)
--($DK$2:$DK$1364<=8))

 

[Jennifre]

I figured out how to get around this going foward, so that part of my Q is moot.

But why this does this, I'm still uncertain of?

Also, can we delete questions we pose here? I can't find that option.

Thank you.

 

[Aladin Akyurek]

I figured out how to get around this going foward, so that part of my Q is moot.

But why this does this, I'm still uncertain of?

Also, can we delete questions we pose here? I can't find that option.

Thank you.

No, you can't delete your contributions to the forum. By the way, what solution did you come up with?

 

[AlphaFrog]

for SOME questions, there is a "decline" option in the last response

But why this does this, I'm still uncertain of?

I'm not sure I follow what you are doing but if a cell has text in it, it will evaluate as greater than 1

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Decline</td><td style="text-align: right;;">TRUE</td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=A1>1</td></tr></tbody></table></td></tr></table><br />

Does that help?

 

[Jennifre]

Thanks Alpha for that tip.

And Aladin, my "solution" is kind of a cop-out, but for the questions I'll use this on where I really need to exclude a response gratefully at one end of the spectrum, I'll just use the >= part of the formula and not use <=1.

I'm still kind of confused, if not very, about why/how the original formula using both <=1 and >=8 (or 6, or whatever the high end of possible responses is) is halving the product of this formula?

Just to confuse things even further, I'm pretty sure the same two pieces of the same formula IS working in other questions and giving me 100% of the data/responses (don't want to look too carefully... yet).

Finding those data errors is... killer when my finding them is just by accident. What else do I have lying in there unnoticed?!

 

[Aladin Akyurek]

Hi, thanks so much for all the help I have received here to my [very dorky] queries here. Thank you!! (This is long, sorry! Hopefully someone will have enough patience to get through it.)

I'm using a sumproduct formula, and when I change one part of it, it changes my data by 100%. I'm wondering why, and what to do to avoid that because I sometimes will need to use the "troublesome" formula.

What I am using this for is to get stats about survey responses. Here is the sumproduct formula I use to match some data between two columns, and if that matches, give me the percentage responding with certain responses.

So, if I use the formula as shown below, gives me 50% of the responses I've received. The last line of the formula + the 2nd-to-last line gives me the percentage of total responses by adding the folks who responded greater than or = to 1, and less than or equal to 8 (8 possible responses).

So to increase the responses to 100% of those I received for the question, I find is reached if I delete the last line, or the "($DK$2:$DK$1364<=8)".

However, for SOME questions, there is a "decline" option in the last response, so I will NEED to use this >= and the <= but I'm worried using both of these will alter my total responses.

Any way to avoid this happening? And, why is it happening anyway? It doesn't make any sense to me. Thank you so much for your help!!

When you have an interval specification like >= 1 and <= 8, there would not be an issue with blanks or other text showing up in the range interest. When something like >= 1 or > 1 is the sole criterion, text values will give trouble as AlphaFrog rightly observes...

=SUMPRODUCT(
--($DG$2:$DG$1364=$HD3),
--ISNUMBER(MATCH($DK$2:$DK$1364,{5,6,7,8},0)))/
SUMPRODUCT(
--($DG$2:$DG$1364=$HD3),
--ISNUMBER($DK$2:$DK$1364),
--($DK$2:$DK$1364>=1))

would resolve the issue with text values round.