today() help

[JESSIER4025]

I need help with the following problem.
I used a blank worksheet to test a formula and i was getting #value! returned.

Basically i am working with two columns.
Column "A" will have dates (1/30/2007) etc..

Column "B" will have length of time(1:30)
i am having trouble writing the formula to say
if cell range in column A is less than today-7 then average cell range in column B

If a formula can be written to make this work will it only pull data from column B that is associated with the cells in column A that meets the date criteria or will it average all data in column B?
If someone can write a formula that will work use any range and i will adjust it accordingly to fit my sheet.

Thanks

[daddylonglegs]

Try

=AVERAGE(IF(A1:A100<TODAY()-7,B1:B100))

confirmed with CTRL+SHIFT+ENTER

[JESSIER4025]

This formula works but in reverse.
Which i dont understand why. The formula looks mathmatically correct.
=AVERAGE(IF(A1:A100<TODAY()-7,B1:B100))
excel is pulling data from cells that meets the exact opposite criteria. I changed < to > and it pulled data that < should have pulled. Very strange.
Can anyone explain this? Maybe i am burnt out from looking at these $*@*!!$@#$ spreadsheets too long.
Thanks

[daddylonglegs]

You asked asked for dates less than today()-7, that will give you all past dates more than a week old

If you use

=AVERAGE(IF(A1:A100>TODAY()-7,B1:B100))

that will give you dates in the last 7 days (including today) and any future dates