Goal seek with two conditions

[ravishankar]

I want to find the values of a,b,c,d and e if a*5+b*4+c*3+d*2+e*1 = 70 (or any other input value) and a+b+c+d+e = 20 (fixed).
any solutions preferably a worksheet function or else a macro is also OK
ravi

 

[estevaoba]

Assuming:

values 5, 4, 3, 2, 1 are in range A2:E2;
70 is in F3; 20 is in G3;
your data are in range A3:E3.

Array enter this (paste and hit ctrl+shift+enter keys simultaneously) and copy across:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IF(AND(SUM($A$3:$E$3*$A$2:$E$2)=$F$3,SUM($A$3:$E$3)=$G$3),A3,"")[/TD]
[/TR]
</tbody>[/TABLE]