Reference blank cells in formula

[cycles]

I am creating a spreadsheet of stock market data which will be based
on calendar days, not market days. Each year's worth of daily data is
in a column with the date to the left of the data. Because I am using
consecutive calendar dates, there will be blank cells for Saturdays,
Sundays, and holidays. To the right of each piece of daily data, I
want to create a formula that determines the percentage gain or loss
on that day from the previous trading day's price. The problem is if I
reference the data for the row above today's date to perform the
calculation, the calculation will not work on the days immediately
following blank dates (Saturdays, Sundays, and holidays). There are as
many as three blanks preceding some days and I would love it if
someone could tell me how to tell Excel I want to subtract the prior
trading day's price (going back to the first non-blank cell above
today's data) from today's data and divide the result by the prior
trading day's price. Thanks in advance for any help on this.

 

[Bernie Deitrick]

Let's say that you prices are in column B. Then a formula like this in row 6

=B6/IF(B5="",IF(B4="",IF(B3="",B2,B3),B4),B5)-1

will give you your value (based on the last four cells, assuming at least one is filled). Format
the cell for percentage.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Leaving at least one blank line before first line of data, put this row of
second data value: in the example row 1 is blank, row 2 & 3 have values in A
(date) & B (stock price)and formula is entered in C3:

=IF(B3<>"",(B3-LOOKUP(10^10,$B$1:B2))/LOOKUP(10^10,$B$1:B2),"")

Copy down

 

[Guest]

.... don't need a leading line: so in my example, first row could contain
headings

 

[cycles]

Thanks so much, Bernie. That works well for the non-blank cells but it
returns 100% for the blank cells when I fill down on the formula.

 

[Bernie Deitrick]

Peter,

Ooops.

=IF(B6<>"",B6/IF(B5="",IF(B4="",IF(B3="",B2,B3),B4),B5)-1,"")

HTH,
Bernie
MS Excel MVP

 

[cycles]

Thanks so much, Toppers, that worked just fine.

 

[cycles]

Thanks so much Topper, that worked just fine.




Leaving at least one blank line before first line of data, put this
row of
second data value: in the example row 1 is blank, row 2 & 3 have
values in A
(date) & B (stock price)and formula is entered in C3:

=IF(B3<>"",(B3-LOOKUP(10^10,$B$1:B2))/LOOKUP(10^10,$B$1:B2),"")

Copy down