Forum Discussion

trevormorris's avatar
trevormorris
Copper Contributor
May 12, 2020

output a sum for variable precise limit

Dear All

 

Not sure if this can be done, but if someone has an idea, it would be very helpful.

I have downloaded a years data from temperature probe into excel, what i want to do is find a total time when the temperature is greater than 25C, but only for that time period and not the whole sheet. 

So the output summation is limited to precise time periods, therefore i will end up with multiple total time periods in a different column.

I have attached a spread sheet, i have shorten the data to a couple of periods when running above and below 25C. The first sheet is the raw data down load and the second sheet is what i am trying to get to (no formulas).

Can get a sheet nearly their but would have to manualy do this final calc.

Any ideas would be very helpful.

Trevor

 

Formulas and Functions

8 Replies

  • ShishirKumar's avatar
    ShishirKumar
    Brass Contributor
    May 13, 2020

    trevormorris 

    You have define the set of rows with unique identifier in next column "F" such as number 1,2,3... So for ex. Row 54-172 can be tagged as 1 and then 200-214 as 2 and so on.

    After this use COUNTIFS($E$1:$E$576,">25",$F$1:$F$576,1)

    Same thing can be used for another set of rows having tag as 2 and so on. Make sure range is selected overall on all rows.

  • bhushan_z's avatar
    bhushan_z
    Iron Contributor
    May 12, 2020

    trevormorris 

     

    If I understood ur problem correctly, u can use COUNTIF or COUNTIFS function.
    for example in 1st sheet (All Data Points) u can use below formula

    =COUNTIFS($D$1:$D$576,">25")
    • trevormorris's avatar
      trevormorris
      Copper Contributor
      May 13, 2020

      bhushan_z 

       

      These function would work, but would only give me the total of the whole sheet (have ~49K rows). What i want is the sum for the individual totals over 25. So want a sum output for rows 54-172, then the next one in this example would be rows 200-214. As you see the number of rows per occurrence above 25 is variable.

      Once these output have been achieved, can then filter sheet to get only the outputs required.

      Hopefully this explains better

       

      Trevor

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 13, 2020

        trevormorris 

        Another variant, if without dynamic arrays and Power Query, add helper column as here

        in D2 0 or 1 depends on condition, in D3

        =IF(D3<=25,0,IF(D2>25,E2,MAX($E$2:$E2)+1))

        and drag it down till end of the range, countif ranges after that as on the right.

        Difference with your sample since it's not clear you count all >25 or all >=25.

         

Resources