Prev: Shapes on Worksheets Move And Resize
Next: How do I edit directly in a cell - previously F2
From: Sai Krishna on 18 Feb 2010 04:55
Hi,

I have a column which displays percentages from 10% to 50%. Next column
displays a text which repeats several times in that column against each
percentage.

In the answer column, I am required to first state the number of occurance
between a range of percentages and the number of occurance of the texts the
fall in that range.

Per Text
10% a
11% b
12% c
13% b
14% c

Note: Showing only part of the list

In my answer sheeti have something like this
Range Count
from to
10% 18% 9
19% 22% 4
23% 25% 3
26% 28% 3

Now I also need to show next to the count column how many "a", "b" and "c"
fall against each of the ranges
regards
sai

From: Mike H on 18 Feb 2010 05:12
Hi,

So we have percentage in col A and person in Col B, Try this

=SUMPRODUCT((B1:B20="a")*(A1:A20>=0.1)*(A1:A20<=0.18))

Note I've used the decimal equavalent for the percentage and the formula
does the 10% to 18% range for person A.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sai Krishna" wrote:

> Hi,
>
> I have a column which displays percentages from 10% to 50%. Next column
> displays a text which repeats several times in that column against each
> percentage.
>
> In the answer column, I am required to first state the number of occurance
> between a range of percentages and the number of occurance of the texts the
> fall in that range.
>
> Per Text
> 10% a
> 11% b
> 12% c
> 13% b
> 14% c
>
> Note: Showing only part of the list
>
> In my answer sheeti have something like this
> Range Count
> from to
> 10% 18% 9
> 19% 22% 4
> 23% 25% 3
> 26% 28% 3
>
> Now I also need to show next to the count column how many "a", "b" and "c"
> fall against each of the ranges
> regards
> sai
>
From: Jacob Skaria on 18 Feb 2010 05:19
With your data in Sheet1 and the answer sheet as Sheet2..

In the answer sheet(say Sheet2) with start % in cell A1 and end % in cell
B1; try the below formula in cell C1

=SUMPRODUCT((Sheet1!A1:A100>=10%)*(Sheet1!A1:A100<=18%)*
(Sheet1!B1:B100={"a","b","c"}))

--
Jacob


"Sai Krishna" wrote:

> Hi,
>
> I have a column which displays percentages from 10% to 50%. Next column
> displays a text which repeats several times in that column against each
> percentage.
>
> In the answer column, I am required to first state the number of occurance
> between a range of percentages and the number of occurance of the texts the
> fall in that range.
>
> Per Text
> 10% a
> 11% b
> 12% c
> 13% b
> 14% c
>
> Note: Showing only part of the list
>
> In my answer sheeti have something like this
> Range Count
> from to
> 10% 18% 9
> 19% 22% 4
> 23% 25% 3
> 26% 28% 3
>
> Now I also need to show next to the count column how many "a", "b" and "c"
> fall against each of the ranges
> regards
> sai
>
 | 
Pages: 1
Prev: Shapes on Worksheets Move And Resize
Next: How do I edit directly in a cell - previously F2