sum until I reach desired quantity

[Guest]

I would like to make a formula (or formulas) that would sum a column until it
gets to my desired amount starting with the last row, and working towards the
top of the spreadsheet.

Example:

Line Qty Amount Price/unit
A 2 200 100.00
B 3 350 116.67
C 5 450 90.00
D 1 105 105.00
Total 11 1105

Desired 7 671.67

So you would have all of the cost for D and C, and only the cost of B that
you needed, in this case it is 1.

Thanks for the help in advance.

 

[kcc]

You don't actually say what you want, but this will give you
something to work with. (I'm assuming you care about the
671.67 rather than the 7.)
Assuming your data is in A17,
in E1 put =MIN(INT(($C$7-F2)/D1),B1) and copy down,
in F1 put =E1*D1+F2 and copy down.
E will have the count of each item.
F will have the total summing from the bottom.
You should also try it with a total of 671.66 and 671.68 to
see if it produces the desired results.
kcc

 

[Domenic]

Assuming the following...

Row 1 contains the headers/labels

A25 contains the data

Row 6 contains the totals

Define the following names...

Insert > Name > Define

Name: Amount

Refers to:

=SUBTOTAL(9,OFFSET(Sheet2!$C$2:$C$5,ROWS(Sheet2!$C$2:$C$5)-(ROW(Sheet2!$C
$2:$C$5)-ROW(Sheet2!$C$2)+1),0,1))

Click Add

Name: CumQty

Refers to:

=SUBTOTAL(9,OFFSET(Sheet2!$B$2:$B$5,ROWS(Sheet2!$B$2:$B$5)-1,0,-(ROW(Shee
t2!$B$2:$B$5)-ROW(Sheet2!$B$2)+1)))

Click Add

Name: Quantity

Refers to:

=SUBTOTAL(9,OFFSET(Sheet2!$B$2:$B$5,ROWS(Sheet2!$B$2:$B$5)-(ROW(Sheet2!$B
$2:$B$5)-ROW(Sheet2!$B$2)+1),0,1))

Click Ok

Then try the following formula...

=IF(F2<=SUM(Quantity),SUMPRODUCT(--(F2>=CumQty),Amount)+IF(F2<SUM(Quantit
y),LOOKUP(0,CumQty-F2-Quantity,(F2-(CumQty-Quantity))/Quantity)*LOOKUP(0,
CumQty-F2-Quantity,Amount)),"Quantity Exceeds Total")

....where F2 contains the quantity of interest, such as 7.

Hope this helps!

 

[kletcho]

A couple of options depending on what you are trying to accomplish.

1. You could either use a circular reference and then allow iterations
(tools--options--calculations)
2. You can use the built in solver depending on how complicated your
criteria are (you need to make sure the solver add-in is checked before
this will show up under tools.)
3. If you have simple criteria then you can use goal seek (tools - goal
seek)

 

[Domenic]

Maybe...

=SUMPRODUCT(--(F2>{0,1,6,9}),F2-{0,1,6,9},{105,-15,26.67,-16.67})

....where F2 contains the quantity of interest, such as 7.

Hope this helps!