Formula help

  • Thread starter Thread starter Wally
  • Start date Start date
W

Wally

I have Windows XP 2002, home and small office edition.
I need help with a formula that will do the following:
1. If a number (i.e. 12) is entered into a singel cell
in the range of D3:E32 that equals 1/2 of a number
entered in cell range F3:f32, then add a 1 in cell R17.
2. If the same scenario occurs again in cell D4:E32 and
F4:f32, then another 1 is added in cell R17 making it a
total of 2 in cell R17. Everytime this scenerio happens
it is recorded in cell R17. I.E., if it happens six
times over a period of time, then R17 shows a 6.
Thanks for your help.
Wally
 
Wally said:
I have Windows XP 2002, home and small office edition.
I need help with a formula that will do the following:
1. If a number (i.e. 12) is entered into a singel cell
in the range of D3:E32 that equals 1/2 of a number
entered in cell range F3:f32, then add a 1 in cell R17.
2. If the same scenario occurs again in cell D4:E32 and
F4:f32, then another 1 is added in cell R17 making it a
total of 2 in cell R17. Everytime this scenerio happens
it is recorded in cell R17. I.E., if it happens six
times over a period of time, then R17 shows a 6.
Thanks for your help.
Wally
Click to expand...

If I understand your requirement correctly, you cannot do this with a
worksheet formula; you would need a macro. The reason is that you are
wanting to trigger the adding of 1 to R17 from an EVENT. Worksheet formulas
work on STATES, not EVENTS. In other words, with a few exceptions (as
always!) when you recalculate a worksheet you get the same result. The
formulas have no memory of history.

If I have misunderstood, please reply with a description of your
requirements that doesn't include 'events' or 'happenings' or whatever you
want to call them.
 
-----Original Message-----


If I understand your requirement correctly, you cannot do this with a
worksheet formula; you would need a macro. The reason is that you are
wanting to trigger the adding of 1 to R17 from an EVENT. Worksheet formulas
work on STATES, not EVENTS. In other words, with a few exceptions (as
always!) when you recalculate a worksheet you get the same result. The
formulas have no memory of history.

If I have misunderstood, please reply with a description of your
requirements that doesn't include 'events'
Click to expand...
or 'happenings' or whatever you
want to call them.

Paul,
Click to expand...
Presently I have a formula in cell R17 that counts every
time the number zero is inserted in a cell in the range
of d3:e32. The formula is;
=COUNTIF(d3:e32,0). If a zero in entered in one of the
cells within the range then the number of times a zero is
entered are added together in cell R17, i.e., when the
first zero is entered within the range then a one is
shown in R17, when another zero is entered in a different
cell within the range it is added to the 1 in cell r17
and makes it 2, etc. I am seeking a similar formula that
will do this: Instead of entering a zero in a cell in the
range d3:e32, I will enter an even number,
i.e.,2,4,6,8,10,12,14,16,18,or 20. Then I will enter into
the cell range e3:e32 the exact double of that number I
just entered; i.e. 4,8,12,16,20,24,28,32,36, or 40. This
is what I am trying to accomplish; when a number is
entered into cell range d3:e32 and it is exactly one-half
of the number entered into cell range e3:e32 it is
recorded in cell R17. For example, if an 8 is entered
into cell range D3:E32 and 16 is entered into cell range
F3:f32, then a 1 is shown in cell r17, if I were to enter
another number (i.e. 16) in a different cell in the range
Dd3:e32 and enter 32 in a cell in the e3:e32 range, cell
r17 shows a 2, which means the event happened twice.
I hope I have cleared this up a little for you, thanks
for your help.
Wally>
 
or 'happenings' or whatever you
Presently I have a formula in cell R17 that counts every
time the number zero is inserted in a cell in the range
of d3:e32. The formula is;
=COUNTIF(d3:e32,0). If a zero in entered in one of the
cells within the range then the number of times a zero is
entered are added together in cell R17, i.e., when the
first zero is entered within the range then a one is
shown in R17, when another zero is entered in a different
cell within the range it is added to the 1 in cell r17
and makes it 2, etc. I am seeking a similar formula that
will do this: Instead of entering a zero in a cell in the
range d3:e32, I will enter an even number,
i.e.,2,4,6,8,10,12,14,16,18,or 20. Then I will enter into
the cell range e3:e32 the exact double of that number I
just entered; i.e. 4,8,12,16,20,24,28,32,36, or 40. This
is what I am trying to accomplish; when a number is
entered into cell range d3:e32 and it is exactly one-half
of the number entered into cell range e3:e32 it is
recorded in cell R17. For example, if an 8 is entered
into cell range D3:E32 and 16 is entered into cell range
F3:f32, then a 1 is shown in cell r17, if I were to enter
another number (i.e. 16) in a different cell in the range
Dd3:e32 and enter 32 in a cell in the e3:e32 range, cell
r17 shows a 2, which means the event happened twice.
I hope I have cleared this up a little for you, thanks
for your help.
Wally>
Click to expand...

I'm sorry, but I'm still confused.
You say the formula
=COUNTIF(d3:e32,0)
"counts every time the number zero is inserted in a cell in the range of
d3:e32."
It doesn't. It counts the number of zeros in the range D3:E32.
If you enter a zero, delete it and enter it again in the same cell, you have
entered the zero twice, but the formula returns 1. There have been two
events, but worksheet formulas don't know about events - they calculate on
the present state. (Calculations based on events require a macro.)

Now to the problem you're trying to describe. Are you trying to count the
number of times you enter a number followed immediately by entering twice
that number (an event), or are you trying to ask how many numbers in one
range are exactly twice the numbers in another (a state)?
There is confusion about the two ranges. In your second post you have
D3:E32, E3:E32 and F3:F32 (ignoring DD3:E32 which I assume is just a typo).
In your original post you also had D4:E32 and F4:F32. In particular, it's
not clear whether your two ranges are the same size as each other. Also, are
you looking for 'doubles' in corresponding cells of the ranges, or anywhere
within the range?

If your ranges are the same size and you're looking for doubles in
corresponding cells, you can do this with a formula such as:
=SUMPRODUCT((B1:B10=2*A1:A10)*(ISNUMBER(A1:A10)))
 
-----Original Message-----
I have Windows XP 2002, home and small office edition.
I need help with a formula that will do the following:
1. If a number (i.e. 12) is entered into a singel cell
in the range of D3:E32 that equals 1/2 of a number
entered in cell range F3:f32, then add a 1 in cell R17.
2. If the same scenario occurs again in cell D4:E32 and
F4:f32, then another 1 is added in cell R17 making it a
total of 2 in cell R17. Everytime this scenerio happens
it is recorded in cell R17. I.E., if it happens six
times over a period of time, then R17 shows a 6.
Thanks for your help.
Wally
Paul, sorry I have confused you with my inablity to
Click to expand...
explain exactly what I want. You have helped to clear
this up and I'll try again.
As you stated in your reply; This is what I am trying to
do: I am trying to count the number of times I enter a
number followed immediately by entering twice that number.
The ranges that I will enter a number in are D3:D32 and
E3:E32 and the range that I will enter twice that number
is F3:F32. So, if I enter a 6 in D3, 23 in E3 and 12
(twice the number in D3) in F 3, the number one will show
up in R17. Next, if I enter 34 in D4 and 10 in E4 and 20
(twice the number in E4) in F4 it will then show the
number 2 in R17.
Hope this does it. Wally
 
explain exactly what I want. You have helped to clear
this up and I'll try again.
As you stated in your reply; This is what I am trying to
do: I am trying to count the number of times I enter a
number followed immediately by entering twice that number.
The ranges that I will enter a number in are D3:D32 and
E3:E32 and the range that I will enter twice that number
is F3:F32. So, if I enter a 6 in D3, 23 in E3 and 12
(twice the number in D3) in F 3, the number one will show
up in R17. Next, if I enter 34 in D4 and 10 in E4 and 20
(twice the number in E4) in F4 it will then show the
number 2 in R17.
Hope this does it. Wally
Click to expand...

You could try this formula in R17:
=SUMPRODUCT((ISNUMBER(F3:F32)*((F3:F32=2*D3:D32)+(F3:F32=2*E3:E32))))
 
-----Original Message-----


You could try this formula in R17:
=SUMPRODUCT((ISNUMBER(F3:F32)*((F3:F32=2*D3:D32)+ (F3:F32=2*E3:E32))))

It works!! Thank you for your patience and help.
.
Click to expand...
 
Back
Top