| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct?
I want to calculate the percentage for when "b" apears in M2:M10 and the
number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as
..55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
50 and 100 are numbers, not %s.
I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
Try this instead
SUMPRODUCT(--(B2:B1050),--(B2:B10<=100))/COUNT(B2:B10) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100))/COUNT(C2:C10) "Rene" wrote: 50 and 100 are numbers, not %s. I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
I'm confused; not hard. Isn't that the same as I was doing with the two
formulas? I'm trying to elininate column c by entering all the data in b2:b10 and then using the criteria in m2:m10. If "b" compute the formula in N2, if criteria is "s" compute the formula in O2. "Joel" wrote: Try this instead SUMPRODUCT(--(B2:B1050),--(B2:B10<=100))/COUNT(B2:B10) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100))/COUNT(C2:C10) "Rene" wrote: 50 and 100 are numbers, not %s. I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
It turns out just adding another set of parethesis to your formula solves the
problem =SUMPRODUCT(--(B2:B1050),(--(B2:B10<=100)/COUNT(B2:B10))) You formula was giving a divide by 0 error. Excel says that empty cells in arrays are treated as zeroes. I think leaving the parenthesis out was dimensioning an internal array incorrectly and producing zeroes in the denominator. "Rene" wrote: I'm confused; not hard. Isn't that the same as I was doing with the two formulas? I'm trying to elininate column c by entering all the data in b2:b10 and then using the criteria in m2:m10. If "b" compute the formula in N2, if criteria is "s" compute the formula in O2. "Joel" wrote: Try this instead SUMPRODUCT(--(B2:B1050),--(B2:B10<=100))/COUNT(B2:B10) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100))/COUNT(C2:C10) "Rene" wrote: 50 and 100 are numbers, not %s. I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
Thanks, can I use lookup in (m2:m10) to determine if the formula will be
calculated? "Joel" wrote: It turns out just adding another set of parethesis to your formula solves the problem =SUMPRODUCT(--(B2:B1050),(--(B2:B10<=100)/COUNT(B2:B10))) You formula was giving a divide by 0 error. Excel says that empty cells in arrays are treated as zeroes. I think leaving the parenthesis out was dimensioning an internal array incorrectly and producing zeroes in the denominator. "Rene" wrote: I'm confused; not hard. Isn't that the same as I was doing with the two formulas? I'm trying to elininate column c by entering all the data in b2:b10 and then using the criteria in m2:m10. If "b" compute the formula in N2, if criteria is "s" compute the formula in O2. "Joel" wrote: Try this instead SUMPRODUCT(--(B2:B1050),--(B2:B10<=100))/COUNT(B2:B10) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100))/COUNT(C2:C10) "Rene" wrote: 50 and 100 are numbers, not %s. I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
I don't completely understand your questions. Don't want to give you an
answer that doesn't apply. "Rene" wrote: Thanks, can I use lookup in (m2:m10) to determine if the formula will be calculated? "Joel" wrote: It turns out just adding another set of parethesis to your formula solves the problem =SUMPRODUCT(--(B2:B1050),(--(B2:B10<=100)/COUNT(B2:B10))) You formula was giving a divide by 0 error. Excel says that empty cells in arrays are treated as zeroes. I think leaving the parenthesis out was dimensioning an internal array incorrectly and producing zeroes in the denominator. "Rene" wrote: I'm confused; not hard. Isn't that the same as I was doing with the two formulas? I'm trying to elininate column c by entering all the data in b2:b10 and then using the criteria in m2:m10. If "b" compute the formula in N2, if criteria is "s" compute the formula in O2. "Joel" wrote: Try this instead SUMPRODUCT(--(B2:B1050),--(B2:B10<=100))/COUNT(B2:B10) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100))/COUNT(C2:C10) "Rene" wrote: 50 and 100 are numbers, not %s. I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
I can confuse myself with excel...
Currently I have a2 b2 c2 d2 105 85 SUMPRODUCT(--(B2:B10=50),--(B2:b10<=100)/COUNT(B2:b10)) returns 50% a2 b2 c2 d2 145 75 SUMPRODUCT(--(C2:C10=50),--(C2:C10<=100)/COUNT(C2:C10)) returns 50% Would like :) a2 b2 c2 d2 m2 n2 o2 105 b 'b' formula returns 50% 145 s 's' formula returns 50% 75 s 85 b Can I do it? "Joel" wrote: I don't completely understand your questions. Don't want to give you an answer that doesn't apply. "Rene" wrote: Thanks, can I use lookup in (m2:m10) to determine if the formula will be calculated? "Joel" wrote: It turns out just adding another set of parethesis to your formula solves the problem =SUMPRODUCT(--(B2:B1050),(--(B2:B10<=100)/COUNT(B2:B10))) You formula was giving a divide by 0 error. Excel says that empty cells in arrays are treated as zeroes. I think leaving the parenthesis out was dimensioning an internal array incorrectly and producing zeroes in the denominator. "Rene" wrote: I'm confused; not hard. Isn't that the same as I was doing with the two formulas? I'm trying to elininate column c by entering all the data in b2:b10 and then using the criteria in m2:m10. If "b" compute the formula in N2, if criteria is "s" compute the formula in O2. "Joel" wrote: Try this instead SUMPRODUCT(--(B2:B1050),--(B2:B10<=100))/COUNT(B2:B10) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100))/COUNT(C2:C10) "Rene" wrote: 50 and 100 are numbers, not %s. I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
Do you go by the alias "Novice" or is this a class assignment?
|
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
yes
"TomPl" wrote: Do you go by the alias "Novice" or is this a class assignment? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
Put the sumproduct into a new cell like A1. Then multiple the m column
formula by a1. "Rene" wrote: I can confuse myself with excel... Currently I have a2 b2 c2 d2 105 85 SUMPRODUCT(--(B2:B10=50),--(B2:b10<=100)/COUNT(B2:b10)) returns 50% a2 b2 c2 d2 145 75 SUMPRODUCT(--(C2:C10=50),--(C2:C10<=100)/COUNT(C2:C10)) returns 50% Would like :) a2 b2 c2 d2 m2 n2 o2 105 b 'b' formula returns 50% 145 s 's' formula returns 50% 75 s 85 b Can I do it? "Joel" wrote: I don't completely understand your questions. Don't want to give you an answer that doesn't apply. "Rene" wrote: Thanks, can I use lookup in (m2:m10) to determine if the formula will be calculated? "Joel" wrote: It turns out just adding another set of parethesis to your formula solves the problem =SUMPRODUCT(--(B2:B1050),(--(B2:B10<=100)/COUNT(B2:B10))) You formula was giving a divide by 0 error. Excel says that empty cells in arrays are treated as zeroes. I think leaving the parenthesis out was dimensioning an internal array incorrectly and producing zeroes in the denominator. "Rene" wrote: I'm confused; not hard. Isn't that the same as I was doing with the two formulas? I'm trying to elininate column c by entering all the data in b2:b10 and then using the criteria in m2:m10. If "b" compute the formula in N2, if criteria is "s" compute the formula in O2. "Joel" wrote: Try this instead SUMPRODUCT(--(B2:B1050),--(B2:B10<=100))/COUNT(B2:B10) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100))/COUNT(C2:C10) "Rene" wrote: 50 and 100 are numbers, not %s. I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
I'm assuming that I can't do it. Thanks again for your help.
Novice/Rene "TomPl" wrote: Do you go by the alias "Novice" or is this a class assignment? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
This worked
=SUMPRODUCT((M2:M10)="b")--SUMPRODUCT(--(F2:F100),--(F2:F10<=105))/COUNT(F2:F10) m column does not contain a formula just the criteria 'b' or 's' "Joel" wrote: Put the sumproduct into a new cell like A1. Then multiple the m column formula by a1. "Rene" wrote: I can confuse myself with excel... Currently I have a2 b2 c2 d2 105 85 SUMPRODUCT(--(B2:B10=50),--(B2:b10<=100)/COUNT(B2:b10)) returns 50% a2 b2 c2 d2 145 75 SUMPRODUCT(--(C2:C10=50),--(C2:C10<=100)/COUNT(C2:C10)) returns 50% Would like :) a2 b2 c2 d2 m2 n2 o2 105 b 'b' formula returns 50% 145 s 's' formula returns 50% 75 s 85 b Can I do it? "Joel" wrote: I don't completely understand your questions. Don't want to give you an answer that doesn't apply. "Rene" wrote: Thanks, can I use lookup in (m2:m10) to determine if the formula will be calculated? "Joel" wrote: It turns out just adding another set of parethesis to your formula solves the problem =SUMPRODUCT(--(B2:B1050),(--(B2:B10<=100)/COUNT(B2:B10))) You formula was giving a divide by 0 error. Excel says that empty cells in arrays are treated as zeroes. I think leaving the parenthesis out was dimensioning an internal array incorrectly and producing zeroes in the denominator. "Rene" wrote: I'm confused; not hard. Isn't that the same as I was doing with the two formulas? I'm trying to elininate column c by entering all the data in b2:b10 and then using the criteria in m2:m10. If "b" compute the formula in N2, if criteria is "s" compute the formula in O2. "Joel" wrote: Try this instead SUMPRODUCT(--(B2:B1050),--(B2:B10<=100))/COUNT(B2:B10) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100))/COUNT(C2:C10) "Rene" wrote: 50 and 100 are numbers, not %s. I'm using the formulas SUMPRODUCT(--(B2:B1050),--(B2:B10<=100)/COUNT(B2:B10)) and SUMPRODUCT(--(C2:C1050),--(C2:C10<=100)/COUNT(C2:C10)) to look in seperate columns. I would like a formula to reduce the numer of columns. Critera being if m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is 's' calculate for b2:l2 (formula is in cell o2) Thanks again "Joel" wrote: 50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as .55. "Rene" wrote: I want to calculate the percentage for when "b" apears in M2:M10 and the number is between 50 and 100 =SUMPRODUCT(--(B2:L1050),--(B2:L10<=100)/COUNT(B2:L10)) a1 b2 c2 m2 111 b 99 s 200 s 110 b 55 b 99 s 225 b b % = 25% (50 and <=100 b2:l10) Thank you in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| Sumproduct | Excel Discussion (Misc queries) | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| sumproduct | Excel Worksheet Functions | |||
| Can I use SUMPRODUCT for this? | Excel Worksheet Functions | |||
Linear Mode
