Excel Help?
mrb82
Registered Posts: 147 Beyond epic contributor 🧙♂️
Hi MIPs!
Do any of you know of a fomula that would look at a list of values (in this case currency) and calculate which of these values will make a given figure?
For example, I receive, say £1,500, from a debtor, but this figure is made up of many smaller varying figures relating to various invoices. I ant to be able to input the received amount that I want to make up, and get the formula to look at a column with the invoice values and see which of these values will make the total I'm looking for.
Does this make sense, should I post this to the student area?
Many thanks if you can help!
Do any of you know of a fomula that would look at a list of values (in this case currency) and calculate which of these values will make a given figure?
For example, I receive, say £1,500, from a debtor, but this figure is made up of many smaller varying figures relating to various invoices. I ant to be able to input the received amount that I want to make up, and get the formula to look at a column with the invoice values and see which of these values will make the total I'm looking for.
Does this make sense, should I post this to the student area?
Many thanks if you can help!
0
Comments
-
I don't know how to write a formula to do this, but what I do when i'm trying to match payments with multiple invoices is list all the invoices in one column in excel and then in the next column enter the payment as a negative number. then click on the payment, hold down the ctrl key and click on the invoices which you think it could relate to. If you look at the bottom ribbon towards the right you'll see the sum of the highlighted cells so keep clicking on invoices until the sum is nil. You can obviously keep trying different combinations until you get it to zero, and if you've got serveral payments to match up, once I've got one matched up I colour the cells of the payment and invoices one colour so I know not to use them again. Hope this helps. On some older versions of excel it may not automatically show the sum on the bottom ribbon but if you right click on it there's usually an option to turn it on.
Jodie0 -
Don't think there is a formula for this (or it'll be very complicated), can you just not as your customers to drop you a note of the invoices they are paying? Or ask them to submit remittance advices?0
-
-
And where's the fun in that?! I often find its quicker and easier to work it out myself than to request and wait for a remittance advice.
This is true. While they do send a remit, it's not until later on in the day, even if I'm straight on the phone asking for it. My cash team require a breakdown by claim number (invoice number) of the total cash receipt so they can import it on to our system.
I currently go through my list that I know is outstanding and ctrl click combinations and let the autosum show me what's what, but it doesn't always work. It's tricky too when there are ~30 claims (invoices) and they're paying 7!
Thanks for the replies though!0 -
Though I've never personally used it, could Goal Seek possibly cope with this (though the query might even be too complex for that)?0
-
I'll give that a go (on Wednesday, of course!).
(hanks for the suggestion and have a great Christmas!0
Categories
- All Categories
- 1.2K Books to buy and sell
- 2.3K General discussion
- 12.5K For AAT students
- 325 NEW! Qualifications 2022
- 160 General Qualifications 2022 discussion
- 11 AAT Level 2 Certificate in Accounting
- 56 AAT Level 3 Diploma in Accounting
- 95 AAT Level 4 Diploma in Professional Accounting
- 8.9K For accounting professionals
- 23 coronavirus (Covid-19)
- 273 VAT
- 92 Software
- 274 Tax
- 138 Bookkeeping
- 7.2K General accounting discussion
- 202 AAT member discussion
- 3.8K For everyone
- 38 AAT news and announcements
- 345 Feedback for AAT
- 2.8K Chat and off-topic discussion
- 582 Job postings
- 16 Who can benefit from AAT?
- 36 Where can AAT take me?
- 42 Getting started with AAT
- 26 Finding an AAT training provider
- 48 Distance learning and other ways to study AAT
- 25 Apprenticeships
- 66 AAT membership