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]

just autosum each row and a quick glance will tell you which equals 130. simplest way anyway.

 

[77ssbssb]

just autosum each row and a quick glance will tell you which equals 130. simplest way anyway.

Thanks, Autosum calculates total rows sum. I want to know the rows whose total equals to 130.

 

[Math Geek]

then you want more than a basic feature. been a while for me but you're looking at if/then type statements if i recall right. should be able to autosum it and then have it highlight the row or something if it equals the right number.

don't want to do your homework for you so i'll let you go from there

 

[13thmonkey]

That's not trivial, in fact I'm not sure it can be done in Excel without doing it the very long way of assessing all combinations, one combination per column, hide all of those columns (10's of them at least), and then use the result of each column to determine flags that highlight which ones add up to 130. I'd do it in VBA, even then it'd be tricky, what it none of the combinations add up to 130.

Interested to see if anyone else has a solution to this. This is nasty.

Are you sure you've understood the question correctly. (out of N numbers which M numbers from that set sum to X)

 

[77ssbssb]

then you want more than a basic feature. been a while for me but you're looking at if/then type statements if i recall right. should be able to autosum it and then have it highlight the row or something if it equals the right number.

don't want to do your homework for you so i'll let you go from there

Thanks, been searching for any video tutorial which will help me as I am a beginner, but couldn't find any one which will help my case of finding out the rows...

 

[Math Geek]

can't say i have ever tried such a thing myself but i do recall balancing type activities where you could use an if/then after summing a column to get it to maybe turn red if it was a negative number or some other special thing to draw your attention to it.

that type thing is what am thinking here except instead of positive and negative, it would be equal to or not equal to his target number. may change how to do it a lot, i'm not sure there. but i do recall doing this in excel years ago in some class or other.
only idea i have really as i never had to go deeper into excel like VBA or scripts.

 

[13thmonkey]

You need to be able to loop, so that you can iterate and get closer to an answer. If you can iterate you can have a first stab at it, find out what the difference is, test combinations to see if any of the remaining numbers combined with a current number creates this difference.

Like I said this is nasty, it's not basic excel, i'm very very very experienced with Excel, it'd take me 30mins of hard thinking just to figure out how to do it, never mind how to then make that happen.

 

[77ssbssb]

can't say i have ever tried such a thing myself but i do recall balancing type activities where you could use an if/then after summing a column to get it to maybe turn red if it was a negative number or some other special thing to draw your attention to it.

that type thing is what am thinking here except instead of positive and negative, it would be equal to or not equal to his target number. may change how to do it a lot, i'm not sure there. but i do recall doing this in excel years ago in some class or other.
only idea i have really as i never had to go deeper into excel like VBA or scripts.

Sir, actually the requirement is for my office report purpose where in I have two reports, report one in which various expense heads are mentioned and in report two there is cumulative total of some of the heads from report 1. I wanted to find out the heads from report one whose total were mentioned in report two

 

[USAFRet]

Yeah, this is non trivial.

Put all the values into an array, and start combining in different combinations.
1 + 2, 1 + 3, 1 + 4, 2 + 3, 2 + 4, 1 + 2 + 3, 1 + 2 + 4, etc, etc etc.

When you hit the magic number, stop.

Whats the use case for this? There may be a different way to skin the cat.

 

[13thmonkey]

you need report 2 broken down, with reference numbers, then you can cross match, else you are just guessing that some numbers from rep1 happen to match rep2, but that could be blind luck.

 

[77ssbssb]

Yeah, this is non trivial.

Put all the values into an array, and start combining in different combinations.
1 + 2, 1 + 3, 1 + 4, 2 + 3, 2 + 4, 1 + 2 + 3, 1 + 2 + 4, etc, etc etc.

When you hit the magic number, stop.

Whats the use case for this? There may be a different way to skin the cat.

Let me make a example Sir:

Report One:
Charge head 1: 200/-
Charge Head 2: 500/-
Charge Head 3: 800/-

Report two:

Charge: 1000/-

Now I want to find out which heads were added in report one to achieve 1000/- in report 2?

 

[13thmonkey]

Yeah, this is non trivial.

Put all the values into an array, and start combining in different combinations.
1 + 2, 1 + 3, 1 + 4, 2 + 3, 2 + 4, 1 + 2 + 3, 1 + 2 + 4, etc, etc etc.

When you hit the magic number, stop.

Whats the use case for this? There may be a different way to skin the cat.

Let me make a example Sir:

Report One:
Charge head 1: 200/-
Charge Head 2: 500/-
Charge Head 3: 800/-

Report two:

Charge: 1000/-

Now I want to find out which heads were added in report one to achieve 1000/- in report 2?

If you manage to make them match it's not an important answer as you don't know if they are correctly matching. One you hit more than maybe 20items in report 1 there will be many ways to reach the number in report 2,which is correct?

 

[USAFRet]

Sir, actually the requirement is for my office report purpose where in I have two reports, report one in which various expense heads are mentioned and in report two there is cumulative total of some of the heads from report 1. I wanted to find out the heads from report one whose total were mentioned in report two

That sounds like an error in capturing the data.

"some of the heads"...but not all.
Which ones and why?


Consider this....
Report 1:

1 - 500
2 - 300
3 - 800
4 - 600

Report 2:
Charge - 1100

There are 2 different ways to get to 1100 from the Report 1 numbers. Which one is the correct answer?

 

[13thmonkey]

Under the hood rep2 is made of many heads, you need the report to give you that detail, then it is trivial and got can get an absolute answer.

 

[Wolfshadw]

And let's not forget this iteration:
Report 1:

1 - 500
2 - 300
3 - 800
4 - 600
5 - 200

Report 2:
Charge - 1100

That makes three different ways to get to 1100 from Report 1 charges.

-Wolf sends

 

[77ssbssb]

Yeah, this is non trivial.

Put all the values into an array, and start combining in different combinations.
1 + 2, 1 + 3, 1 + 4, 2 + 3, 2 + 4, 1 + 2 + 3, 1 + 2 + 4, etc, etc etc.

When you hit the magic number, stop.

Whats the use case for this? There may be a different way to skin the cat.

Let me make a example Sir:

Report One:
Charge head 1: 200/-
Charge Head 2: 500/-
Charge Head 3: 800/-

Report two:

Charge: 1000/-

Now I want to find out which heads were added in report one to achieve 1000/- in report 2?

If you manage to make them match it's not an important answer as you don't know if they are correctly matching. One you hit more than maybe 20items in report 1 there will be many ways to reach the number in report 2,which is correct?

Sir, Actually my report one will be having about 60 entries and from that I have to get total of one entry and they will be not be having similar sums that I am sure of. So, considering that the sum will be not be more than once in report 1 what will be the process to get the heads?

 

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

 

[Ralston18]

In your example:

Report One:
Charge head 1: 200/-
Charge Head 2: 500/-
Charge Head 3: 800/-

Original Report two:

Charge: 1000/-

==========


Why not just have Report 2 list the "heads" that were somehow identified and totaled to appear in Report 2?

As I understand it, all the necessary selections, logic, and calculations are already being done. Just need to capture and include the labels as part of the selection process.

E.g:

Report One:

Charge head 1: 200/-
Charge Head 2: 500/-
Charge Head 3: 800/-

New Report two:

Charge Head 1: 200/-
Charge Head 3: 800/-

Total Charge: 1000/-

 

[13thmonkey]

Precisely, the data must exist.