N
nastech
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, inclusive (for a
%change equation).
to get a daily RUNNING Total, I get accurate numbers by adding separately.
thanks.
Is there a shorter way? idea:
2 day: (bi/bj%-100)+(bj/bk%-100)
3 day: (bi/bj%-100)+(bj/bk%-100)+(bk/bm%-100) ..etc for 4 & 5 day
---
given idea: (note: gains are not re-invested into next left columns, so
the following may not work, if that is what was intended)
(do need to have sum of all columns specified by $AQ$2: 2-5 columns:
may need to -1 from $AQ$2 ?)
is there a way to add all the columns together, guesse a range calculation?
=IF(OR(BI9="",COUNTA(BI9:BN9)=0),"",(BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)
and to make into a full percent % figure: (still not sure how to get all
days, same as full example below)
=IF(OR(BI9="",COUNTA(BI9:BN9)=0),"",((BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)*100)
full example works:
=IF(OR(BI9="",COUNTA(BJ9:BN9)=0),"",IF(OR($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))))))
numbers from bi9 to bn9 (6 cells) to select 2 thru 5, inclusive (for a
%change equation).
to get a daily RUNNING Total, I get accurate numbers by adding separately.
thanks.
Is there a shorter way? idea:
2 day: (bi/bj%-100)+(bj/bk%-100)
3 day: (bi/bj%-100)+(bj/bk%-100)+(bk/bm%-100) ..etc for 4 & 5 day
---
given idea: (note: gains are not re-invested into next left columns, so
the following may not work, if that is what was intended)
(do need to have sum of all columns specified by $AQ$2: 2-5 columns:
may need to -1 from $AQ$2 ?)
is there a way to add all the columns together, guesse a range calculation?
=IF(OR(BI9="",COUNTA(BI9:BN9)=0),"",(BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)
and to make into a full percent % figure: (still not sure how to get all
days, same as full example below)
=IF(OR(BI9="",COUNTA(BI9:BN9)=0),"",((BI9/OFFSET(BI9,0,$AQ$2,1,1))-1)*100)
full example works:
=IF(OR(BI9="",COUNTA(BJ9:BN9)=0),"",IF(OR($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))))))