NON-TEXT VALUES IN CELLS
 

I'm creating a timesheet for technicians in my service department. I have a
team-based system where technicians split the total hours their team does by
the number of techncians on the team, which is 4 per team. It currently
takes the hours in a top cell and automatically divides the hours into the
individual
technicians rows below (e.g., team turns 40 hours in a day, each technician
gets 10). Is there any way to exclude a cell if it contains a non-numeric
value (e.g., "VACATION" or "SICK"), and recalculate by the number of open
cells left?

NON-TEXT VALUES IN CELLS
 

try this array formula
=AVERAGE(IF(ISNUMBER(L3:L14),L3:L14))

--
Don Guillett
SalesAid Software

"Service Manager" <Service wrote in
message ...
I'm creating a timesheet for technicians in my service department. I have
a
team-based system where technicians split the total hours their team does
by
the number of techncians on the team, which is 4 per team. It currently
takes the hours in a top cell and automatically divides the hours into the
individual
technicians rows below (e.g., team turns 40 hours in a day, each
technician
gets 10). Is there any way to exclude a cell if it contains a non-numeric
value (e.g., "VACATION" or "SICK"), and recalculate by the number of open
cells left?

NON-TEXT VALUES IN CELLS
 

I think what you gave me was the opposite of what I was asking (though I
appreciate the response). My spreadsheet colum for a sample team on a sample
day looks like this:

Tech Team A Monday
40
Tech#1 13.3
Tech #2 13.3
Tech #3 VACATION
Tech #4 13.3

Where 40 is the number of hours the team produced that day. The formula is
now set for each tech as the total colum divided by 4. I have to manually
change it to three, two, etc. when one or more members of each team are off.
What I want the spreadsheet to do is see a non-numeric value (in this case
VACATION) in the cell next to tech #3 when I type it in over top of the
formula, an dthen recalculate Techs #1, #2, and #4 accordingly. Is this
possible?

"Don Guillett" wrote:

try this array formula
=AVERAGE(IF(ISNUMBER(L3:L14),L3:L14))

--
Don Guillett
SalesAid Software

"Service Manager" <Service wrote in
message ...
I'm creating a timesheet for technicians in my service department. I have
a
team-based system where technicians split the total hours their team does
by
the number of techncians on the team, which is 4 per team. It currently
takes the hours in a top cell and automatically divides the hours into the
individual
technicians rows below (e.g., team turns 40 hours in a day, each
technician
gets 10). Is there any way to exclude a cell if it contains a non-numeric
value (e.g., "VACATION" or "SICK"), and recalculate by the number of open
cells left?