Forum Discussion
Solved
Requesting help to programme a spreadsheet
Good morning,
I would like to have your guidance on how to programme this spreadsheet the way my Supervisor wants.
If you open the sample file, between rows 3 to 5, the engineer will key in these data
1. Number of the unit
2. Its start up and shut down date and time.
Column I will calculate the duration between shutdown and start up (I have programmed it).
The problem is I don't know how to make the table from row 10 to 15 to update the number of starts based on the type of starts . It has to identify the type of unit and also the number of hours to update the table. This is where I get stuck.
Kindly help me. Thank you.
Hello Jeffry
I think this is related to "Problem with IF function".
You can use a mix of Ingeborg's and my solution.
Insert an additional column after "Duration" with this formula and copy it down.
=LOOKUP(I3,{0,10,56.0001},{"Hot","Warm","Cold"})In the same column you have to write the types:
K12: Cold
K13: Warm
K14: Hot
And put this formula in C12 and copy it down and to the right.
=COUNTIFS($A$3:$A$5,C$11,$K$3:$K$5,$K12)
Hello Jeffry
I think this is related to "Problem with IF function".
You can use a mix of Ingeborg's and my solution.
Insert an additional column after "Duration" with this formula and copy it down.
=LOOKUP(I3,{0,10,56.0001},{"Hot","Warm","Cold"})In the same column you have to write the types:
K12: Cold
K13: Warm
K14: Hot
And put this formula in C12 and copy it down and to the right.
=COUNTIFS($A$3:$A$5,C$11,$K$3:$K$5,$K12)
Dear Mr. Detlef,
That was very helpful. Hope you could help me to resolve one final hurdle with the spreadsheet.
Everyday, new data will be keyed in into the row 3 to 5. Meaning, the engineers will delete yesterday's data and key in today's data.
But the accumulated start-up table (row 12-14) should not reset when the data in the top table is deleted and new data is keyed in. Because we can only measure the total start-up if the bottom table adds up all the start-up.
Hope you can help me, sir.
Thank you.
P.S. I have attached the edited file as per your instruction.
This can't be done with formulas. May be it can be done with VBA.
But you better should consider redesigning your setup and put everything in a big table - and especially without any merged cells.
See the uploaded file.
