two part function to output into separate cells?

Hi,

Im using Numbers 4 to fill in my time sheet. I've got formulas set up to do most of my calculations, but one is still giving me trouble.


I have three totals, Total Hours (all hours I was at work), Regular hours (all hours minus breaks), Overtime hours (all hours over 40 per week).


I have it set up where total hours and total break time is already added up for me. I now want formulas to do the rest.


Im looking for a formula that will subtract break hours from total hours, and output the result only if less than 40. if greater than 40, I want it to output 40, and output the excess into another cell.


I have figured out how to have the regular hours cell output anything less than 40, and if greater to output 40, but I do not know how to get the amount over the limit to output to another cell.


I am doing that first part like this:


IF((B2−C2)<40, B2−C2, 40)


so, if:

B2=43 and C2=4, RESULT=39

B2=43 and C2=2, RESULT=40


In that second example, how would I get that extra 1 to output to another cell?


Thanks in advance!

MacBook Pro (15-inch, Late 2016), macOS High Sierra (10.13.3)

Posted on Jan 31, 2018 1:22 PM

Reply

Similar questions

4 replies

Jan 31, 2018 2:11 PM in response to ajones138

Nevermind, I figured it out!

I don't use spreadsheets a whole lot, so I was missing one key concept in my logic; formulas go into the cell where the result is displayed. With this in mind, I used a variation of the formula in my original question, in the cell where I wanted to excess over the limit displayed.


IF((B2−C2)<40, 0, B2−C2−40)


so, if:


B2=43 and C2=2, RESULT=1


Thanks ajones138.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

two part function to output into separate cells?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.