Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
greater than less than
How do I insert (F2:F1050) into the formula? Percentage of numbers between
50 and 100 SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b") Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
greater than less than
Try
=SUMPRODUCT((M2:M10="b")*(F2:F1050)*(F2:F10<100))/COUNTIF(M2:M10,"b") Mike "Rene" wrote: How do I insert (F2:F1050) into the formula? Percentage of numbers between 50 and 100 SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b") Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
greater than less than
Hi,
Or to continue using the -- approach =SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100)*(--(F2:F1050))/COUNTIF(M2:M10,"b")) -- Thanks, Shane Devenshire "Rene" wrote: How do I insert (F2:F1050) into the formula? Percentage of numbers between 50 and 100 SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b") Thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
greater than less than
Both returned 0% when it should of returned 20%
"ShaneDevenshire" wrote: Hi, Or to continue using the -- approach =SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100)*(--(F2:F1050))/COUNTIF(M2:M10,"b")) -- Thanks, Shane Devenshire "Rene" wrote: How do I insert (F2:F1050) into the formula? Percentage of numbers between 50 and 100 SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b") Thanks in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
greater than less than
If you've got the wrong answer, then presumably your data values aren't what
you thought they were, or your original formula wasn't what you needed for your purpose. What values are in M2:M10 and in F2:F10? The formula might be clearer as =SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100),--(F2:F1050))/COUNTIF(M2:M10,"b"),or as =SUMPRODUCT((M2:M10="b")*(F2:F10<100)*(F2:F1050))/COUNTIF(M2:M10,"b")but the result will be the same as Shane's if the data is good.Also, I think you meant "should have", rather than "should of".--David Biddulph"Rene" wrote in ... Both returned 0% when it should of returned 20% "ShaneDevenshire" wrote: Hi, Or to continue using the -- approach=SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100)*(--(F2:F1050))/COUNTIF(M2:M10,"b")) -- Thanks, Shane Devenshire "Rene" wrote: How do I insert (F2:F1050) into the formula? Percentage of numbersbetween 50 and 100 SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b") Thanks in advance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
greater than less than
umm...my apologies.
I changed the data. It works. Averageif works, but I haven't solved MIN =AVERAGEIF(M2:M10,"b",F2:F10) "David Biddulph" wrote: If you've got the wrong answer, then presumably your data values aren't what you thought they were, or your original formula wasn't what you needed for your purpose. What values are in M2:M10 and in F2:F10? The formula might be clearer as =SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100),--(F2:F1050))/COUNTIF(M2:M10,"b"),or as =SUMPRODUCT((M2:M10="b")*(F2:F10<100)*(F2:F1050))/COUNTIF(M2:M10,"b")but the result will be the same as Shane's if the data is good.Also, I think you meant "should have", rather than "should of".--David Biddulph"Rene" wrote in ... Both returned 0% when it should of returned 20% "ShaneDevenshire" wrote: Hi, Or to continue using the -- approach=SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100)*(--(F2:F1050))/COUNTIF(M2:M10,"b")) -- Thanks, Shane Devenshire "Rene" wrote: How do I insert (F2:F1050) into the formula? Percentage of numbersbetween 50 and 100 SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b") Thanks in advance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
greater than less than
Hi,
There are advantages in 2007! -- Thanks, Shane Devenshire "Rene" wrote: umm...my apologies. I changed the data. It works. Averageif works, but I haven't solved MIN =AVERAGEIF(M2:M10,"b",F2:F10) "David Biddulph" wrote: If you've got the wrong answer, then presumably your data values aren't what you thought they were, or your original formula wasn't what you needed for your purpose. What values are in M2:M10 and in F2:F10? The formula might be clearer as =SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100),--(F2:F1050))/COUNTIF(M2:M10,"b"),or as =SUMPRODUCT((M2:M10="b")*(F2:F10<100)*(F2:F1050))/COUNTIF(M2:M10,"b")but the result will be the same as Shane's if the data is good.Also, I think you meant "should have", rather than "should of".--David Biddulph"Rene" wrote in ... Both returned 0% when it should of returned 20% "ShaneDevenshire" wrote: Hi, Or to continue using the -- approach=SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100)*(--(F2:F1050))/COUNTIF(M2:M10,"b")) -- Thanks, Shane Devenshire "Rene" wrote: How do I insert (F2:F1050) into the formula? Percentage of numbersbetween 50 and 100 SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b") Thanks in advance. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
greater than less than
I'm using 2007
"ShaneDevenshire" wrote: Hi, There are advantages in 2007! -- Thanks, Shane Devenshire "Rene" wrote: umm...my apologies. I changed the data. It works. Averageif works, but I haven't solved MIN =AVERAGEIF(M2:M10,"b",F2:F10) "David Biddulph" wrote: If you've got the wrong answer, then presumably your data values aren't what you thought they were, or your original formula wasn't what you needed for your purpose. What values are in M2:M10 and in F2:F10? The formula might be clearer as =SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100),--(F2:F1050))/COUNTIF(M2:M10,"b"),or as =SUMPRODUCT((M2:M10="b")*(F2:F10<100)*(F2:F1050))/COUNTIF(M2:M10,"b")but the result will be the same as Shane's if the data is good.Also, I think you meant "should have", rather than "should of".--David Biddulph"Rene" wrote in ... Both returned 0% when it should of returned 20% "ShaneDevenshire" wrote: Hi, Or to continue using the -- approach=SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100)*(--(F2:F1050))/COUNTIF(M2:M10,"b")) -- Thanks, Shane Devenshire "Rene" wrote: How do I insert (F2:F1050) into the formula? Percentage of numbersbetween 50 and 100 SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b") Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Greater than # but less than # | Excel Worksheet Functions | |||
If sum greater than 12 use 12 | Excel Discussion (Misc queries) | |||
if value is greater than # * by 20% if less * by 40 | New Users to Excel | |||
Greater Than but Less than | Excel Worksheet Functions | |||
IF greater than Zero | Excel Worksheet Functions |