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

 

 

Formulas and Functions

5 Replies

  • 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

Resources