I'm tracking stock prices, and enter daily closing prices for stock symbols
listed in a column. Each row contains daily closing prices. The final
column (once I've got the formula sorted out) is the %age change from the
first value in the row.

How do I designate the last value entered, divided by the first value in
that row? After specifying the first value in the row, End Right seems to go
to the last entered value in that row. How do I enter a formula in the
Percentage Change column to divide the last value in the row by the first
value in that row (-1)?

Hi,

Maybe an example of the data would help but the formula would be:
if the first value in the row is in column 2 then:

=INDIRECT("R"&ROW()&"C"&COLUMN()-1,FALSE)/INDIRECT("R"&ROW()&"C2",FALSE)

change the 2 in "C2" at the end to "C1" if it is really the first column or
some other column.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"GMW" wrote:

I'm tracking stock prices, and enter daily closing prices for stock symbols
listed in a column. Each row contains daily closing prices. The final
column (once I've got the formula sorted out) is the %age change from the
first value in the row.

How do I designate the last value entered, divided by the first value in
that row? After specifying the first value in the row, End Right seems to go
to the last entered value in that row. How do I enter a formula in the
Percentage Change column to divide the last value in the row by the first
value in that row (-1)?

Last value

=LOOKUP(2,1/ISNUMBER(1:1),1:1)

This array formula gets the first entry

=MATCH(TRUE,1:1<"",0)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"GMW" wrote in message
...
I'm tracking stock prices, and enter daily closing prices for stock
symbols
listed in a column. Each row contains daily closing prices. The final
column (once I've got the formula sorted out) is the %age change from the
first value in the row.

How do I designate the last value entered, divided by the first value in
that row? After specifying the first value in the row, End Right seems to
go
to the last entered value in that row. How do I enter a formula in the
Percentage Change column to divide the last value in the row by the first
value in that row (-1)?

I have stock symbol in column A. Stock price starts from column B.

Last value: Same as Bob's.

First value/stock price

{=INDEX(1:1,MATCH(TRUE,(ISNUMBER(1:1)),0))} array formula i.e. ctrl+shift+enter

I have to use ISNUMBER because column A is a stock symbol which is text in North America. If the stock symbol is number, then I have to come up with a different formula.

This formula seems to be okay if certain stock price columns are left blank or inadvertently have text. The first stock price doesn't have to be in column B. If the first value *always* starts in column B, I may just hardcode column B in the formula to calculate percentage.

Bob or anyone, please correct me if I am wrong.

Epinn

"Bob Phillips" wrote in message ...
Last value

=LOOKUP(2,1/ISNUMBER(1:1),1:1)

This array formula gets the first entry

=MATCH(TRUE,1:1<"",0)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"GMW" wrote in message
...
I'm tracking stock prices, and enter daily closing prices for stock
symbols
listed in a column. Each row contains daily closing prices. The final
column (once I've got the formula sorted out) is the %age change from the
first value in the row.

How do I designate the last value entered, divided by the first value in
that row? After specifying the first value in the row, End Right seems to
go
to the last entered value in that row. How do I enter a formula in the
Percentage Change column to divide the last value in the row by the first
value in that row (-1)?

Bob,

I don't feel comfortable with my formula.

From Excel Help for Index function:-

"If array contains only one row or column, the corresponding row_num or column_num argument is optional."

What does "optional" really mean? I don't even have to place a comma where the syntax asks for row number in my formula? Not sure why my formula works.

GMW,

FYI http://office.microsoft.com/en-us/ma...444281033.aspx

Epinn

"Epinn" wrote in message ...
I have stock symbol in column A. Stock price starts from column B.

Last value: Same as Bob's.

First value/stock price

{=INDEX(1:1,MATCH(TRUE,(ISNUMBER(1:1)),0))} array formula i.e. ctrl+shift+enter

I have to use ISNUMBER because column A is a stock symbol which is text in North America. If the stock symbol is number, then I have to come up with a different formula.

This formula seems to be okay if certain stock price columns are left blank or inadvertently have text. The first stock price doesn't have to be in column B. If the first value *always* starts in column B, I may just hardcode column B in the formula to calculate percentage.

Bob or anyone, please correct me if I am wrong.

Epinn

"Bob Phillips" wrote in message ...
Last value

=LOOKUP(2,1/ISNUMBER(1:1),1:1)

This array formula gets the first entry

=MATCH(TRUE,1:1<"",0)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"GMW" wrote in message
...
I'm tracking stock prices, and enter daily closing prices for stock
symbols
listed in a column. Each row contains daily closing prices. The final
column (once I've got the formula sorted out) is the %age change from the
first value in the row.

How do I designate the last value entered, divided by the first value in
that row? After specifying the first value in the row, End Right seems to
go
to the last entered value in that row. How do I enter a formula in the
Percentage Change column to divide the last value in the row by the first
value in that row (-1)?

If you have a vertical array like this in A1:A4

1
2
3
4

=INDEX(A1:A4, 3)
=INDEX(A1:A4, 3, 1)

return the same element. The column number is optional because there is
only one column. Same thing for data containing only one row.

1 2 3 4

=INDEX(A1:D1, 3)
=INDEX(A1:D1, 1, 3)

except the row number is optional.

Optional means the argument does not have to be supplied. Putting in a
comma means you have supplied the argument, but it is empty (or blank).

Consider the If statement
=IF(A1=3, "A1 is 3") returns FALSE (for a false condition)
=IF(A1=3, "A1 is 3",) returns 0 (for a false condition)

or
=IF(A1=3,,) will return 0 for both True and False conditions.

or
=IF(A1=3,) will return 0 for True condition and FALSE for false condition.

Because for IF statements, the true conditional is required, but it can be
empty (or blank). The false conditional is optional and will return FALSE if
omitted. If it is empty (or blank as the help file says) it will return 0.



"Epinn" wrote:

Bob,

I don't feel comfortable with my formula.

From Excel Help for Index function:-

"If array contains only one row or column, the corresponding row_num or column_num argument is optional."

What does "optional" really mean? I don't even have to place a comma where the syntax asks for row number in my formula? Not sure why my formula works.

GMW,

FYI http://office.microsoft.com/en-us/ma...444281033.aspx

Epinn

"Epinn" wrote in message ...
I have stock symbol in column A. Stock price starts from column B.

Last value: Same as Bob's.

First value/stock price

{=INDEX(1:1,MATCH(TRUE,(ISNUMBER(1:1)),0))} array formula i.e. ctrl+shift+enter

I have to use ISNUMBER because column A is a stock symbol which is text in North America. If the stock symbol is number, then I have to come up with a different formula.

This formula seems to be okay if certain stock price columns are left blank or inadvertently have text. The first stock price doesn't have to be in column B. If the first value *always* starts in column B, I may just hardcode column B in the formula to calculate percentage.

Bob or anyone, please correct me if I am wrong.

Epinn

"Bob Phillips" wrote in message ...
Last value

=LOOKUP(2,1/ISNUMBER(1:1),1:1)

This array formula gets the first entry

=MATCH(TRUE,1:1<"",0)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"GMW" wrote in message
...
I'm tracking stock prices, and enter daily closing prices for stock
symbols
listed in a column. Each row contains daily closing prices. The final
column (once I've got the formula sorted out) is the %age change from the
first value in the row.

How do I designate the last value entered, divided by the first value in
that row? After specifying the first value in the row, End Right seems to
go
to the last entered value in that row. How do I enter a formula in the
Percentage Change column to divide the last value in the row by the first
value in that row (-1)?

JMB,

Thank you so much. I have started my own thread "Interpreting comma where an optional argument is" because I don't want to "hijack" this thread. Hope GMW won't mind the extra details. I guess after all the formula I suggested for him/her works well.

First value/stock price

{=INDEX(1:1,MATCH(TRUE,(ISNUMBER(1:1)),0))} array formula i.e. ctrl+shift+enter

Epinn

"JMB" wrote in message ...
If you have a vertical array like this in A1:A4

1
2
3
4

=INDEX(A1:A4, 3)
=INDEX(A1:A4, 3, 1)

return the same element. The column number is optional because there is
only one column. Same thing for data containing only one row.

1 2 3 4

=INDEX(A1:D1, 3)
=INDEX(A1:D1, 1, 3)

except the row number is optional.

Optional means the argument does not have to be supplied. Putting in a
comma means you have supplied the argument, but it is empty (or blank).

Consider the If statement
=IF(A1=3, "A1 is 3") returns FALSE (for a false condition)
=IF(A1=3, "A1 is 3",) returns 0 (for a false condition)

or
=IF(A1=3,,) will return 0 for both True and False conditions.

or
=IF(A1=3,) will return 0 for True condition and FALSE for false condition.

Because for IF statements, the true conditional is required, but it can be
empty (or blank). The false conditional is optional and will return FALSE if
omitted. If it is empty (or blank as the help file says) it will return 0.