learn excel in london - Solver

The UK's most regular instructor-led training courses.
Training information: learn excel in london · Excel course london · Microsoft Excel Training
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version Standard

Solver

Ronak has attended:
Excel Advanced course

by - delegate Ronak [1 post] (2006 Nov 24 Fri, 18:34) Reply

Can I use the solver function to find from a set of accounting transactions the sum of debits or credits that sum up to a particular number. i.e. I have 100 transactions, from those transactions I need to find a combination of entries that sum up to £10,500. Can this be provided by Excel's solver function and can it provide the result with a variety of permutations?

Version	Date	Location	Places available	Book	Next place rate:
Excel VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003/XP	2008 Aug 14 Thu + 15 Fri	Bloomsbury	3	book now	£475	£495
2003/XP	2008 Aug 26 Tue + 27 Wed	Bayswater	4	book now	£475	£485
2003/XP	2008 Sep 8 Mon + 9 Tue	Bayswater	5	book now	£450	£485
2003/XP	2008 Sep 18 Thu + 19 Fri	Bloomsbury	6	book now	£450	£485
2003/XP	2008 Sep 25 Thu + 26 Fri	Southwark	6	book now	£450	£485
2003/XP	2008 Oct 6 Mon + 7 Tue	Bayswater	7	book now	£345	£399
Full Schedule: See all 46 Excel VBA course dates. Bookings currently available until 26th November 2009.

RE: solver

by - trainer Amanda platinum contributer [917 posts] (2006 Nov 28 Tue, 14:53) Edited on 2006 Nov 29 Wed, 16:06 Reply

Hi Ronak

Thanks for your question.

The Solver feature will not be able to do this for you as Solver will change the entries in the cells in order to give you the solution you specify (e.g. total of £10,500).

This may be something you could resolve using VB programming but it would be quite complex to set up.

Amanda

· Tips to Facilitate Printing of Excel worksheets
· Microsoft Excel Training Course
· How Excel Training Can Improve Your Company's Sales Process
· Excel Macros for the Non-Programmer
· Customized Financial Excel Courses in London

Excel tip:

Converting an American date format to European using Formula

Excel depending on your local setting will only pick up date values of the dd mmm yyyy oders as date type. If you import data from various sources including America their date order is different with data value in mmm dd yyyy, excel can only treat it as text indicated by left aligning it. To overcome this you have to do the the following.

1. Extract the date components mmm dd yyyy, by using the the Text functions LEFT, MID or RIGHT

2. Reorder dd mmm component and concatenate using "&" in the right order this will create a text string with the date in the right order it then needss to be converted to a value so excel can recognise it.

3. To convert to value encase in TEXT function.

4. Format to desired date format.

View all Excel hints and tips