Search

Friday, March 23, 2012

Excel Cell Search & Calc

Thanks to my buddy Ron for this challenge...

The Excel Formula below is handy when you want to search for a specific value contained within an adjacent cell and then do a calculation of time that rounds up to the nearest quarter hour.  This formula includes error trapping if the searchable cell contains a null value.


=IFERROR(CEILING(IF(OR(ISNUMBER(SEARCH("*R*",H2)),ISNUMBER(SEARCH("*A*",H2))),AD2,IF(ISNUMBER(SEARCH("*C*",H2)),AD2*DataRef!$B$1,IF(ISNUMBER(SEARCH("*I*",H2)),AD2*DataRef!$B$2,""))),0.25),"")