shortening an equation, adding increments of a range..

[Guest]

hi, ok: tried to shorten the following, but don't see it. idea is range of
numbers from bi9 to bn9 (6 cells) to select 2 thru 5 of each (for a %change
equation).
to get a daily (incremental) total, I get accurate numbers by adding
separately.
Is there a shorter way? idea: (thanks)

2 day: (bi/bj)+(bj/bk)
3 day: (bi/bj)+(bj/bk)+(bk/bm) ..etc for 4 & 5 day

full example:
=IF(OR(BI9="",COUNTA(BJ9:BN9)=0),"",IF($AQ$2=2,(BI9/BJ9%-100)+(BJ9/BK9%-100),IF($AQ$2=3,(BI9/BJ9%-100)+(BJ9/BK9%-100)+(BK9/BL9%-100),IF($AQ$2=4,(BI9/BJ9%-100)+(BJ9/BK9%-100)+(BK9/BL9%-100)+(BL9/BM9%-100),IF($AQ$2=5,(BI9/BJ9%-100)+(BJ9/BK9%-100)+(BK9/BL9%-100)+(BL9/BM9%-100)+(BM9/BN9%-100))))))

 

[Bill Martin]

Firstly, the way you're calculating your percent returns is a bit strange
-- maybe you need to do it that way, or maybe you can use a simpler
approach?

Specifically, for a two day return you're adding the gains of each day.
Typically what you want to do is to multiply them if two days of gains are
*compounded* (i.e. the full gains of day one are reinvested for day 2). If
you take that approach, then the two day return is (bi/bk). Ripple that
all the way through your IF statement and it will substantially reduce the
amount of "stuff" you're doing.

And if you accept the first approach, then you can get rid if a lot of IF
statements by using:

[ ] = IF(BI9="",COUNTA(...), (BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)


Bill

 

[Guest]

Hi, working rotating shifts.. getting back, but will take all the help can
get: not that fast with some math, just making up what will work. with
your equation I get the general idea for offset, but thats about it.. aside
from answer not comming up the same as a copy line, with other formula, all
answers are comming up "0" zero. am I typing the equation in wrong? not
sure about the step you said to repeat? thanks..
guessing from your example: (what is the negative 1 for?)

=IF(OR(BI9="",COUNTA(BI9:BN9)=0),"",(BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)

if different than thought: 6 columns most recent value in BI9, daily all
values are copy, paste-special (values) right one column & new values
immediately put back to BI9, left most column.. $AQ$2 has number of days
(columns) comparing to, to right of BI9, thanks.
---------------------------

Firstly, the way you're calculating your percent returns is a bit strange
-- maybe you need to do it that way, or maybe you can use a simpler
approach?

Specifically, for a two day return you're adding the gains of each day.
Typically what you want to do is to multiply them if two days of gains are
*compounded* (i.e. the full gains of day one are reinvested for day 2). If
you take that approach, then the two day return is (bi/bk). Ripple that
all the way through your IF statement and it will substantially reduce the
amount of "stuff" you're doing.

And if you accept the first approach, then you can get rid if a lot of IF
statements by using:

[ ] = IF(BI9="",COUNTA(...), (BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)


Bill
------------------------

hi, ok: tried to shorten the following, but don't see it. idea is range of
numbers from bi9 to bn9 (6 cells) to select 2 thru 5 of each (for a %change
equation).
to get a daily (incremental) total, I get accurate numbers by adding
separately.
Is there a shorter way? idea: (thanks)

2 day: (bi/bj)+(bj/bk)
3 day: (bi/bj)+(bj/bk)+(bk/bm) ..etc for 4 & 5 day

full example:
=IF(OR(BI9="",COUNTA(BJ9:BN9)=0),"",IF($AQ$2=2,(BI9/BJ9%-100)+(BJ9/BK9%-100),IF($AQ$2=3,(BI9/BJ9%-100)+(BJ9/BK9%-100)+(BK9/BL9%-100),IF($AQ$2=4,(BI9/BJ9%-100)+(BJ9/BK9%-100)+(BK9/BL9%-100)+(BL9/BM9%-100),IF($AQ$2=5,(BI9/BJ9%-100)+(BJ9/BK9%-100)+(BK9/BL9%-100)+(BL9/BM9%-100)+(BM9/BN9%-100))))))

 

[Guest]

If I get one thing right, columns are not compounded.. amount integrity is
maintained to show direction of movement, for up/down..

 

[Guest]

Hi, figured out what -1 was for, to get correct percent (in decimal form);
otherwise multipy all by 100, but do not see how to get total of all days
selected, still add one at a time, with your equation?, but then maybe don't
see how fixed days ($AQ$2) is to manipulate to add multiple days... thanks.

 

[Bill Martin]

Hi, figured out what -1 was for, to get correct percent (in decimal form);
otherwise multipy all by 100, but do not see how to get total of all days
selected, still add one at a time, with your equation?, but then maybe don't
see how fixed days ($AQ$2) is to manipulate to add multiple days... thanks.

---------------------------

I don't really see a simple way to do what you want by adding each gain -
short of slogging through it all with a huge ugly collection of IFs as you
were doing to start with.

Good luck...

Bill