Excel: Total of some cells equal to a specific value

[77ssbssb]

Hello everyone,

I have an excel spreadsheet in which there are some random numbers available and I want to find out the specific total.
Let's say I have 4 numbers in colmun 80, 40, 20, 90 in separate rows and I want to find out which rows add to 130.

Any help on this?

PS: I am a beginner in excel 2003.

 

[77ssbssb]

So with 60 entries there might be 100s of combinations that match.

For the final time rep2 needs to be broken down into its components, it must have these components.

I am so Sorry for wasting time of everyone, if I am unable clarify my issue. As, I have mentioned that they will be no similar sums in report one that I am sure off. I just want to know the process of finding out the rows in report one which were used to get report two sum.

 

[13thmonkey]

So with 60 entries there might be 100s of combinations that match.

For the final time rep2 needs to be broken down into its components, it must have these components.

I am so Sorry for wasting time of everyone, if I am unable clarify my issue. As, I have mentioned that they will be no similar sums in report one that I am sure off. I just want to know the process of finding out the rows in report one which were used to get report two sum.

What you want can't be done and give an answer that is actually useful.

Your database that creates rep2 will have the background data, else it can't create rep2. You need to talk to the provider of the reports.

 

[USAFRet]

I am so Sorry for wasting time of everyone, if I am unable clarify my issue. As, I have mentioned that they will be no similar sums in report one that I am sure off. I just want to know the process of finding out the rows in report one which were used to get report two sum.

No, you're not "wasting our time".
We like solving problems like this. And applying collective several decades of experience to do it.

We're just saying, with the data given, what you seek cannot be done.

 

[Math Geek]

i'd have to see the spreadsheet itself to understand what is happening on report 2.

i can't even think of how that report looks or how it get the numbers it's adding up. if we had that, perhaps we could work backwards to see how it's getting data and maybe figure out if it can tell you how its working as well. i also think it is not possible but if the 2nd report can get numbers from random cells on report 1, then there may be a way to get it to show where the data is coming from.

can you upload a sample of the spreadsheet for us to check out. without that i'm not sure we can be of any more help. again i am not an expert but being able to tinker with a sample would go a long way for all of us to offer some help if possible.

 

[13thmonkey]

i'd have to see the spreadsheet itself to understand what is happening on report 2.

i can't even think of how that report looks or how it get the numbers it's adding up. if we had that, perhaps we could work backwards to see how it's getting data and maybe figure out if it can tell you how its working as well. i also think it is not possible but if the 2nd report can get numbers from random cells on report 1, then there may be a way to get it to show where the data is coming from.

can you upload a sample of the spreadsheet for us to check out. without that i'm not sure we can be of any more help. again i am not an expert but being able to tinker with a sample would go a long way for all of us to offer some help if possible.

Rep2 is from a different part of an ERP system in all probability is supplied as excel but just appears to be a headline number. The op appears to be then trying to infer how that number is created, perhaps so that what hasn't been accounted for is known.

 

[Math Geek]

that's true. i understand what he is wanted to find with the data but i did not consider that the report is created from a database.

i have only superficial experience generating reports like that so don't think even seeing how the report is created would let me reverse engineer it.

so i guess there is nothing we can really do to help 🙁

 

[Alabalcho]

So with 60 entries there might be 100s of combinations that match.
For the final time rep2 needs to be broken down into its components, it must have these components.

More precisely, 60! combinations, where 60! = 60 * 59 * 58 * 57 * ... * 3 * 2 * 1. Yes, this is a number with more than 80 digits!

 

[13thmonkey]

Not all of them will match the target number, only a very small percentage will.

 

[Alabalcho]

Not all of them will match the target number, only a very small percentage will.

That's correct, but there is no way to know which ones unless you try all possible permutations.
Of course, some "optimisations" can be done, like excluding all numbers which are larger than the target sum, but this won't help unless majority of these numbers are excluded.

 

[Ralston18]

Might be one those situations where Excel continues to be applied while the circumstances truly require a database. To at least generate the reports.

Data volume increases, computations get more involved, reports added, there are more and more special requirements, etc. all mandating Excel to jump through the proverbial "hoops".

A once simple spreadsheet becomes a clunge that even the author can no longer manage it.

Excel has come a long ways beyond just number crunching but Excel is not a database.

But all so many people find "database" to be too intimidating, scary, confusing, etc.. And generally unwilling to make any effort to step up and to learn easier and more productive, flexible, and expandable products. Not even to make their own work and life easier. Or less stressful perhaps.

Or just too afraid of change. Or even worse, the powers that be won't permit the risk. Even if parallel systems are run to permit full testing and validation. Sad.

Yet overall one can use Excel tables as the "back end" and Access as the "front end" to do all so many things much easier. Very straightforward to link data or import/export between Excel and Access.

Have made a few converts along the way - but many not. First problem with a database and they regressed....

Some sample spreadsheets, as has been suggested/requested would be helpful.

 

[13thmonkey]

It's Kludge btw, what you said is something a bit different.

Fact remains with the information the op has to work with, there is no answer.

 

[USAFRet]

Yet overall one can use Excel tables as the "back end" and Access as the "front end" to do all so many things much easier. Very straightforward to link data or import/export between Excel and Access.

Amen.
I use Access as the front end brain, and intermediary, for a LOT of things.

However...I've also seen far too many Access efforts fail completely, and I have to rebuild from the ground up.
This would be one of those cases.

 

[Ralston18]

Fully agree.

And then after you rebuild it all, get things working, someone comes along and, because they do not understand "database", reverts it all back to Excel.....

:-(

 

[77ssbssb]

Sir I am posting a screenshot of a sample excel sheet, in the let us say I want to find out which rows have a some equal to about 18500-18600. How will I do so?



Also, the file I have to work with is in "csv" extension

 

[13thmonkey]

Sir I am posting a screenshot of a sample excel sheet, in the let us say I want to find out which rows have a some equal to about 18500-18600. How will I do so?



Also, the file I have to work with is in "csv" extension

If(and(a1<18600,a1>18500),true,false)

Then just copy the formula through the rows next to the values. You need a basic excel course, there are nearly free on line ones that will tell you about this.

 

[77ssbssb]

Sir I am posting a screenshot of a sample excel sheet, in the let us say I want to find out which rows have a some equal to about 18500-18600. How will I do so?



Also, the file I have to work with is in "csv" extension

If(and(a1<18600,a1>18500),true,false)

Then just copy the formula through the rows next to the values. You need a basic excel course, there are nearly free on line ones that will tell you about this.

Thanks Sir, just tried the solution you mentioned, but it doesn't highlight rows A2 and A4 whose total comes to 18554.12 which comes under the range of 18500 to 18600, and sure Sir will try to learn more basics of excel.

 

[13thmonkey]

Sir I am posting a screenshot of a sample excel sheet, in the let us say I want to find out which rows have a some equal to about 18500-18600. How will I do so?



Also, the file I have to work with is in "csv" extension

If(and(a1<18600,a1>18500),true,false)

Then just copy the formula through the rows next to the values. You need a basic excel course, there are nearly free on line ones that will tell you about this.

Thanks Sir, just tried the solution you mentioned, but it doesn't highlight rows A2 and A4 whose total comes to 18554.12 which comes under the range of 18500 to 18600, and sure Sir will try to learn more basics of excel.

You are trying to do the same thing again, it is not possible to tell you which ones equal a given number or range of numbers. A formula can have one answer, there might be many answers to your question.

 

[Corwin65]

The possible combinations of numbers that it could take have me stumped. Been around the block with spreadsheeds (Lotus) and databases (dBase) and the number of possible combinations needed to arrive at the desired number would be hard to figure out.

As stated before, the data already exists, the OP just needs to have access to the data he is trying to proof.