Identify occurence in "array" which meets condition(s)
 

Example Scenario: where my formula tests whether the value in column A equals
a multiple of 5. The result is displayed in column B.
Call No. Meet Formula?
1
2
3
4
5 Y
6
7
8
9
10 Y
Total 2

So, from the list above, I know that the 5th and 10th call will meet the
formula.

Problem:
My formula is a lot more complicated than just checking for multiples of 5
and I have to run the formula per row (it will be a v long explanation if you
wanna know why). Is there a way such that I just enter "20" in one cell and
the formula gives me a result of "5, 10, 15, 20" (using the multiple of 5
example)?

I wish I can share my spreadsheet with the formula with an expert. Any
volunteers?

Melissa,

You can easily test if it is a multiple of 5

=IF(MOD(A1,5)=0,"Y","")

but what do you mean by putting 20 in, do you then want to also test that
the value is <= 20,. so 25 would not show a Y? If so, then use

=IF(AND(A1<=B1,MOD(A1,5)=0),"Y","")


RP
(remove nothere from the email address if mailing direct)


"Melissa" wrote in message
...
Example Scenario: where my formula tests whether the value in column A
equals
a multiple of 5. The result is displayed in column B.
Call No. Meet Formula?
1
2
3
4
5 Y
6
7
8
9
10 Y
Total 2

So, from the list above, I know that the 5th and 10th call will meet the
formula.

Problem:
My formula is a lot more complicated than just checking for multiples of 5
and I have to run the formula per row (it will be a v long explanation if
you
wanna know why). Is there a way such that I just enter "20" in one cell
and
the formula gives me a result of "5, 10, 15, 20" (using the multiple of 5
example)?

I wish I can share my spreadsheet with the formula with an expert. Any
volunteers?

What I mean by 20 is if the no. of calls extend to 20. My earlier list goes
up to 10 only. So rather than extending my list to, say, 6500, I just want
to key in 20 and get the result "4". Likewise, if I key in "18", I should
get "3".

And thanks for the MOD formula. I actually know how to use it already but
my formula is not as simple as checking for multiples of 5. I'll have to
e-mail you my spreadsheet to explain the formula! Can I e-mail it to you??

"Bob Phillips" wrote:

Melissa,

You can easily test if it is a multiple of 5

=IF(MOD(A1,5)=0,"Y","")

but what do you mean by putting 20 in, do you then want to also test that
the value is <= 20,. so 25 would not show a Y? If so, then use

=IF(AND(A1<=B1,MOD(A1,5)=0),"Y","")


RP
(remove nothere from the email address if mailing direct)


"Melissa" wrote in message
...
Example Scenario: where my formula tests whether the value in column A
equals
a multiple of 5. The result is displayed in column B.
Call No. Meet Formula?
1
2
3
4
5 Y
6
7
8
9
10 Y
Total 2

So, from the list above, I know that the 5th and 10th call will meet the
formula.

Problem:
My formula is a lot more complicated than just checking for multiples of 5
and I have to run the formula per row (it will be a v long explanation if
you
wanna know why). Is there a way such that I just enter "20" in one cell
and
the formula gives me a result of "5, 10, 15, 20" (using the multiple of 5
example)?

I wish I can share my spreadsheet with the formula with an expert. Any
volunteers?

Yeah, that is okay.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Melissa" wrote in message
...
What I mean by 20 is if the no. of calls extend to 20. My earlier list
goes
up to 10 only. So rather than extending my list to, say, 6500, I just
want
to key in 20 and get the result "4". Likewise, if I key in "18", I should
get "3".

And thanks for the MOD formula. I actually know how to use it already but
my formula is not as simple as checking for multiples of 5. I'll have to
e-mail you my spreadsheet to explain the formula! Can I e-mail it to
you??

"Bob Phillips" wrote:

Melissa,

You can easily test if it is a multiple of 5

=IF(MOD(A1,5)=0,"Y","")

but what do you mean by putting 20 in, do you then want to also test
that
the value is <= 20,. so 25 would not show a Y? If so, then use

=IF(AND(A1<=B1,MOD(A1,5)=0),"Y","")


RP
(remove nothere from the email address if mailing direct)


"Melissa" wrote in message
...
Example Scenario: where my formula tests whether the value in column A
equals
a multiple of 5. The result is displayed in column B.
Call No. Meet Formula?
1
2
3
4
5 Y
6
7
8
9
10 Y
Total 2

So, from the list above, I know that the 5th and 10th call will meet
the
formula.

Problem:
My formula is a lot more complicated than just checking for multiples
of 5
and I have to run the formula per row (it will be a v long explanation
if
you
wanna know why). Is there a way such that I just enter "20" in one
cell
and
the formula gives me a result of "5, 10, 15, 20" (using the multiple
of 5
example)?

I wish I can share my spreadsheet with the formula with an expert.
Any
volunteers?