software

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 amp; 5 day

full example:
=IF(OR(BI9=quot;quot;,COUNTA(BJ9:BN9)=0),quot;quot;,IF($AQ$2=2,(BI 9/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))))))

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 quot;stuffquot; 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=quot;quot;,COUNTA(...), (BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)Bill
------------------------
On Wed, 1 Mar 2006 01:47:05 -0800, nastech wrote:

gt; hi, ok: tried to shorten the following, but don't see it. idea is range of
gt; numbers from bi9 to bn9 (6 cells) to select 2 thru 5 of each (for a %change
gt; equation).
gt; to get a daily (incremental) total, I get accurate numbers by adding
gt; separately.
gt; Is there a shorter way? idea: (thanks)
gt;
gt; 2 day: (bi/bj) (bj/bk)
gt; 3 day: (bi/bj) (bj/bk) (bk/bm) ..etc for 4 amp; 5 day
gt;
gt; full example:
gt; =IF(OR(BI9=quot;quot;,COUNTA(BJ9:BN9)=0),quot;quot;,IF($AQ$2=2,(BI 9/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))))))

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 quot;0quot; 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=quot;quot;,COUNTA(BI9:BN9)=0),quot;quot;,(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 amp; new values
immediately put back to BI9, left most column.. $AQ$2 has number of days
(columns) comparing to, to right of BI9, thanks.
---------------------------
quot;Bill Martinquot; wrote:

gt; Firstly, the way you're calculating your percent returns is a bit strange
gt; -- maybe you need to do it that way, or maybe you can use a simpler
gt; approach?
gt;
gt; Specifically, for a two day return you're adding the gains of each day.
gt; Typically what you want to do is to multiply them if two days of gains are
gt; *compounded* (i.e. the full gains of day one are reinvested for day 2). If
gt; you take that approach, then the two day return is (bi/bk). Ripple that
gt; all the way through your IF statement and it will substantially reduce the
gt; amount of quot;stuffquot; you're doing.
gt;
gt; And if you accept the first approach, then you can get rid if a lot of IF
gt; statements by using:
gt;
gt; [ ] = IF(BI9=quot;quot;,COUNTA(...), (BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)
gt;
gt;
gt; Bill
gt; ------------------------
gt; On Wed, 1 Mar 2006 01:47:05 -0800, nastech wrote:
gt;
gt; gt; hi, ok: tried to shorten the following, but don't see it. idea is range of
gt; gt; numbers from bi9 to bn9 (6 cells) to select 2 thru 5 of each (for a %change
gt; gt; equation).
gt; gt; to get a daily (incremental) total, I get accurate numbers by adding
gt; gt; separately.
gt; gt; Is there a shorter way? idea: (thanks)
gt; gt;
gt; gt; 2 day: (bi/bj) (bj/bk)
gt; gt; 3 day: (bi/bj) (bj/bk) (bk/bm) ..etc for 4 amp; 5 day
gt; gt;
gt; gt; full example:
gt; gt; =IF(OR(BI9=quot;quot;,COUNTA(BJ9:BN9)=0),quot;quot;,IF($AQ$2=2,(BI 9/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))))))
gt;

If I get one thing right, columns are not compounded.. amount integrity is
maintained to show direction of movement, for up/down..
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.On Thu, 2 Mar 2006 16:47:27 -0800, nastech wrote:

gt; Hi, figured out what -1 was for, to get correct percent (in decimal form);
gt; otherwise multipy all by 100, but do not see how to get total of all days
gt; selected, still add one at a time, with your equation?, but then maybe don't
gt; 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