excel function

[Guest]

Hi,

I have a spreadsheet with a column for "date entered", "order booked date"
and "lag" where "lag" is the difference between the "date entered" and the
"order booked date". This is a large spreadsheet and I need to take the
"order booked date" and "lag" and get an average weekly lag. I'm trying to
find a function or way to best do this without having to sort the "order
booked date", etc. The spreadsheet needs to be able to be manipulated by
other columns after.

Thanks for any help!

 

[Guest]

Why do you have to sort it?

 

[Guest]

Brian,

I'm sure I don't have to sort it. That was basically my way of saying that
I don't know how to find the data. Any help you can provide would be
appreciated.

Sincerely,

Adam Pisani

 

[Roger Govier]

One way would be
Assuming Columns A,B,and C for your three headings
=SUMPRODUCT(--($B$2:$B$1000>=DATE(2005,7,24)),--($B$2:$B$10000<=DATE(2005,7,31)),$C$2:$C$10000)
/SUMPRODUCT(--($B$2:$B$1000>=DATE(2005,7,24)),--($B$2:$B$10000<=DATE(2005,7,31))
for the current week.
You could have cells holding the relevant dates for the start and end of
weeks and subsitute those in the formula in place of
DATE(2205,7,24) etc.

 

[Ruthki]

need to know more about how your data is laid out and how you want t
see the result to give you any specific function advice.

General ideas -
=datedif(start date, end date,"D") this function will allow you t
calc dif between 2 dates in days.

=weeknum(date) will give you the week number


= you can use sumif and countif to sum data and get averages based o
different criteria. Or you can use sum product formula to wit
different criteria

 

[Guest]

Thank you very much!

One way would be
Assuming Columns A,B,and C for your three headings
=SUMPRODUCT(--($B$2:$B$1000>=DATE(2005,7,24)),--($B$2:$B$10000<=DATE(2005,7,31)),$C$2:$C$10000)
/SUMPRODUCT(--($B$2:$B$1000>=DATE(2005,7,24)),--($B$2:$B$10000<=DATE(2005,7,31))
for the current week.
You could have cells holding the relevant dates for the start and end of
weeks and subsitute those in the formula in place of
DATE(2205,7,24) etc.

 

[Guest]

Thank you very much.