If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula help
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 |
#2
|
|||
|
|||
Formula help
"Wally" wrote in 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 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. |
#3
|
|||
|
|||
Formula help
-----Original Message----- "Wally" wrote in 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 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. Paul, 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 |
#4
|
|||
|
|||
Formula help
wrote in message
... -----Original Message----- "Wally" wrote in 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 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. Paul, 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 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))) |
#5
|
|||
|
|||
Formula help
-----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 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 D332 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 |
#6
|
|||
|
|||
Formula help
wrote in message
... -----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 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 D332 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 You could try this formula in R17: =SUMPRODUCT((ISNUMBER(F3:F32)*((F3:F32=2*D332)+( F3:F32=2*E3:E32)))) |
#7
|
|||
|
|||
Formula help
-----Original Message----- wrote in message ... -----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 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 D332 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 You could try this formula in R17: =SUMPRODUCT((ISNUMBER(F3:F32)*((F3:F32=2*D332 )+ (F3:F32=2*E3:E32)))) It works!! Thank you for your patience and help. . |
Thread Tools | |
Display Modes | |
|
|