Opposite of Sumproduct calculation

[segran]

Hi

I have the following calculation...the Sumproduct of Data1 and Data2 to obtain Result.

<title>Excel Jeanie HTML</title>


<!-- ######### Start Created Html Code To Copy ########## -->


Sheet2

*	A	B	C	D	E
1	Data1	0.16	0.13	0.19	0.14
2	Data2	110131	89376	9140	16110
3	*	*	*	*	*
4	Results	34001	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:60px;"><col style="width:42px;"><col style="width:35px;"><col style="width:42px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B4 =SUMPRODUCT(B2:E2,B1:E1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4






<!-- ######### End Created Html Code To Copy ########## -->




I want to do a reverse calculation for Data1 from Data2 and Result (which I have).

Any help will be appreciated.

Thank you

 

[circledchicken]

Hi,

One option is to use the Solver add-in that comes with Excel.
Your objective would be to set B4 to the known value for your result, by changing cells B1:E1.
You could also add any constraints / other options as necessary.

If you Google the topic, there are lots of examples on how to use it, here's one to get you started:
Using Excel and Solver for problem solving, a practical tutorial. | Chandoo.org - Learn Microsoft Excel Online

 

[pgc01]

Hi

It's not possible. There are infinite solutions for the reverse calculation. The original values are just one of them.

 

[segran]

Thank you circledchicken.
However, I have many Data2 type values and many corresponding Result type values, how how will I use solver to work out the respective Data1 values?

 

[circledchicken]

Thank you circledchicken.
However, I have many Data2 type values and many corresponding Result type values, how how will I use solver to work out the respective Data1 values?

You, can run it multiple times, or alternatively use VBA - here is a tutorial to get started learning about that:
Using Solver in Excel VBA

As per pgc01's comment, you need to be careful how you use it - you will get 'a' solution not necessarily the one you want.
You may want to use the optimisation constraints and options to restrict the range of potential solutions.