Deciphering a formula?

I have a spreadsheet with a formula that someone on this forum was gracious enough to help me create. Unfortunately, my knowledge of Excel isn't sophisticated enough to completely follow what it is looking at.

If you look at the Summary worksheet of the attached file, you will see averages (4wk, 3mo, 6mo, and 1yr) for each employee. The formula is supposed to exclude any days on the weekly spreadsheets with a zero in the SOLD column. (When the employee is out for training, sick, on vacation, or doesn't work Saturdays, we don't want to penalize their averages).

Some of the managers have said the averages don't seem to be leaving out the 0hr days. I'm wondering if the formula is excluding the Weekly SOLD total with 0hrs, instead of looking at each day and excluding all days filled in with a 0 SOLD? Also, if nothing is filled in for the SOLD column on a day, how does the formula handle that?

TIA!
Amy

Thank you John and CAntosh!

I like how you changed the SOLD weekly total to indicate how many days were worked. Is it possible to insert a column next to it (on the weekly spreadsheets) which shows the average hrs/day for the week? (Ie "SOLD" 50.5@5, "Daily Ave" 10.1) And thank you for adjusting the Productivity and Efficiency columns too.

"The return there should represent the DAILY average in the given time period, counting only the days worked." Yes, that's what I need.

I think the reason the formulas had 2 weeks extra added to the averages was because the workbook automatically adds a new worksheet for the current week, so at best the averages would be calculated through the previous week. However, I think the managers were filling them in about a week after a week ended (ie not Monday morning immediately after the week ended). The 4 week averages were only pulling 2 weeks' worth of information because last week's numbers hadn't been entered, and the current week was blank because it was in process. Can I change it to add one extra week for each average (so it's not looking at the current week for the averages), and tell them to keep up on the spreadsheet once a week is completed if they want accurate averages on the Summary sheet? Right now the 4 week ave is showing 0 for everyone because the data entry is behind. I checked Ernesto's 3 mo ave as an example. For 13 weeks from the week of Feb 27th through the week of May 22nd (the last week filled in), I get 155.3 hours in 51 days which averages out to 3.04 hrs/day, but the 3mo ave on the Summary spreadsheet shows 2.48 hrs/day and 71.90 hrs worked.

Lastly, is there a quick way to apply these formula changes to other workbooks? We have multiple workbooks with the same set up, one for each group of employees. I was using this one as an example to get the formulas working the way we want them to, then I will have to update the others. :|

Thanks again!

I tried to apply the average hrs/day formula to the May 1-May 6 spreadsheet but I'm getting a DIV/0 error.

Thank you for confirming how to adjust the number of weeks. That's what I did before but I wasn't 100% sure if that was correct.

Can you test one average to see if the averages are being calculated correctly now? I tried but maybe my math is off: "I checked Ernesto's 3 mo ave as an example. For 13 weeks from the week of Feb 27th through the week of May 22nd (the last week filled in), I get 155.3 hours in 51 days which averages out to 3.04 hrs/day, but the 3mo ave on the Summary spreadsheet shows 2.48 hrs/day and 71.90 hrs worked."

Thanks for the tips on updating the other files!

Ah, you're right about the Daily Average formula. For May 1 - May 6, it should be the following in X6, filled down:

=(E6+H6+K6+N6+Q6+T6)/COUNTIFS($C6:$T6,"<>"&"",$C$5:$T$5,"SOLD")

Generally, it should be a sum of the 6 daily "SOLD" values divided by a COUNTIFS of the entries that are both non-blank and in the "SOLD" column. You should be able to group the weekly sheets and do most of them together, but the obstacle you'll face is that not all of your sheets have the SOLD header on the same row, so you may have to do the sheets with headers in row 5 together, then do the sheets with headers in row 4.

Looking at Ernesto's previous 13 weeks prior to today would start with the week of April 10-15 through the week of July3-8, for a total of 71.9 across 29 days, providing the average of 2.48. Each formula's timing is centered on TODAY, not the last date of non-zero values, so it will always look back 13 weeks from the current date. If you want more flexibility, you could create a changeable TARGET DATE cell in, say... B1, then change the TODAY() parts of the formulas to $B$1. That would allow you to look 4, 13, 26, or 52 weeks back from whatever date is in B1. Perhaps that approach would work better if the data won't always be updated expediently? Using the new approach, you could enter May 29th as the target date and get the 13 prior weeks, returning the 155.3 hrs. for Ernesto in 51 days from your initial search. Check the attachment to see if it's more to your liking:

I don't need to worry about adding the Daily Ave Sold column going backwards. I added it to the Master spreadsheet so when the macro duplicates it each week it will be there going forward. The managers will appreciate having that quick view for the week.

"Each formula's timing is centered on TODAY, not the last date of non-zero values, so it will always look back 13 weeks from the current date." Thank you for pointing that out. The target date is perfect. That solves the problem. Whether they're updated through last week or haven't been updated for several weeks, they can get the correct average. Brilliant.

Thank you again for all your help. This will be very appreciated by many people.
Amy

Sigh. Ok, they're requesting one more change. They said there are some days when the employee works all day on a project but none of the time is billable until the next day when they complete the job. So they are filling in 0 sold on those days, which I had expected that would mean they didn't work that day and it wouldn't count in the averaging.

They want 0 sold days to count in the averaging formulas, but if any text is entered in the sold column for the day, that cell should be excluded without causing a formula error. (Ie they would enter 'school', 's', 'vaca', 'vacation', 'v', 'sick', 'ill', 'off', or a variety of things to note why they didn't work that day, instead of entering a 0, and they don't want it to count against their average.

Is that possible??

I think that will do it. I updated the sample sheet and I'm having them test it out. THANK YOU!!!

The 4 week daily average and total (columns F & G) on the SUMMARY sheet isn't populating. I have a target date of Dec. 12th (B1 on the SUMMARY sheet) with more than 4 weeks of data.

I'm sure it's something silly and I'm going to be embarrased I'm overlooking it, but I don't see it.

Hi John!!

The Infiniti hour tracker sheet I uploaded had a date filled in in B1. I'm not sure why that isn't showing when you downloaded the file. Let me know if you still can't see it with this new version.

I've taken out the old dates that we're not going to fill in, and think I have the tab names matching the dates now for column N forward. (It's not case sensitive is it?)

I took out the -4 offset in the formula in F3 (for the 4 week average), since we have the target date feature now and shouldn't need the offset anymore. I was hoping that might fix it since I was told you can't have a negative offset, but it didn't make a difference.

I don't understand the current formulae which use LEFT/RIGHT/SEARCH functions.
Try

in F3

=IFERROR(G3/SUM(IFERROR(OFFSET($M3,0,MATCH($B$1,$M$1:$WK$1,1)-1,1),0)),0)

in G3

=IFERROR(SUM(OFFSET($M3,0,MATCH($B$1,$M$1:$WK$1,1)-1,1,-4)),0)

See attached.

Is the 'right' function is for the offset? I don't see 'left' or 'search' in the formulas. We can drop that since we added the 'target date' feature. Before I had a problem because the workbook automatically adds a new tab at the beginning of the week. Since the days hadn't been worked yet, it was averaging in the current week (with no hours worked), and the past 3 weeks with data for the 4 week average. We offset it so each average formula on the Summary sheet would look back one week further and return the correct average.

We also set it up so any cell left blank or with text would not be included in the average. Only days with a number filled in would be counted. We didn't want to bring the averages down by including days not worked (holidays, vacation, training, sick, usual day off, etc). That's important.

I don't see 'left' or 'search' in the formulas

Look at formulae in row 4: I only "corrected" the 2 highlighted formulae.

Oh, I see it in the total hours column. Not sure what that was supposed to do.

Do the formulae in #18 do the job?

Sorry, I took a few days off for Christmas. I hope you had a nice holiday if you celebrate.

Unfortunately no. I set the target date to 12/10: the total hours is correct but the daily average is wrong. It's showing 3.76 hrs/day ave. However, there were 21 days worked during those 4 weeks, so the daily average should be 8.51 (178.7/21). The formula should only include days if there is a number in the SOLD column indicating that the employee worked).

Thank you JeteMc. That's progress!

The averages are correct now based on the last full week completed, but it doesn't seem to be based off of the target date in B1. For example, when I change the date from 12/10 to 12/13, none of the averages change. It would be nice to see the average based on a chosen date.

What's important is the function. (I'm not sure what appearance you were referring to?) The goal is to return the daily average of hours sold over certain periods (4 weeks, 3 mos, 6 mos, and 1 year), excluding days with empty SOLD cells or with text entered in the cell (days not worked may be left blank or the reason may be noted- ie holiday, sick, training, etc). We don't want to reduce the average by including days they didn't work.

(As a side note, I think the offsets were added to correct the time period being looked at before the target date feature was added. The formula was including the current week, which is automatically added to the spreadsheet at the beginning of the week, but is blank because it hasn't happened yet. That was throwing the averages off because it was returning a 4 week average based on only 2 or 3 weeks of data (including the current blank week and last week, which may or may not have been filled in already), and returning a low 4 week average. The target date was added to solve this problem. If all data is entered through yesterday, you can set the target date to yesterday. If data hasn't been updated for weeks, you can set the target date to the last date updated.)

Thank you again for your help and Happy New Year!!

I see what you mean. Is there a way to make the averages and totals on the SUMMARY tab calculate according to the target date like you did on Sheet 1? You can take out anything in the formula that may not be needed. Thanks again!

Thank you JeteMc! CAntosh set up the target date and I just realized you have to change the date by a week or more, not 3 days like I was trying to do. Everything is working now. I am SO happy. This has been a long project and I could never have done it without everyone on this forum. I hope everyone has a [I]wonderful[I] 2018!

Sorry, I just realized I misunderstood your reply earlier. Thanks again!!!