Sum Cells from Low to High and Pro rata the Last Value

[andybason]

Hi all,

It is difficult to summarise what I am trying to achieve the in the subject so I'll explain here:

In column B I insert values from 1-11.

This brings up values in columns C and D. I want to sum the values in column D based on the order in column B (from lowest to highest) stopping when the sum of the values in column C exceeds 24. When they exceed 24 the last value (based on the values in column B) in column D should be prorated.

So in this xl2bb it should be 25.1996 + 12.7992 + (12.00015 pro rated). The pro rated element here is 24 - (14.8 + 8.4).

I hope that makes sense. If not please ask!

Thanks

MOvLS.xlsm
	A	B	C	D
7	Player	toBat	expBs	expRuns
8	Sean Abbott
9	Colin Ackermann
10	Jos Buttler
11	Wanindu Hasaranga de Silva	2	8.4	12.79992
12	Laurie Evans
13	Richard Gleeson	3	13.5	12.00015
14	Calvin Harrison
15	Tom Hartley
16	Fred Klaassen
17	Tom Lammonby
18	Wayne Madsen	1	14.8	25.19996
19	Jamie Overton
20	Matt Parkinson
21	Ollie Robinson
22	Andre Russell
23	Phil Salt
24	Ashton Turner
25	Daniel Worrall
Chase Model

Cell Formulas
Range	Formula
C8:C25	C8	=IF(B8="","",INDIRECT("'"&A8&"'!N74"))
D8:D25	D8	=IF(B8="","",(C8*(VLOOKUP(A8,Summary!B10:V34,21,FALSE)/100)))
A8:A25	A8	=IF(Summary!B10="","",Summary!B10)

 

[SanjayGMusafir]

Just one clarification, once you have assigned order 1-11 in column B, Can we sort data manually on the basis of Column B?

 

[SanjayGMusafir]

Just one clarification, once you have assigned order 1-11 in column B, Can we sort data manually on the basis of Column B?

Also need to understand how the Pro Rata is calculated - You sample data only shows VLookups