Hi there,

We track patron interactions, e.g., reference questions, during multiple discrete tracking periods throughout the year and then use that data to make assumptions about our total number of interactions each year. I'm looking at using the trend function in Excel to fill in between tracking periods, but it seems overly complicated. What do you do to fill in the blanks?

Let me know if I can clarify this further.

Thank you!

Janelle

(05-08-2019, 05:35 PM)janelle.youngblood Wrote: [ -> ]Hi there,

We track patron interactions, e.g., reference questions, during multiple discrete tracking periods throughout the year and then use that data to make assumptions about our total number of interactions each year. I'm looking at using the trend function in Excel to fill in between tracking periods, but it seems overly complicated. What do you do to fill in the blanks?

Let me know if I can clarify this further.

Thank you!

Janelle

Hi, Janelle.

I know that we're tracking for one week each month then, as you, using that to extrapolate the annual number. I'll run this by the person who does this for Arapahoe Libraries today and post the results here. Great question! Thanks for posting it.

(05-10-2019, 08:46 AM)jane.martel Wrote: [ -> ] (05-08-2019, 05:35 PM)janelle.youngblood Wrote: [ -> ]Hi there,

We track patron interactions, e.g., reference questions, during multiple discrete tracking periods throughout the year and then use that data to make assumptions about our total number of interactions each year. I'm looking at using the trend function in Excel to fill in between tracking periods, but it seems overly complicated. What do you do to fill in the blanks?

Let me know if I can clarify this further.

Thank you!

Janelle

Hi, Janelle.

I know that we're tracking for one week each month then, as you, using that to extrapolate the annual number. I'll run this by the person who does this for Arapahoe Libraries today and post the results here. Great question! Thanks for posting it.

Hi, Janelle.

Here you go: We collect transaction numbers for one week each month. At the end of the year, these numbers are summed then the sum divided by 12 (the number of weeks for which we have "real" data) to get the average number of transactions per week. This is multiplied by 52 (weeks in a year) to get our annual transaction number to report. If you'd like, I could send you a spreadsheet with the formulas.

(05-10-2019, 10:26 AM)jane.martel Wrote: [ -> ] (05-10-2019, 08:46 AM)jane.martel Wrote: [ -> ] (05-08-2019, 05:35 PM)janelle.youngblood Wrote: [ -> ]Hi there,

We track patron interactions, e.g., reference questions, during multiple discrete tracking periods throughout the year and then use that data to make assumptions about our total number of interactions each year. I'm looking at using the trend function in Excel to fill in between tracking periods, but it seems overly complicated. What do you do to fill in the blanks?

Let me know if I can clarify this further.

Thank you!

Janelle

Hi, Janelle.

I know that we're tracking for one week each month then, as you, using that to extrapolate the annual number. I'll run this by the person who does this for Arapahoe Libraries today and post the results here. Great question! Thanks for posting it.

Hi, Janelle.

Here you go: We collect transaction numbers for one week each month. At the end of the year, these numbers are summed then the sum divided by 12 (the number of weeks for which we have "real" data) to get the average number of transactions per week. This is multiplied by 52 (weeks in a year) to get our annual transaction number to report. If you'd like, I could send you a spreadsheet with the formulas.

Thank you, Jane! I like that method. What we're struggling with, though, is getting monthly numbers to forecast between our tracking periods. Do you know if that's something Arapahoe uses averages for, or a simpler trend function?

(05-10-2019, 11:14 AM)janelle.youngblood Wrote: [ -> ] (05-10-2019, 10:26 AM)jane.martel Wrote: [ -> ] (05-10-2019, 08:46 AM)jane.martel Wrote: [ -> ] (05-08-2019, 05:35 PM)janelle.youngblood Wrote: [ -> ]Hi there,

We track patron interactions, e.g., reference questions, during multiple discrete tracking periods throughout the year and then use that data to make assumptions about our total number of interactions each year. I'm looking at using the trend function in Excel to fill in between tracking periods, but it seems overly complicated. What do you do to fill in the blanks?

Let me know if I can clarify this further.

Thank you!

Janelle

Hi, Janelle.

I know that we're tracking for one week each month then, as you, using that to extrapolate the annual number. I'll run this by the person who does this for Arapahoe Libraries today and post the results here. Great question! Thanks for posting it.

Hi, Janelle.

Here you go: We collect transaction numbers for one week each month. At the end of the year, these numbers are summed then the sum divided by 12 (the number of weeks for which we have "real" data) to get the average number of transactions per week. This is multiplied by 52 (weeks in a year) to get our annual transaction number to report. If you'd like, I could send you a spreadsheet with the formulas.

Thank you, Jane! I like that method. What we're struggling with, though, is getting monthly numbers to forecast between our tracking periods. Do you know if that's something Arapahoe uses averages for, or a simpler trend function?

Hi, Janelle.

I'm not sure I fully understand your question but thought of something that might work. Let's say you track reference questions for 7 days and you had 700 reference questions. That's an average of 100/day. Then you could take your daily average of 100 and multiply by the number of days in the month, say 30. That's 3000 forecasted for the month minus the 700 you know you've already actually done so you can predict that you'll have another 2300 (3000 minus 700) for the monthly forecast. Does that help?