Forum Discussion

Pandiyarajgnanasekar's avatar
Pandiyarajgnanasekar
Copper Contributor
Mar 24, 2021

Calculation of data that occurs one after each entries

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...
Formulas and Functions
tauqeeracma's avatar
tauqeeracma
Iron Contributor
Mar 24, 2021

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

Pandiyarajgnanasekar's avatar
Pandiyarajgnanasekar
Copper Contributor
Mar 24, 2021

Hellotauqeeracma , 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.

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Mar 25, 2021

    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

    • Pandiyarajgnanasekar's avatar
      Pandiyarajgnanasekar
      Copper Contributor
      Mar 25, 2021

      Hellotauqeeracma ,

       

      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.

Resources