-1

I'm new to stackoverflow and was looking for some help.

I'm building an Excel formula (using LET) to enforce a minimum run length for a CHP engine. The logic is:

  • If today's suggested load (row 8) > 0 → output that value.
  • If today's suggested load = 0 → look back 11 previous values plus the current one (12 total), spanning both the previous day (row 5) and the current day (row 8).
  • If all 12 are zero → output 0 (machine is off).
  • Otherwise → output 0.5 (machine is still in its minimum run).

I have been struggling and enlisted AI...I know.

Rows 5 and 8 have been pre calculated. This logic is an extract from a wider model of an engine. Values in rows 5 and 8 are the amount an engine is running. Each column is an hour. The engine can only throttle down to 50% before turning off.

Row 5 is the previous day and row 8 is the current day. Looking back is applied because I want the engine to run for a minimum of 12 hours. So the first values of the current day will need to reference the previous day.

Each value is an hour so if the engine is running at 70% 70% 60% 50% 0% 0% 50% 50%…I would want the two 0% to change to 50%. This is to satisfy the minimum run time requirement of 12 hours. If there were 13 0% then the 13th one wouldn’t be 50% it would be 0% as it would be after 12 hours of running at the minimum load. However if the engine has already been running for over 12 hours and there is any amount of 0% after, that is fine because the previous 12 values (hours) are running which meets the minimum run time requirement. But if after a 0% there was a 50% followed by a 0%, the 0% after the 50% needs to change to 50% because the minimum run time has been triggered.

I have modified rows 5 and 8 to stress test the formula because the model in which this applies the values could be any combination of 0% to 100% for many days. So the current formula is not looking at the previous day correctly.

I gave the example of some previous day values of 0% 0% 0% now corrected to 0% 50% 0%. Because of the 50% on the previous day, the 0% values on the current day should be adjusted by the formula to 50% since the minimum run time should be triggered. If the last 3 values of the previous day were 0% 0% 0% then it is correct.

I need a formula that dynamically includes the last 11 values from the previous day + current cell, with minimal helper rows. This formula will apply to multiple days for a year with duplicate sheets to test different outcomes so I would prefer not to use volatile formulas to improve performance.

The current working formula (with no helper rows) is:

=LET(
  lookback,11,
  prevDay,$E$5:$AG$5,
  currDay,$E$8:$AG$8,
  allData,HSTACK(prevDay,currDay),
  prevCols,COLUMNS(prevDay),
  pos,prevCols+COLUMNS($E9:E9),
  above,E8,

  currSoFar,INDEX(currDay,SEQUENCE(1,COLUMNS($E9:E9))),
  anyPosToday,SUM(--(currSoFar>0))>0,

  lastPosIdxToday,IFERROR(LOOKUP(2,1/(currSoFar>0),SEQUENCE(,COLUMNS($E9:E9))),NA()),
  lastPosAbs,IFERROR(prevCols+lastPosIdxToday,NA()),

  zerosSinceStartLen,IF(ISNA(lastPosAbs),0,MIN(lookback, pos-lastPosAbs)),
  zerosSinceStart,IF(
    zerosSinceStartLen>0,
    INDEX(allData,1,SEQUENCE(1,zerosSinceStartLen,lastPosAbs+1)),
    ""
  ),
  zeroCount,SUM(--(zerosSinceStart=0)),

  IF(above>0,
     above,
     IF(NOT(anyPosToday),
        0,
        IF(zeroCount<lookback,0.5,0)
     )
  )
)

Previous day (E5:AG5, 29 values):

97%  98%  96%  96%  95%  96%  94%  94%  96%  96%  97%  99%  100%  100%  100%  100%  100%  100%  100%  100%  76%  77%  70%  69%  64%  63%  0%  50%  0%

Current day (E8:AG8, 29 values):

0%  0%  0%  70%  60%  0%  0%  0%  0%  0%  0%  0%  0%  0%  0%  0%  0%  0%  0%  60%  80%  77%  77%  77%  0%  0%  100%  100%  100%

Expected current day altered output:

0  0  0  0.7  0.6  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0  0  0  0  0.6  0.8  0.77  0.77  0.77  0.5  0.5  1  1  1

Actual result:

0.5  0.5  0.5  0.7  0.6  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0.5  0  0  0  0.6  0.8  0.77  0.77  0.77  0.5  0.5  1  1  1
Improve this question
3
  • @user22566114 I have edited the question. I gave the example of some previous day values of 0% 0% 0% now corrected to 0% 50% 0%. Because of the 50% on the previous day, the 0% values on the current day should be adjusted by the formula to 50% since the minimum run time should be triggered. If the last 3 values of the previous day were 0% 0% 0% then it is correct. Commented Oct 27 at 1:09
  • I've tried several times to find a solution for this task and unfortunately i can't help you with this. Perhaps an expert of the community can provide a solution that delivers the expected output. I'm very interested in reading and understanding and learning from this solution as well. Commented Oct 29 at 15:26
  • @user22566114 the solution is: =LET( lookBack, 12, prevDay, E5:AG5, prevDayOutput, E6:AG6, currDay,E8:AG8, output, DROP(REDUCE(prevDayOutput, SEQUENCE(,COLUMNS(currDay)), LAMBDA(x,y, HSTACK(x, IF(INDEX(currDay, y), INDEX(currDay, y), LET( v, TAKE(DROP(HSTACK(prevDay, TAKE(currDay,, y)),,-1),,-lookBack), out, IF(OR(AND(v>0), TAKE(x,,-1)=0, AND(v=0), AND(TAKE(x,,-lookBack)>0)), 0, 0.5), out ) )) )),,COLUMNS(prevDayOutput)), output) Commented Nov 3 at 23:13

2 Answers 2

Reset to default
1

I don't completely understand what you want to do but your formula returns exactly the expected output in my Excel online sample sheet unless i'm doing something wrong.

excel-engine

=LET(
  lookback,11,
  prevDay,$E$5:$AG$5,
  currDay,$E$8:$AG$8,
  allData,HSTACK(prevDay,currDay),
  prevCols,COLUMNS(prevDay),
  pos,prevCols+COLUMNS($E9:E9),
  above,E8,
  currSoFar,INDEX(currDay,SEQUENCE(1,COLUMNS($E9:E9))),
  anyPosToday,SUM(--(currSoFar>0))>0,
  lastPosIdxToday,IFERROR(LOOKUP(2,1/(currSoFar>0),SEQUENCE(,COLUMNS($E9:E9))),NA()),
  lastPosAbs,IFERROR(prevCols+lastPosIdxToday,NA()),
  zerosSinceStartLen,IF(ISNA(lastPosAbs),0,MIN(lookback, pos-lastPosAbs)),
  zerosSinceStart,IF(
    zerosSinceStartLen>0,
    INDEX(allData,1,SEQUENCE(1,zerosSinceStartLen,lastPosAbs+1)),
    ""
  ),
  zeroCount,SUM(--(zerosSinceStart=0)),

  IF(above>0,
     above,
     IF(NOT(anyPosToday),
        0,
        IF(zeroCount<lookback,0.5,0)
     )
  )
)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
=LET(
lookBack, 12,
prevDay, E5:AG5,
prevDayOutput, E6:AG6,
currDay,E8:AG8,
output, DROP(REDUCE(prevDayOutput, SEQUENCE(,COLUMNS(currDay)), LAMBDA(x,y,
    HSTACK(x, IF(INDEX(currDay, y), INDEX(currDay, y),
    LET(
    v, TAKE(DROP(HSTACK(prevDay, TAKE(currDay,, y)),,-1),,-lookBack),
    out, IF(OR(AND(v>0), TAKE(x,,-1)=0, AND(v=0), AND(TAKE(x,,-lookBack)>0)), 0, 0.5),
    out
    )
    ))
)),,COLUMNS(prevDayOutput)),
output)
Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.