Posts by kant

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Conditionally sum values at each interval of change in other cell

    Re: Conditionally sum values at each interval of change in other cell


    Thanks rbrhodes,


    Unfortunately, your post still does not answer my question: "SUMIF" gives the total # of strawberries that John collected during the entire period, while I need the two partial sums he collected during the two working shifts in the example.


    Thanks for helping


    Kant

  • Conditionally sum values at each interval of change in other cell

    Re: RANGE Until VALUE"XXX" Changes to "YYY"


    Hello again...


    I'll try to explain this in another way (as everybody can see English is not my first language...)


    John is a simple robot-farmer. He performs just two tasks: working and sleeping.
    When John is awake he picks up strawberries in his field. When he's asleep the wild birds eat them.
    John gets tired quite randomly: sometimes he can work for 14 hours in a row or more, sometimes he falls asleep after one or two hours.


    In column A you find the number of strawberries picked up by John (when he's awake) or eaten by the birds (when John's asleep).
    Column B shows when John is awake.


    A B
    133 sleepin'
    234 sleepin'
    342 awake
    571 awake
    345 awake
    343 sleepin'
    234 sleepin'
    564 sleepin'
    323 sleepin'
    234 awake
    113 awake
    123 sleepin'
    ...
    (in this example: during the last 12 hours, John slept for two hours, then he worked for 3 hours and fell asleep again for 4. He woke up and worked for two more hours, and then fall asleep again).


    John needs a formula to tell him (every time that he falls asleep) how many strawberries he picked since he's last awakening.


    (in the example, the formula should give 342+571+345 when John falls asleep the first time, and 234+113 when John falls asleep the second time.)


    Please help!


    Thanks a lot!


    kant (and John)

  • Conditionally sum values at each interval of change in other cell

    Hello,


    I'm working on a sheet of this type:


    A1 B1 C1
    Value Signal Switch
    123 Off
    132 Off
    144 Off
    123 On On
    134 On
    165 On
    345 On
    345 Off Off
    765 Off
    342 Off
    123 Off
    133 On On
    134 On
    177 On
    132 Off Off
    145 Off
    ...

    Along the sheet, column B switches several times from "On" to "Off". I did isolate the signal getting column C ("Switch"). I need to perform some simple operations (as AVERAGE, MIN, MAX, etc) on each "strips" marked by "ons" on column B, that is, I need a formula that applies to values in column A within "On" and "Off" in column C.


    For Instance, I need the minimum for each strips (that is 123 for the first strip; and 133 for the second one)


    I hope I made myself clear.


    Thanks a Lot


    Kant