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.

 

[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.