Using if function to calculate normal hours

[sanjeshni]

HI

I need help in using IF function. according to the worksheet, normal hours is 8 hours per day Monday to Friday. can anyone help me with the function to calculate normal hours in 1 cell.

thanks
sanjeshniNORMAL HOURS.xls

[Tony Valko]

Maybe this...

=MIN(8,N(B3))+MIN(8,N(C3))+MIN(8,N(D3))+MIN(8,N(E3))+MIN(8,N(F3))

Copy down as needed.

[dredwolf]

Or this in I3:

=SUMPRODUCT(((B3:F3>=8)*8)+((B3:F3<8)*B3:F3))

copied down...

[sanjeshni]

thanks, it works. but my requirement is to use a IF function. can a solution be available on that??

[Tony Valko]

thanks, it works. but my requirement is to use a IF function.

Here's an example that contains IF functions.

Array entered**:

=SUM(IF(B4:F4>=8,8,B4:F4))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[sanjeshni]

thanks. got it

[Tony Valko]

You're welcome!

[dredwolf]

Is this a classroom assignment ? IF so, this sites policy is to NOT give solutions, just advice..., but if you understand both the proffered solutions, neither is hard to change int an if statement...