Updating cell from table

[marquez313]

I'm using Excel 2007 to keep a database of daily settlements for financial futures as well as a variety of studies for these daily values. I want to have one page that I can print every morning with the relevent information for the day. So what i'm trying to figure out is how to make the cell on my "Settlements" page always read the last cell of my data column or find the average of the last five cells, for example.

 

[nbrcrunch]

I may have gotten your columns mixed up. After testing I also realized that "-5" would give the last 5 days AND the current date. Since you wanted a total of 5 days, including the current date, that has to be "-4"

I think A had the dates and B had the values, right? If so then the formula i posted earlier is correct. Also, I just remembered its not the full column, because you were reserving the first row for your totals, so:

=average(if(a2:a10000 > = today()-4,b2:b10000))

If it is the other way around, then

=average(if(b2:b10000 > = today()-4,a2:a10000))

Remember that it must be entered with Ctrl-Shift-Enter

 

[marquez313]

so close! here's what i have entered:

=AVERAGE(IF('TUA Data'!G:G>=TODAY()-4,'TUA Data'!H:H))

but when i go to the page and just do an average of the last 5 days, the numbers don't jive.