Question

How to find which numbers make-up a total in Excel

Asked by: maianoel

If I have a series of numbers in a column in Excel, and I have a total amout is there any program or method to figure out which numbers will add up to that total?  In this case the total would not be a total of the whole column rather a total of only certain numbers in the column.  I thought I had done that back on college in statistics but can not remember how to do it now.  I'm using Office 2007 currently.

Thanks for he help!

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-04-15 at 07:23:18ID24324304
Tags

Excel 2007

,

Excel 2003

Topic

Microsoft Excel Spreadsheet Software

Participating Experts
2
Points
500
Comments
16

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Excel Column Totals
    I'm sure this must be easy, so not to run out of points I'm only offering 20!! I have an Excel sheet which now (thanks to Ferg!) contains 136 columns of data (all differing lengths). Along row 100 I want to detail the total count of cells in each alternate column (ie column ...
  2. Excel 2k - Totaling Checkboxes in a specific column...
    Greetings: The problem: I have an excel sheet that has checkboxes in 2 columns, one denotes if a person is a member of group A, the other if a member of group B. A particular person can be a member of either, both, or none. I am trying to find a way to count the total nu...
  3. Statistical Sampling in Excel
    I would like to know how to do statistical sampling in Excel. I have a about 4000 data points. I would like to determine of that population a statistically significant sample size, and then a random sampling of that data. For this sampling, I would like to have a 3-5% con...
  4. Excel spreadsheet for college education savings account
    I want to set up a savings account for my daughters college education. Is there a template for a spreadsheet that I can use that shows interest rates, amount saved etc. My sister says there is a function (FV) but I don't know much about that either. Any help with this woul...
  5. Statistical Analysis in Excel XP
    I have imported csv data from an acrobat form into excel xp. The form was sent to dozens of people, and there will be many responses, all to be imported into and analysed in excel. The data are numerical representations of which radio buttons each survey-recipient has select...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: ProdOpsPosted on 2009-04-15 at 07:33:11ID: 24148410

If you click on the Total cell is there a formula that indicates what cells in the column above it that it is using to create the total?

 

by: maianoelPosted on 2009-04-15 at 07:38:37ID: 24148485

I don't have a formula.  Let me state it another way.  I have a total number I am trying to explain and I need to know which items make up that total.  Basically I'm trying to reconcile one of our accounts and the raw data comes from a database.  I know the total I'm looking for just not the specific line items that make up that total.  Unfortunately in this case the dates don't help either... it all has to do with the way the items were entered originally but with hundreds of line items it would take forever to try to figure it out manually.

 

by: ProdOpsPosted on 2009-04-15 at 07:52:13ID: 24148668

OK - I understand...  One of the VBA gurus may be able to write a routine that can determine which grouping of cells will make up the total that you have but I am guessing there will be many possible solutions using the column of numbers.  This one is outside my VBA experience.  Be patient and someone will pick this one up that has more VBA experience.

 

by: maianoelPosted on 2009-04-15 at 08:49:04ID: 24149410

Thanks ProdOps.

 

by: felixdsouzaPosted on 2009-04-16 at 01:19:10ID: 24155402

Check out the VBA macro in the attached file. It has sample data in it.  You only need to run the macro.

 

by: maianoelPosted on 2009-04-16 at 11:05:44ID: 24160508

OK, so this seemed to be exactly what I was looking for and works great as is but as soon as I add in the actuals I'm trying to work with I get an error on the following line:

TargetTotal = Cells(1, 5) ' Assumption: Target total is in cell E1

I've attached the file with my values in.

Thanks for your help!

 

by: maianoelPosted on 2009-04-16 at 11:43:10ID: 24161100

I think I may have figured it out... the values in my file have decimal places.  I changed two of the declarations from Integer to Currency and it appears to be running now, although it's taking a few minutes.  I'll let you know how this comes out.

 

by: felixdsouzaPosted on 2009-04-16 at 21:58:55ID: 24165009

Hi,

You are right about the cause for the error.  Changing the data types of the total variables to Currency or Double or Long would solve that problem.

You are also right that the macro is going to take forever to run.  That is because you have 81 individual figures, some of which go into making up your total.  This gives rise to a total of 2^81 different permutations and 2^81 different possible totals from the list - one of which you are searching for.  The only way I can see is to do it by brute force which is the approach taken in the macro.  However due to the huge number of permutations  to be tried out, the macro is going to take forever to run.

Now I have modified the macro in the attached file.  This reduces the looping to some extent, resulting in a much faster run.  However, please note that this is also going to take forever to execute !!!

My advice is to execute it on the fastest CPU you have at your disposal.

Next, I have a question for you - Do you know how many of the individual figures make up the total (even though you don't know which ones) ?  If so, that could be used to drastically reduce the run time.

Also, is this a one time exercise, or are you going to be doing this sort of thing regularly ??

 

by: maianoelPosted on 2009-04-17 at 08:09:11ID: 24168577

Thanks, I'll do that.  My home PC is much more powerful than my laptop here at work.  I tried running it on the laptop, which has a dual core CPU, and it ran for almost 4 hours with no result.  I finally stopped it so I could go home.  I'll start it tonight and let it run all weekend if necessary.  Overall this should be a one time even rather than something we'll have to do consistently (I hope)!

Thanks for all your help!!

 

by: maianoelPosted on 2009-04-17 at 08:09:33ID: 31570466

Perfect!!

 

by: felixdsouzaPosted on 2009-04-19 at 21:35:56ID: 24181907

Just in case you are unable to get your solution in a single run because it is taking too long, let me know and I will see if I can modify the macro so that you can stop the run in between and then continue from where you left off the next time you run the macro

 

by: maianoelPosted on 2009-04-20 at 17:48:31ID: 24189963

Yeah, that seems to be a problem.  I started it at work and it ran all day withouta result.  If it's not too difficult to add that I would certainly appreciate it!

 

by: felixdsouzaPosted on 2009-04-22 at 20:48:00ID: 24211682

Hi maianoel,

Sorry I was busy and could not work on this.  Will do so now.  You can expect a solution in a couple of hours from now.

 

by: felixdsouzaPosted on 2009-04-23 at 04:02:54ID: 24213649

Hi,

Sorry again for the delay.  Try out this solution.

First time you run the macro, the value for the IndexValue cell (H1) should be blank.  Every few minutes you will get a prompt whether to continue or stop.  Click OK to continue, Cancel to stop.  If you stop, you will find that the H1 cell has been populated with a value.  Just let it be and save the file.  Next time you run the macro, it will start from where you left off the last time.

Also you will see a variable Counter3 in the code.  At one point you will find the code
If Counter3 Mod 1000 = 0 Then
The value in that statement (1000) controls how often the prompt offering you the chance to stop the macro will appear.  Increase or decrease this value to suit your need.  Note that larger values will cause the prompt to appear after a very long time!!!

 

by: maianoelPosted on 2009-04-30 at 10:17:08ID: 24272186

felixdsouza - I wanted to thank you for all your efforts here.  Sorry for the delay in responding back... I just got busy with some work stuff and didn't get back to this until yesterday.  This seems to work perfectly.  I've changed the counter around a bit and I'll keep plugging away until there is a resolution.

Also, since the second part was really a second item I'd like to open a new question to award additional points for that, if you are OK with it.

Thanks again!

 

by: felixdsouzaPosted on 2009-05-03 at 22:05:32ID: 24292637

Hi maianoel,

Your suggestion of opening a new question and awarding additional points is extremely decent.  However, what really matters to me is that you get an ultimate solution to your problem.  While technically I had provided a correct solution with my first post (which you accepted), the fact of the matter is that your ultimate objective (and the solution to your problem) will only come when you get the actual figures which add up to your target total.

I know that that can take a huge amount to time and a solution may not come out in a single run, hence I was glad to work further on the solution to produce the last solution provided which allows you to break up the job midway, save the file and continue on the next day from where you had left off.

If you really wish, you can open a new question for the second part of the question, so as to award additional points.  However, I would suggest that you indicate in your question itself the exact status as well as that you have already received the answer, so as to avoid wasting other experts time when they attempt to work on your problem.

All the best, and do let me know when you solve your problem.

Felix

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...