# Opposite of Sumproduct calculation

#### segran

##### Active Member
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>

 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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### pgc01

##### MrExcel MVP
Hi

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

#### segran

##### Active Member
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

##### Well-known Member
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.

Replies
2
Views
388
Replies
4
Views
393
Replies
1
Views
443
Replies
3
Views
506
Replies
7
Views
250