FORMULA - Estimated vs Actual Time

Laura

FORMULA HELP NEEDED

(See Screenshot)

I need help creating a formula to show:

IF "Actual Time" Column is 10% more than "Estimated Time" Column, then "ERP vs ACTUAL" Column shows "DOWN" Symbol

IF Not, shows "UP" Symbol.

Times are entered in minutes (00.00)

 

Thanks in advance!

 

This is what I currently have:

=IF(AND([Actual Time]1 > [ERP Time]1 * 1.1), "Down", "Up")

It works for the most part BUT it doesnt show the "down" symbol if the "Actual Time" is exactly 10% greater than "ERP Time"

Adam Overton

Change you condition to use >= instead of just > and then exactly 10% will match.

 

Laura

Thanks Adam! Only problem here is, it now turns red when its Blank. How can I prevent that? 

Mike Wilday

Try this: 

=IF(Isblank([Actual Time]1),"", IF(AND([Actual Time]1 > [ERP Time]1 * 1.1), "Down", "Up"))

Mike Wilday

Even better, try this: 

=IF(OR(ISblank([Actual Time]1),ISblank([ERP Time]1)),"", IF(AND([Actual Time]1 > [ERP Time]1 * 1.1), "Down", "Up"))

That way the field will be blank if you are missing time in either column.