I want to do a what if analysis for the caculation below. Is there a way to
use a cell reference to another worksheets in the same workbook for the
variables in the formula below ( ie. 0,500000,1000000, etc and
..0.01;-0.001;-0.0002 etc.)


=SUMPRODUCT(--(C5{0;500000;1000000;2500000;10000000}),C5-{0;500000;1000000;2500000;10000000},{0.01;-0.001;-0.0002;-0.005;-0.001})/365

Thus the formula would read something like this:

=SUMPRODUCT(--(C5{0;worksheet1 $c$1;worksheet1 $c$2;worksheet1
$c$3;worksheet1 $c$4}),C5-{worksheet1 $c$1;worksheet1 $c$2;worksheet1
$c$3;worksheet1 $c$4},{worksheet1 $d$1;worksheet1 $d$2;worksheet1
$d$3;worksheet1 $d$4})/365
with this formula is in worksheet2.

Any help would be appreciated.

Thanks

{0.01;-0.001;-0.0002;-0.005;-0.001}

What are the percentages?

Build a table like this:

C1 = 0
C2 = 500000
C3 = 1000000
C4 = 2500000
C5 = 10000000

D1 = 0.01
D2 = ?
D3 = ?
D4 = ?
D5 = ?

E1: =D1
E2: =D1-D2
E3: =D2-D3
E4: =D3-D4
E5: =D4-D5

A1 = some number

Then:

=SUMPRODUCT(--(A1C1:C5),(A1-C1:C5),E1:E5)

http://mcgimpsey.com/excel/variablerate.html

--
Biff
Microsoft Excel MVP


"John S" wrote in message
...
I want to do a what if analysis for the caculation below. Is there a way to
use a cell reference to another worksheets in the same workbook for the
variables in the formula below ( ie. 0,500000,1000000, etc and
.0.01;-0.001;-0.0002 etc.)


=SUMPRODUCT(--(C5{0;500000;1000000;2500000;10000000}),C5-{0;500000;1000000;2500000;10000000},{0.01;-0.001;-0.0002;-0.005;-0.001})/365

Thus the formula would read something like this:

=SUMPRODUCT(--(C5{0;worksheet1 $c$1;worksheet1 $c$2;worksheet1
$c$3;worksheet1 $c$4}),C5-{worksheet1 $c$1;worksheet1 $c$2;worksheet1
$c$3;worksheet1 $c$4},{worksheet1 $d$1;worksheet1 $d$2;worksheet1
$d$3;worksheet1 $d$4})/365
with this formula is in worksheet2.

Any help would be appreciated.

Thanks