Reverse SUMPRODUCT: Solve For Distributions [HELP]

**vaultboy21** · 08-30-2012, 01:26 PM

Hello,

Can anyone help me with the below (a general example):
I have certain values (in this case age; in months old) with varying distributions of the ages in a population; like this:

12 (1 year) = 50%
24 (2 years) = 18%
36 (3 years) = 12%
48 (4 years) = 10%
60 (5 years) = 10%

...So, using SUMPRODUCT I get a blended average 'months old' age of 25.44

Working this way is pretty easy; where I'm having an issue with is working in reverse. Instead of knowing the distributions, I'm given, (for example) that a given group of kids comprised of the same ages as above have an average age of 17.38 months old... How do I figure-out what the possible distributions are between the ages (12/24/36/48/60 months)??? i.e. the example from above changes to:

12 (1 year) = ??%
24 (2 years) = ??%
36 (3 years) = ??%
48 (4 years) = ??%
60 (5 years) = ??%

...and I have the blended average 'months old' age of: 17.38

I've tried looking at it many diferent ways and can't seem to figure-out how to get to these potential values. For simplicity's sake, let's assume that percentages must be whole numbers (i.e. can only be 50%-25%-25% not 49.71%-25.15%-21.14%).

Thanks!!

simplified example worksheet attached below:
example.xlsx

**Melvinrobb** · 08-30-2012, 02:26 PM

In theory, couldn't there be multiple distributions, possibly infinate?

**smls** · 08-30-2012, 02:36 PM

Are you looking for any possible answer or are you just looking for a single possible answer?

**vaultboy21** · 08-30-2012, 02:49 PM

Originally Posted by smls

Are you looking for any possible answer or are you just looking for a single possible answer?

Ideally i need just one, as I know the general trends the population follows, but if not maybe some sort of data table showing which combinations would yield the same result.

And, btw, the only reason I added the contraint to look at only distributions of 'whole percentages' would be to minimize the number of potential outcomes.

Thanks!

**Melvinrobb** · 08-30-2012, 02:57 PM

Essentially this first and foremost is a math problem. you have two formulas:

Formula #1: 12*A + 24*B + 36*C + 48*d + 60*E = 17.38
Formula #2: A + B + C + D + E = 1

If you find out where these intersect, you'll get your answer. How do to this using Excel? I am not sure... but I'm guessing it can be done...

**ChemistB** · 08-30-2012, 03:58 PM

You can use solver to get results but they probably won't match reality because, as mentioned above, you have too many variables and thus an infinite # of solutions. Attached is an example with Solver. (You'll need to activate the Solver Add-in if it's not already there) You'll find Solver on the Data Tab. If you know some of the variables, you can remove them from the variable cells and see if that helps.

Using Melvinrobb's two equations in A1 and A2 and setting C1 to C5 as the variables A, B, C, D and E. I set up constraints in the solver such that A1 = 17.38 and solve for A2 = 1.

**surabhi10** · 12-13-2012, 08:26 PM

I am first time solver user and looking same help reversing the sumproduct function in excel as I have the total but percentage range is missing so need to get the percentage range with you example in solver ,but not able to get this.