Running LET() function as a loop

[molylyly]

I have a long LET() function where I define a number of variable but with one of them being a range. Subsequently, I have a number of calculations which depend on the range but I want to make sure that all of the calculations are run fully with the first number in the range before the calculations are done again with the second number in the range. The formula works when calculating with a single number but not with the range. How can I solve this? (I don't have access to LAMBDA, SCAN etc. due to semi annual channel)

Example:
Range {1,3,5}
when range=1, result=20
when range=3, result=50
when range=5, result=26

So I want the result of the formula being {20,50,26}, how do I ensure this?

Formula looks like this where I would like to run a loop for the second part based on the range from "peaks_position"


=LET(
Deficit_period,E26,
Storage_beginning,H26,
Price_period,F26,
Hours,$D$6,
deficit,OFFSET(Deficit_period,,,Hours),
prices,OFFSET(Price_period,,,Hours),
rows,SEQUENCE(Hours),
peaks_position,FILTER(rows,(deficit>0)*(OFFSET(deficit,1,0)<=0)),


rows_peak_position,SEQUENCE(ROWS(peaks_position)),
no_rows_peak_position,SEQUENCE(peaks_position),
rows_offset,rows-1,
table_peaks_position,IF(rows,TRANSPOSE(peaks_position)),
table_pp_na,table_peaks_position-rows_offset,
table_peaks_position_relative,IFERROR(IF(table_pp_na<0,0,table_pp_na),0),
storage_required,IFERROR(SUBTOTAL(9,OFFSET(Deficit_period,rows_offset,,table_peaks_position_relative)),0),
binary_table_pp_relative,IF(table_peaks_position_relative>0,1,0),
deficit_to_peak,IF(rows_peak_position,OFFSET(Deficit_period,,,peaks_position,)),
deficit_to_peak_table,INDEX(IF(binary_table_pp_relative>0,deficit_to_peak,0),no_rows_peak_position),
prices_to_peak,IF(rows_peak_position,OFFSET(Price_period,,,peaks_position,)),
prices_to_peak_table,INDEX(IF(binary_table_pp_relative>0,prices_to_peak,0),no_rows_peak_position),
Deficit_prices_table,IFERROR(INDEX(SORTBY(CHOOSE({1,2},deficit_to_peak_table,prices_to_peak_table),prices_to_peak_table,1,deficit_to_peak_table,-1),no_rows_peak_position,{1,2}),"N/A"),
storage_required_peak,INDEX(storage_required,no_rows_peak_position),
Deficit_sorted_by_p,INDEX(Deficit_prices_table,0,1),
P_min,INDEX(Deficit_prices_table,0,2),
***_deficit,MMULT(IF(SEQUENCE(ROWS(Deficit_sorted_by_p))>=TRANSPOSE(SEQUENCE(ROWS(Deficit_sorted_by_p)))=TRUE,1,0),Deficit_sorted_by_p),
P_min_no,IFERROR(XMATCH(-storage_required_peak,***_deficit,-1),XMATCH(SMALL(***_deficit,1),***_deficit,-1)),
price_marginal,INDEX(P_min,P_min_no),
To_storage,MIN(MAX(IF(price_marginal>=Price_period,storage_required_peak,0))+SUM(IF(prices_to_peak_table<Price_period,deficit_to_peak,0))-Storage_beginning,-Deficit_period),
output,IF(To_storage<0,0,To_storage),

output
)

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Running LET() function as a loop
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[molylyly]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Running LET() function as a loop
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies, cross-posts here and here

 

[Peter_SSs]

Thanks for adding the cross-post links.

Something to consider for the future: Cross-posting is tempting as it seems like a quick/good answer is more likely. However, the opposite may be true as it is common that many of the experienced helpers in any forum don't want to spend time developing a possible solution if one might have already been provided at another forum. It may be that the potential helpers are not members at the other forums and/or may not want to continually check the other forums to see what developments have occurred.