greater than less than

[Rene]

How do I insert (F2:F10>50) into the formula? Percentage of numbers between
50 and 100

SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b")

Thanks in advance.

 

[Mike H]

Try

=SUMPRODUCT((M2:M10="b")*(F2:F10>50)*(F2:F10<100))/COUNTIF(M2:M10,"b")

Mike

 

[ShaneDevenshire]

Hi,

Or to continue using the -- approach

=SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100)*(--(F2:F10>50))/COUNTIF(M2:M10,"b"))

 

[Rene]

Both returned 0% when it should of returned 20%

Hi,

Or to continue using the -- approach

=SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100)*(--(F2:F10>50))/COUNTIF(M2:M10,"b"))

 

[David Biddulph]

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:F10>50))/COUNTIF(M2:M10,"b"),or as =SUMPRODUCT((M2:M10="b")*(F2:F10<100)*(F2:F10>50))/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" <[email protected]> wrote in messageBoth 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:F10>50))/COUNTIF(M2:M10,"b"))>>>> -->> Thanks,>> Shane Devenshire>>>>>> "Rene" wrote:>>>> > How do I insert (F2:F10>50) into the formula? Percentage of numbersbetween>> > 50 and 100>> >>> > SUMPRODUCT(--(M2:M10="b"),--(F2:F10<100))/COUNTIF(M2:M10,"b")>> >>> > Thanks in advance.

 

[Rene]

umm...my apologies.

I changed the data. It works. Averageif works, but I haven't solved MIN

=AVERAGEIF(M2:M10,"b",F2:F10)

 

[ShaneDevenshire]

Hi,

There are advantages in 2007!

 

[Rene]

I'm using 2007

Hi,

There are advantages in 2007!