Fishboy
Well-known Member
- Joined
- Feb 13, 2015
- Messages
- 4,267
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?
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?