Can anyone please help me with two different formulas. In my worksheet the value of cell A1 updates automatically and will have a value of either 1 or 0. I need a formula in cell B1 that counts the number of times cell A1 has a value of 1. Also a way to reset it. The second formula I need is for cell C1. This formula needs to accumulate the amount of time cell A1 has a value of 1. Also a way to reset it. I have tried various countif or sumif formulas but cant get it to work.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Counting and time accumulation formula help
- Thread starter brian5857
- Start date
Oaktree
MrExcel MVP
- Joined
- Jun 20, 2002
- Messages
- 8,115
- Office Version
- 365
Welcome to the board!
Can you post your code that is "automatically updating" A1?
A workaround would be to, rather than write over A1 with each update, put the update and the time of the update as the next entry in 2 new columns.
Can you post your code that is "automatically updating" A1?
A workaround would be to, rather than write over A1 with each update, put the update and the time of the update as the next entry in 2 new columns.
Upvote
0
Thanks for the help.
The cell A1 is updated from a program called RSLinx.
=RSLINX|PDCSHIP!'O;002/04,L1,C1'
The value will either be a 1 or a 0. I am trying to write a formula in each of the two cell next to A1. One cell that adds up the total number of times A1 has a value of 1 and one cell that displays the total amount time A1 has a value of 1 over a 24 hour period.
The cell A1 is updated from a program called RSLinx.
=RSLINX|PDCSHIP!'O;002/04,L1,C1'
The value will either be a 1 or a 0. I am trying to write a formula in each of the two cell next to A1. One cell that adds up the total number of times A1 has a value of 1 and one cell that displays the total amount time A1 has a value of 1 over a 24 hour period.
Upvote
0
Oaktree
MrExcel MVP
- Joined
- Jun 20, 2002
- Messages
- 8,115
- Office Version
- 365
Ok, that defines the formula in A1, but what causes A1 to be updated? Do you just press F9 to recalculate it? Do you import new data? Or do you use some sort of macro? The macro you'll need to list the information about each change will depend on the method used to update the value.
The "number of times A1 has a value of 1" cannot be done by a formula alone, since A1 will, at one time, house only a 0 or a 1. You will need to fire a macro every time you want A1 to update.
The "number of times A1 has a value of 1" cannot be done by a formula alone, since A1 will, at one time, house only a 0 or a 1. You will need to fire a macro every time you want A1 to update.
Upvote
0
Cell A1 updates all by itself. It is referencing to a value in the PLC program which is always changing. I dont use a macro or F9....
I was trying to set up the worksheet so that every time cell A1 has a value of 1 it puts a 1 in the next available cell next to A1. Which would be B1 and then C1...
For example:
A1
0
A1 B1
1 1
A1 B1
0 1
A1 B1 C1
1 1 1
Then Find the sum of B1 through However for it goes. At least that is what I am trying but cannot get it to work.
I was trying to set up the worksheet so that every time cell A1 has a value of 1 it puts a 1 in the next available cell next to A1. Which would be B1 and then C1...
For example:
A1
0
A1 B1
1 1
A1 B1
0 1
A1 B1 C1
1 1 1
Then Find the sum of B1 through However for it goes. At least that is what I am trying but cannot get it to work.
Upvote
0
Similar threads
- Question
