multiple if formulas

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Multiple IF formulas

Multiple IF formulas

resolvedResolved · Medium Priority · Version 2003

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

Hi Alan,

The times are entered as : 13:49:00 (for example)
The formatting of the cells appears to be as "custom; hh:mm"

This is the same for both C and D columns.

Thanks,
Sarah.

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


 

Excel tip:

Wrapping Text in a Cell in an Excel 2010 Workbook

When you have a lot of text you want to put in a particular cell but you can't decrease the font size to fit because the text will then become ineligible, then manually wrap the text in a cell by simply pressing ALT+ENTER.

View all Excel hints and tips


Server loaded in 0.07 secs.