how to analyse a total

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

I am sure I used a tool or formula a few years back for this.

I have a list of say 25 items. (column A)
In the next column (Column B) there is an amount against each item.
eg bread £5

I am looking for something that will give me the breakdown if I have a total
of say £195 as to how that total was achieved.(from what items)
 
I have a list of say 25 items. (column A)
In the next column (Column B) there is an amount against
each item. eg bread 5
I am looking for something that will give me the breakdown
if I have a total of say 195 as to how that total was
achieved.(from what items)
Click to expand...

If you put the list of unique items (bread, milk, etc) into column C,
say starting in C2, and if the data is in A2:A26, put the following
into D2 and copy down as needed:

=SUMIF(A2:A26,C2,B2:B26)
 
I have a list of say 25 items. (column A)
In the next column (Column B) there is an amount against
each item. eg bread 5
I am looking for something that will give me the breakdown
if I have a total of say 195 as to how that total was
achieved.(from what items)
Click to expand...

If you put the list of unique items (bread, milk, etc) into column C,
say starting in C2, and if the data is in A2:A26, put the following
into D2 and copy down as needed:

=SUMIF(A2:A26,C2,B2:B26)

I am probably not explaining this properly although I will try the above and
see if it works.
It may well have been an `add in` type programme that did this for me in the
past.

In the previous one I put the total in and it showed me the values (£s) as
to how it arrived at that total.

eg I would type in say £101 and it would show the individual amounts that
would add to £101.

My apologies for being vague about this but it was a few years back I used
this.
 
If you put the list of unique items (bread, milk, etc) into column C,
say starting in C2, and if the data is in A2:A26, put the following
into D2 and copy down as needed:

=SUMIF(A2:A26,C2,B2:B26)

I am probably not explaining this properly although I will try the above and
see if it works.
It may well have been an `add in` type programme that did this for me in the
past.

In the previous one I put the total in and it showed me the values ( s) as
to how it arrived at that total.

eg I would type in say 101 and it would show the individual amounts that
would add to 101.

My apologies for being vague about this but it was a few years back I used
this.
Click to expand...

Sounds like a school problem.... You'll have to do all
possibilities. Just a lot of loops. Most likely why you were asked.

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
In the previous one I put the total in and it showed me
the values ( s) as to how it arrived at that total.
Click to expand...

Sorry. I completely misunderstood your requirements.

Take a look at
http://www.sulprobil.com/html/accounts_receivable_problem.html .

Even though it is described as an Accounts Receivable problem, it
really is a solution to the general problem of determining which
subset of a list of numbers sum to a specified total.

Hope you can figure out how to use Bernd's implementation, really
attributed to Michael Schwimmer.

There is also a way to use Solver to generate a solution. But I
believe Schwimmer/Bernd's algorithm is faster and more reliable.

But be forewarned: there is no guarantee of a soluion with any
algorithm, and any algorithm might take an inordinate amount of time
to find a solution. In theory, an algorithm might need to consider
2^n combinations, where n is the number of individual values to be
summed -- 25, in your case.
 
In the previous one I put the total in and it showed me
the values ( s) as to how it arrived at that total.
Click to expand...

Sorry. I completely misunderstood your requirements.

Take a look at
http://www.sulprobil.com/html/accounts_receivable_problem.html .

Even though it is described as an Accounts Receivable problem, it
really is a solution to the general problem of determining which
subset of a list of numbers sum to a specified total.

Hope you can figure out how to use Bernd's implementation, really
attributed to Michael Schwimmer.

There is also a way to use Solver to generate a solution. But I
believe Schwimmer/Bernd's algorithm is faster and more reliable.

But be forewarned: there is no guarantee of a soluion with any
algorithm, and any algorithm might take an inordinate amount of time
to find a solution. In theory, an algorithm might need to consider
2^n combinations, where n is the number of individual values to be
summed -- 25, in your case.


Thanks all thats exactly what I am looking for, that will do the job.
I am sure last time though it was part of the excel `add-in` package.
No matter though this will do as its a one off thing.

The last time I used similar it was indeed several invoices of differing
amounts and I had a payment but could not work it out.
It took just a few seconds for the programme (then) to do it.

Thanks again guys
 
Back
Top