Sum of value * qty based on a determined figure

[AkelaWolf76]

'=sum of qty*value but only up to X qty

I have a qty column and a value column and an trying to find the total value of a section of this based on a given requested qty

 

[hagia_sofia]

Hello, what is supposed to happen when there is remainder - e.g. the target quantity is 13 but the running totals are 12 - 15?

 

[Joe4]

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: Sum of value * qty based on a determined figure
There is no need to repeat the link(s) provided above but if you have posted the question at other 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.

 

[AkelaWolf76]

Hello, what is supposed to happen when there is remainder - e.g. the target quantity is 13 but the running totals are 12 - 15?

if I am looking to return on qty "13" the values of 12-15 would need to based on the value of where 13 lands
eg if I require 5 then the return should be £500 but if I require 11, it should return £1400, 15 should be £2500 etc

 

[AkelaWolf76]

I have attached an example here: Sum of value * qty based on a determined figure

 

[hagia_sofia]

Hello again, many thanks for the clarification. Please test the following:

=LET(
n,A3,
q,A5:A17,
v,B5:B17,
a,MAKEARRAY(ROWS(q),MAX(q),LAMBDA(r,c,IF(c<=CHOOSEROWS(q,r),CHOOSEROWS(v,r),NA()))),
SUM(TAKE(TOCOL(a,2),n)))

 

[AkelaWolf76]

Hello again, many thanks for the clarification. Please test the following:

=LET(
n,A3,
q,A5:A17,
v,B5:B17,
a,MAKEARRAY(ROWS(q),MAX(q),LAMBDA(r,c,IF(c<=CHOOSEROWS(q,r),CHOOSEROWS(v,r),NA()))),
SUM(TAKE(TOCOL(a,2),n)))

That has worked perfectly. Many thanks