Excel: Complicated ROUNDUP / ROUNDDOWN issue

Hi All, In my latest Excel 2010 workbook I have the following formula: =IF(L13=0,0,IF(F13="Standard",L13,IF(F13="50%",(LEFT(L13,FIND(" ",L13&" ")-1)/2&" "&RIGHT(L13,LEN(L13)-FIND(" ",L13))),IF(F13="x2",(LEFT(L13,FIND(" ",L13&" ")-1)*2&" "&RIGHT(L13,LEN(L13)-FIND(" ",L13))),IF(F13="x3",(LEFT(L13,FIND(" ",L13&" ")-1)*3&" "&RIGHT(L13,LEN(L13)-FIND(" ",L13))),"None"))))) It is basically looking for a rule in F13 and applying it to L13. Now, L13 contains a number, a space then a word which is why I have the LEFT and RIGHT parts in there. The "Standard", "x2", "x3" and "None" rules are all working great. So far so good. I then run into issues with the "50%" rule where the value is divided by 2. My problem is if the divided number results in a decimal less than 1 I need it to ROUNDUP, but if the divided number results in a decimal above 1, I need to ROUNDDOWN. As you can imagine the inclusion of text in this cell is making life complicated and I am unsure how to go about adding this caveat into my formula. Anyone out there got a any clever suggestions that may help me out here please?

This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of June 09, 2015.

For more resources for Microsoft Excel