Prev: Combining rows with duplicate names - sum numeric, concatenate str
Next: how do i make a word flash or blink in excel
From: John S on 2 Mar 2010 17:28
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


From: T. Valko on 3 Mar 2010 01:08
>{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(a)discussions.microsoft.com> wrote in message
news:F320517F-1189-410B-85F3-F7DD2A1C33E2(a)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
>
>


 | 
Pages: 1
Prev: Combining rows with duplicate names - sum numeric, concatenate str
Next: how do i make a word flash or blink in excel