MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Reference to another worksheet cell in a sumproduct formula

• Follow

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

```>{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(--(A1>C1:C5),(A1-C1:C5),E1:E5)

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

--
Biff
Microsoft Excel MVP

"John S" <JohnS@discussions.microsoft.com> wrote in message
news:F320517F-1189-410B-85F3-F7DD2A1C33E2@microsoft.com...
>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

1 Replies
491 Views

8/23/2012 10:32:20 AM