excel function

A

Adam Pisani

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!
 
A

Adam Pisani

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
 
R

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.
 
R

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
 
A

Adam Pisani

Thank you very much!

Roger Govier said:
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.
Click to expand...
 
Top