i need help with a calculation formula...
 

if E1:E12 (each) cell is equal to more than 9.5, I need L12 to equal the
cells minus 9.5 - i'm trying to get my worksheet to calculate overtime hours
for our employees

for example E1 is 10.5, E2 is 9.5 , E3 is 10...I need L12 to show 1.5 in
overtime hours. I'm not sure is excel can calculate this way, but if it can
I would surely appreciate any input!

Enter this in L12 (this is an array formula so enter with Ctrl+Shift+Enter):

=IF(COUNTIF(E1:E3,""&9.5)0,SUM(E1:E3-9.5),"")
--
Regards,
Dave


"moegecko" wrote:

if E1:E12 (each) cell is equal to more than 9.5, I need L12 to equal the
cells minus 9.5 - i'm trying to get my worksheet to calculate overtime hours
for our employees

for example E1 is 10.5, E2 is 9.5 , E3 is 10...I need L12 to show 1.5 in
overtime hours. I'm not sure is excel can calculate this way, but if it can
I would surely appreciate any input!

A non array option

=SUMPRODUCT(--(E1:E39.5),E1:E3-9.5)

hth RES