MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How To Calculate Only Positive Values In a Row With Infrequent Cells In a Row?

Hello, I only want to add positive values in a cell and not a negative value in a row. But the cells I want to total in the row are every 5. Is there a formula that can do this? Many thanks

I want to find the amount of overtime hours we are paying each week to employees and I want a monthly total at the end of the row for each employee. How I have set the spreadsheet up to get the O/T value is:
F4(Actual Hrs) – B4(Contracted Hrs) = E4(O/T Hrs) That formula is for one employee for the one week, so there are another 3 or 4 formulas going across the rows for the month for the employee. The trouble is when I get the final formula for the end of the month it is calculating the negative values ie if the employee hasn’t reached their contracted hours as well as the positive values ie if they have gone over their contracted hours (which I want to know) Whatever formula I put in at the end it never picks up only the positive values eg, e4+k4+q4+w4+ac4+ai4,”>0″e4+k4+q4+w4+ac4+ai4

Comments: 2 Responses to “How To Calculate Only Positive Values In a Row With Infrequent Cells In a Row?”

    2 years ago

    I don't understand why the values are in every fifth row. Every row should be one record of data. Why are 4 rows skipped?

    If you have E4 contains the number of overtime hours, instead of it being F4-B4, make it MAX(F4-B4,0). So it will be 0 if the number is negative. Then just use SUM(E) to get the total.

    2 years ago

    Thank you Gary, you have solved my problem and saved me many hours. Many thanks. PS I meant columns not rows.

Comments Closed.