Running LET() function as a loop

[mlylyly]

Hi community,

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?

[#2]

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),
cum_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,cum_deficit,-1),XMATCH(SMALL(cum_deficit,1),cum_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
)