Please help with an "if" question
This is a discussion on Please help with an "if" question within the iWork Forum forums, part of the iPad App Store category; Hello all
I apologies if this is a simple question regarding formulas. I am new to "numbers" and have never used excel. I need to ...
Please help with an "if" question
I apologies if this is a simple question regarding formulas. I am new to "numbers" and have never used excel. I need to write a formula that will do this:
John works 9 hours at $15/per hour. If John works more than 5 hrs but less than 8 he takes a half hours break (not paid) if he work more than 8 hours he takes an hour break (not paid for)
I need a cell to calculate his pay at the end of his shift.
I am learning as fast as I can but these "if" formulas are a little tricky for me.
If someone could kindly help me I think I can work out the rest from your example.
Thank you very much
01-02-2012 07:45 PM
It was a bit of a challenge, but fun. Hope I'm not doing someone's homework, because figuring these things out is half the lesson.
Anyway, the illustration is not entirely clear, so here are some things to hold in mind.
The IFERROR is a wrapper in case the basic equation gives back an error. This happens when the Wk Hr cell is empty. I'm not certain but I believe it is the DUR2HOURS function that does this. It is not the usual dived by zero problem, because an entry of zero in the Wk Hr cell clears the error.
You can make the formula simpler by not formatting the Wk Hr column as duration as I did; but that means you have to remember to enter your work time in decimal. 1h 30m = 1.5. If you do use Duration as I did, you have to convert it to a decimal value before you test it or use it to compute the Pay. That makes for a lot of DUR2HOURS functions.
There are probably ways to compare the durations directly, but it looked like they would only add another function without lessening the complication of the final formula.
It requires two IF statements, the second nested in the if-false expression of the first. The first tests for the least case, equal too or less than 5 hr. If it is true you get a simple 15 x hr computation. If false you still have two cases to differentiate, so you need the second if statement. You don't have to test for less than or equal to 5 again, if it were true you'd already computed the answer.
In the second (nested) IF statement you test whether the Wk Hr are less than or equal to 8. If so you subtract the 30 minutes (.5 hours) from the total hours then multiply by 15.
Anything else will obviously be greater than 8 hours, so in the if-false expression of the nested IF statement you subtract 1h from the total and multiply by 15.
I did a casual test to see if the formula worked (which is why I ended up adding the error wrapper), but I did not rigorously test for all possible inputs. For instance, I did not try to see what would happen if I entered a negative number.
Hopefully this plus the illustration should be enough for you to figure out what is going on. Don't miss the manually entered parentheses. They are necessary to get the right results.
Last edited by twerppoet; 01-04-2012 at 10:03 PM.
Reason: Add second attachment for clear view of formula.
By Lend27 in forum iPad General Discussions
Last Post: 03-21-2012, 06:30 PM
By Greygoose_ipad in forum iPad 2 Forum
Last Post: 12-21-2011, 11:06 PM
By Wolfhawk in forum iBooks
Last Post: 10-27-2011, 12:45 PM
By Ayrshire in forum iPad Apps
Last Post: 07-13-2011, 04:54 AM
By High Plains Drifter in forum iPad General Discussions
Last Post: 05-03-2010, 02:28 PM
Search tags for this page
apple numbers nested if statements
how to use if in ipad number
if formula in iwork numbers
iwork numbers if function
nesting functions iworks suite
numbers iworks nesting if conditions
numbers mac using multiple if
writing if statements in numbers ipad
Click on a term to search for related topics.