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.