sum
 

Greatly appreciated if someone would help me with the formula for the
following:

Using a stock price, for example, how do I can a cell automatically
calculate "cells" and not a time frame?

For example, I have the present formula:

=SUMIF(A50:A5000,TODAY(),F50:F5000)

This returns today's closing price on a summary page. But how do I get
it to automatically calcualte the today's closing price vs. 25 cells
ago (not time, but cells)? I can figure out how to calculate the above
minus the price of 25 days ago, but not 25 cells ago...

Thanks in advance,
E

sum
 

Do you want to sum the last 25 entries in the column?

Assuming there are no empty cells:

=SUM(OFFSET(F50,COUNT(F50:F65536)-1,,-25))

Biff

"gotta know" wrote in message
oups.com...
Greatly appreciated if someone would help me with the formula for the
following:

Using a stock price, for example, how do I can a cell automatically
calculate "cells" and not a time frame?

For example, I have the present formula:

=SUMIF(A50:A5000,TODAY(),F50:F5000)

This returns today's closing price on a summary page. But how do I get
it to automatically calcualte the today's closing price vs. 25 cells
ago (not time, but cells)? I can figure out how to calculate the above
minus the price of 25 days ago, but not 25 cells ago...

Thanks in advance,
E

sum
 

I very much appreciate your help but i am having a hard time trying to
figure it out.

Here's what I want to do:

1. automatically calculate the columns (with or without blank cells)
as soon as the day begins.


Let's say than in column A, I have the dates. In column B, I have the
stock price. So after midnight and I input the stock price, the cell
that is a quick reference, will show the stock price of today vs. the
stock price of 5 cells ago. I can obviously make another column and
avoid this, but I have lots of data on my spreadsheets and don't want
to navigate too much. I am trying to 'automate'.

-E

sum
 

Ok, the dates are irrelevant unless you want to search for the price on a
specific date but you're not saying that is what you want to do.

I'm assuming the dates are in ascending order in column A (but they're
irrelevant for what you want to do. I'm just trying to put together a
picture of what you have)

Column B has price.

Each day you make a new entry. So, today's price will be the last entry in
the column. To find the last entry in the price column:

=LOOKUP(10^10,B:B)

To find the price from 5 days ago (not counting today):

B1 is a column header, something like, Price, and the actual price data
starts in B2:

=INDEX(B2:B65536,COUNT(B2:B65536)-n)

Where n = number of days.

Or, you could use a cell to enter the number of days:

F1 = 5

=INDEX(B2:B65536,COUNT(B2:B65536)-F1)

Biff

"gotta know" wrote in message
ps.com...
I very much appreciate your help but i am having a hard time trying to
figure it out.

Here's what I want to do:

1. automatically calculate the columns (with or without blank cells)
as soon as the day begins.


Let's say than in column A, I have the dates. In column B, I have the
stock price. So after midnight and I input the stock price, the cell
that is a quick reference, will show the stock price of today vs. the
stock price of 5 cells ago. I can obviously make another column and
avoid this, but I have lots of data on my spreadsheets and don't want
to navigate too much. I am trying to 'automate'.

-E

sum
 

Thank you very much... To think that I've been using excel for years,
creating P&L's, etc. and have usually formulated convoluted approaches
to derive at an answer. Perhaps a training seminar is in line.

Thanks again,
E