• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumation Formula with Find Feature

E

excel_noobie

New Member
Say I have a list of invoices, and the client says to use a maximum dollar amount to apply to their AR. I want to select the invoices that will reach the dollar amount to exhaust.


Say I have 100 Invoices with various amounts, and the client sends in $50,000.00 and said apply it to whatever invoices but we can only use 50,000.00 is it then possible to create a formula to select the invoices that will total or as close to 50,000?
 
Hi excel_noobie,


Welcome to the forums!!!


Please see this solution, i will try to come up with a compactor one:


http://dl.dropbox.com/u/60644346/Charging%20Invoices_Largest%20Amount.xlsx


Regards,
 
Hi Faseeh!! =_)


I like it, but somehow you built the function to pick the top ones, but the idea is match to the total amount the customer sends. so if it was 40,000 they paid, then you could have 9000+9000+9000+8000+5000 and that uses the 40,000 up.
 
Hi ,


This is not an easy problem !


A similar question has been asked earlier , and you'll find there a link to a VBA procedure written by Tushar Mehta to solve this problem. Check if it helps :


http://chandoo.org/forums/topic/need-help-macro-for-combination-of-number-that-summatch-to-a-value


Narayan
 
hi,


this is not good but at least you can see something.

A1 : Target Invoice Value


1. pivot the invoice, then sort from largest to smallest (B1 : C10 for example)

2. in D1 =floor($A$1/B1,1) then drag down

3. in E1 = = B1*D1 then drag down

4. in F1 = $A$1-E1 then drag down


at least you can see how many invoice :)


http://speedy.sh/TKWaS/Copy-of-Charging-Invoices-Largest-Amount.xlsx
 
You must log in or register to reply here.
Back
Top