Forum Discussion
JessMo
Jul 06, 2022Copper Contributor
breaking down number according to defined parameters
I'm working on a spread sheet to track employee hours and earnings for a summer program. In columns B through W, I want to enter each employee's weekly hours, and have those hours separated out by f...
HansVogelaar
Jul 06, 2022MVP
In row 2, the hours up to 20 are =MIN(B2,20) and the hours over 20 are =MAX(B2-20,0)
To sum the hours up to 20 in B2:W2: =SUM(IF(B2:W2<20,B2:W2,20))
And the hours over 20 in B2:W2: =SUM(IF(B2:W2>20,B2:W2-20,0))