Calculating an average but excluding boxes with text

Hi all,

I'm trying to get excel to calculate the average time spent on appointments by officers in a small team. Each of them sees people each day, and as we're very busy I'm eager to get the time spent on each appointment down, by giving them a target of the number of people to see each day. At the moment we gather some stats on this, which show how many people are being seen by each officer.

The attached example sheet shows for one week a list of columns with officer's initials at the top, and says how many people were seen by each person each day during that week and then a how many people overall for the week.

I'd like to be able to generate statistics on the amount of time each officer has spent on average with customers, and base this on the fact that people have 360 minutes per day (they do 6 hours of appointments and another hour for admin) to see people.

I think the spreadsheet attached gives a good idea of the system and what I want to do.

Any help would be really greatly appreciated!

all the best

Km

Sorry, forgot to attach!

Not quite what I'm after, but thanks for trying!

If you take a look at the spreadsheet, what I'm looking for isn't a simple average excluding text. The column with the numbers in it, and occasion text instead of numbers, isn't being averaged, as I have a box showing the total for that column at the bottom. I just want to make sure that days where people aren't working full 360 minute days aren't being used, so I want to muliply 360 X number of boxes containing a number (i.e. 4 if one of those 5 working days contains text instead of a number), then divide that by the total number of people they saw that week.

This will give me the average time spent with customers, on days when the officer was working - and excluding days they weren't, or days where they were on half a days work. If I included those results it would skew the results. This means I can't simply do a calculation that assumes every week is a five working day week. Hence my problem.....

Ok, so sue has two values, day 1 = 10. Day 2 = 5. I'm not interested in the average number of people she has seen on those two days, which as you rightly point out would be 7.5.

I'm interested in the average time she spends with each person.

I know that on day 1 and day two she has 360 minutes per day to spend with customers, so 720 minutes overall. I therefore know that if I wanted to find out how long on average she was spending with customers, i.e. how many people she is trying to fit into her 720 minutes of appointments time, I would calculate as follows;

each day counts as 360 minutes. there's two days above, so that's 2 x 360 = 720.

I then need to divide the total number of minutes, 720, by the total number of people seen in those two days, to give me the number of minutes spent on average with each customer. In the case above, we have 720 minutes divided by 15 people, which equals 48 minutes per person.

This is fine, but I want to be able to do this calculation for each officer and for their total average/running average for a week. The problem therefore is that if an officer has been on annual leave for three days, and this is in the columns instead of a number (e.g. see columns with 'AL' entered), I can't just assume that every working week is 5 working days X 360 minutes, i.e. 1800 minutes, divided by the total number of people seen. I need to be able to discount the days where they weren't in, so I can't use 1800 minutes to divide by, but instead need to get Excel to figure out how many minutes were worked, and then divide by the total number of customers they actually saw.

What I need is something which can say 360 X number of days worked (I think the way to do this is to get Excel to count the boxes in the range B5:B10 for example, but to exclude the boxes which have a letter in them as opposed to a number) Divided by the total number of customers seen.

If you look at the spreadsheet, I have all the info there, including a 'total number of customers seen in a week', just not the code to calculate the number of minutes to divide by.

Hope that makes sense!

Nearly there!

There's a slightly easier way to do this though; B11: =360*COUNT (B5:B9) / B10 (or whatever cell contains the total for that column - see spreadsheet)

However, whilst this counts the number of boxes between B5 and B9, it doesn't discount the ones which don't have a number in.... in fact, we always know that a count of B5:B9 will equal 4, even if we drag that formula across to other cells to do the same for the C column. So I could replace this formula with =(4*360)/B10. But it wouldn't give me the info I'm after....

I need a formula which goes more like B11: =360*COUNT(B5:B9....but with some code to avoid the cells in this range with just letters!) / B10

See what I mean?

Ok, your code which calculates the Number of Days worked, assumes they worked every day in cells B5:B9. Sue didn't! - look at the cells B5:B9. Some of these have 'AL', which means annual leave. Doing COUNT(B5:B9) will give 5, I accidentally said 4 earlier. But she didn't work 5 days that week, she only worked 2.

If I use your code for Sue's calculation, it will say that 360*5 (the number of days between B5:B9, but not the number of days actually worked because it doesn't exclude the AL days) /B10 .... will equal 120, therefore giving the impression that Sue spent 120 minutes with each customer that week. This isn't accurate, as she only worked two days and saw 10 and 5 people respectively on each day. The rest of the week she was on annual leave and saw nobody, but it wouldn't be fair to include her annual leave days when calculating how much time she had to see people!

Actually, Sue spent 48 minutes with each person. This is because 360*2 (the number of days she worked between B5 and B9, excluding AL days), is 720, and 720/B10 is 48.

The results for SW, who worked five days, is 62 mins per customer, for KG, who worked four of days, is 38 mins per customer. For BMT, who worked four days, she spent 40 minutes with each customer, for LR she worked four days and spent 240 mins with each customer as she only saw 6 people over the course of that four day week.

I think what I'm looking for is a COUNTIF function that only counts the days between B5 and B9 if they're numbers, or higher numbers than zero.

Does that make sense?

****, I just spent all that time writing a response and someone has clarified that Ron's response is actually correct.

Doh!!! thanks muchly. I think this should do it :-/

my bad, sorry.