Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference blank cells in formula
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference blank cells in formula
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 "cycles" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference blank cells in formula
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 "Bernie Deitrick" wrote: 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 "cycles" wrote in message oups.com... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference blank cells in formula
.... don't need a leading line: so in my example, first row could contain
headings "Toppers" wrote: 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 "Bernie Deitrick" wrote: 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 "cycles" wrote in message oups.com... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference blank cells in formula
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference blank cells in formula
Peter,
Ooops. =IF(B6<"",B6/IF(B5="",IF(B4="",IF(B3="",B2,B3),B4),B5)-1,"") HTH, Bernie MS Excel MVP "cycles" wrote in message ps.com... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference blank cells in formula
Thanks so much, Toppers, that worked just fine.
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference blank cells in formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help: Add cells with certain text + cells that are blank | Excel Worksheet Functions | |||
Very puzzling - how do I reference cells without the blank rows??? | Excel Worksheet Functions | |||
how to get excel to display blank if reference cell blank | Excel Worksheet Functions | |||
reference blank cell in formula | Excel Worksheet Functions | |||
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions |