# 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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### 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
346
Replies
4
Views
337
Replies
1
Views
432
Replies
3
Views
489
Replies
7
Views
249

1,109,411
Messages
5,528,617
Members
409,828
Latest member
99DodgeRam

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...