Calculation of data that occurs one after each entries

[Pandiyarajgnanasekar]

Hello everyone,

Currently I am trying to figure out a way to calculate the sum value from the PLC. In the below example, I try to explain the situation,

From the above table, I need to Sum up the first set of numbers (rows 3 to 4) in to a cell. Then, the sum next set of numbers (rows 10 to 12) and then next set and so on..... I am not sure which function, I can use to calculate such thing. Also, I have to make the occurance of each set of zeros like, Stop1, stop2, stop3, and so on for each respective dates.

Does anyone know, some way to solve this. Any help will be appreciated. Thank you.

[#2]

Hi @Pandiyarajgnanasekar 

As per my understanding you need to sum rows but with constant intervals. If this is the case you may use Offset() functions as mentioned below:

=SUM(OFFSET($G$1,(2+7*(ROW()-ROW($I$2))),0,3,1))

A sample file is also attached for your reference. Your second requirement needs more clarification.

Thanks

Tauqeer

[#3]

Hello@tauqeeracma , Thank you so much for the response.

Let me explain the requirement with the table below,

Consider a machine that is initially at stop (stop1) for some time (15 mins - 6:00 to 6:15 min). Then, it starts producing some materials for some time (Production1)(The time data will be like time logs of its running) and then it stops for some time (stop 2), like that we will have data intermittently. My requirement is sum up the time and quantity production when it runs but representing or describing each stop and running state. I hope you understand the requirement.

If  not, let me know, I will try to explain more. Thank you.

[#4]

Hi @Pandiyarajgnanasekar 

With the help of below formula and along with solver / helper columns you can achieve your first requirement (i.e. Production quantity).

 =IFERROR(IF(LEFT(E3)="P",INDEX(P:P,MATCH(F3,Q:Q,0),1),0),0)

Please refer the attached file for more clarity.

For second requirement please clarify, difference of which cells you want to take for Production as well as Stop Time.

My understanding is that for Production_1 you need difference of C9 & C7. For Production_2 difference of C14 and C11 and so on.

Thanks

[#5]

Hello@tauqeeracma ,

Its great to see that you solved it very simply. I admire your thinking. Thank you.

The second requirement is simple from now, You already calculated the Quantity for production, now we need to find the time required for each production and each stops. The time with respect to zeros are stop time and the time with respect to number >0 are considered as run time. 

For the first production in your example file, production is 14.8, now how much time it took to produce this quantity. Similarly for all the produced quantiy and what is the stop time between each production stage..... like that. I hope you understand.

I really appreaciate your great effort to help me. Thank you so much.

[#6]

@Pandiyarajgnanasekar 

Hello Pandiyarajgnanasekar,

Based on your explanation the second requirement is also solved in the attached file.

You will also find formula explanation about how "Time Consumed" is calculated. 

Hope it works as desired.

Thanks

Tauqeer