Query sum of values in column.

[mat18]

Hi Guys/Girls

I'm having some problems and was wondering if anyone here can help.

What I'm trying to do is, say in column A I have a set of values which are a sum of values in column B, i.e
A B
1. 100.5 20
2. 20.6 80.5
3. 30 10.2
4. 20.4
5. 15
6. 5

So above B1 + B2 = A1, and B3 + B4 + B5 + B6 = A2.+ A3
Is there anyway I can use excel to match multiple values in column B with a sum or single value in Coulmn A.

Any help would be much appriciated.
Cheers

[ZERKproject]

Hi Mat,

if you want A1 to display B1+B2 you could put either:
=B1+B2
or
=SUM(B1:B2)
In A1

If you wanted B3+B4+B5+B6 in A2
You would put:
=SUM(B3:B6)
in A2.

The sum of A2+A3 to match B3:B6 would of course require further information, i.e. which cells add to which specifically or whether there is a certain ratio that you want the total split at.

[mat18]

Basically what I have is a a data set in Column A ~ 100 Cells, which I know reconcile with a combination of values in Column B, so that say the total of column A is +10,000 and Column B is -10,000, but what I want to be able to do is find out the possible comination of values in Column B that could equal a single or comination of 1 or more values in Column A, eg. A2 may = B75, or A2 may equaly B16 + B10, or A2 may equal B1 + B2 +B100 + B45 etc, or even A2 + A5 may equal B1, or A2 + A5 may equal B7 + B3 + B10 etc.

I know I could do a number of if statments for every value in Column B to check wether they equal a value in Column A but the number of if statements I would need is too high.

Hope this helps

[ZERKproject]

Not sure about the heavily complicated statistics but for a start you could match against an array
Lets say you have data in A1:A100, and B1:B100

In C1 you could put:
=IF(MATCH($B1,$A$1:$A$100,0),"$B1","")
then fill that down,

that could find what fields in B have corresponding data in A, although as i say that is quite a basic start that could present you some information at least

[mat18]

This is true, thanks.
So I would be left with say A1 = B1 + B54 for instance or A1 + A45 = B12 + B78 + B2. So is there anyway I could check this. If I could get rid of any that are A1 = B1 + B54 for instance this would be a huge help, I had thought about using the Solver Add on, (never used this myself before so didn't get very far), but I was trying to see if from a set of values in Column B do any them sum to give a value in Column A.
In fact what I have is a number say £14.98, what I want to know is what this figure reconciles against this on the other side, I don't have a -£14.98, but I could have a figure -£x so I need to find what I need to add to that £14.98 to equal the -£x value, obviously with a large data set this could be a number of results however if it can narrow it down for me that would be great.

[ZERKproject]

i'm not sure if solver would be what you're after, i actually got taught solver in a workshop this week and it basically adjusts a set of values you select within limits you decide to make the target cell equal a certain value, it's not so much for matching values as adjusting values to reach a goal

[Alf]

I'm also not sure if Solver is the best way to go, especially if you want all possible combination to match a given value.

Still I've made a small Solver model you can have a look at to see if Solver could be of use to you.