| 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?
Do you go by the alias "Novice" or is this a class assignment?
|
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct?
yes
"TomPl" wrote: Do you go by the alias "Novice" or is this a class assignment? |
|
#9
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? |
| 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 | |||
Hybrid Mode
