Lookup and sum multiple variables based on a pre-defined equation

[JT90000]

All,

I have a slightly frustrating problem to solve in Excel.

Firstly, I have a database of quarterly data with hundreds of variables. I need to combine these variables based on some pre-defined relationships. For example, my database looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Q1 2014[/TD]
[TD]Q2 2014[/TD]
[TD]Q3 2014[/TD]
[TD]Q4 2014[/TD]
[/TR]
[TR]
[TD]AAAA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAAB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAAC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAAD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I then have some equations, such as:


BBBB = AAAA - AAAB (note that sometimes we need to minus a variable)
CCCC = AAAD + AAAE + AAAD + AAAF

And it needs to be output into a table like this.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Q1 2014[/TD]
[TD]Q2 2014[/TD]
[TD]Q3 2014[/TD]
[TD]Q4 2014[/TD]
[/TR]
[TR]
[TD]BBBB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCCC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

There can be upto 15 variables going into one equation. I was wondering what the best way of doing this is given that:

a) The equations need to be changeable (even though they are unlikely to change, we sometimes have calssification changes), and
b) The formulae to merge the variables needs to be relatively transparent. At the moment, we have a vlookup formula that is about 20 lines long, and is extremely difficult to fix if anything breaks.

For info, there are about 500 'input' variables and about 70' output variables.

Thanks very much in advance.

 

[DILIPandey]

Hi JT,

Can you post some sample data here with your expected output, manually entered ?


Regards,
DILIPandey