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

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

circledchicken

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,851
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,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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top