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.

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.

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.

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.

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.

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.

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.

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.