Opposite of Sumproduct calculation

segran

Active Member
Joined
Aug 20, 2004
Messages
335
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

*ABCDE
1Data10.160.130.190.14
2Data211013189376914016110
3*****
4Results34001***

<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
CellFormula
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
 

Some videos you may like

Excel Facts

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

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi

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

segran

Active Member
Joined
Aug 20, 2004
Messages
335
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
Joined
Aug 13, 2011
Messages
2,932
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,486
Messages
5,548,347
Members
410,828
Latest member
A9Bosv3
Top