Sarah has attended:
Excel Advanced course
Multiple IF formulas
Hi,
I'm having trouble with a formula.
I have a spreadsheet that I run, to log overdue items by number of hours they are late by.
If the item I'm waiting on is overdue by 4 or more hours, then I want a statement to automatically populate in the "results cell", to say "4+ hours late". The same applies for items overdue by between 0.01minute and 2 hours, and between 2 hours and 4 hours, with corresponding statements (i.e. "between 2-3 hours late" and "up to 2 hours late").
All I want to have to do, is to log the time the item landed on my desk, and let the formula work out how late it is, and give the correct statement depending on how late.
I have therefore entered the following formula in order that the appropriate text be displayed depending on how overdue a particular item is:
=IF(D74>=C74+4, "4+ hours late", IF(D74>=C74+2, "2-3 hours late", IF(D74<=C74,"on time", IF(D74>=C74, "up to 2 hours late"))))
However, the formula is missing out the 2nd part of the IF formula (between 2-3 hours late). The 1st part works perfectly, as does the last part, but for anything that should be between 2-3 hours late, the formula is returning "4+ hours late" which is wrong.
Why is it skipping the 2nd part?
Please help!!
Thanks.
RE: Multiple IF formulas
P.S: Cell 'D74' is the cell where I type the time the item lands on my desk.
Cell 'C74' is the set time that the item should have been submitted by. These times are fixed, and have been entered into my spreadsheet in these cells.
RE: Multiple IF formulas
Hi Sarah.
I think the problem lies with the format of the C74 cell. How do you enter the time into it?
Alan Burbridge
Best STL
RE: Multiple IF formulas
I can see nothing wrong with your IF statement logically.
What number format are the values in C74 and D74?
Regards
Laura
RE: Multiple IF formulas
Hello Sarah,
My name is Greg and I am one of the trainers at Best.
From the formula you have given us, with nested If statements, you have to give a value for when all your conditions are not met - ie what happens when D74 is not >=C74+4 and when D74 is not >= C74 +2 and when D74 is not >=C74. In this case (correct me if I am wrong), you want to specify "On Time" is neither of the conditions are met.
Therefore the formula is:
=IF(D74>=C74+4,"4+hours late",IF(D74>C74+2,"2-3 hours late",IF(D74>=C74,"up to 2 hours late","On time"))). Please try this and see whether it works to your satisfaction.
I hope this answers your question. Please do not hesitate to contact us if you have any further questions.
Greg Eze
