Complicated ROUNDUP / ROUNDDOWN issue

[Fishboy]

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?

 

[Fishboy]

Sorry, i think i put one of the brackets in wrong! Try

=IF(L13=0,0,IF(F13="Standard",L13,IF(F13="50%",(MAX(1,FLOOR(LEFT(L13,FIND(" ",L13&" ")-1)/2,1))&" "&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")))))

Aha! This is spot on mate. Many thanks for your help.

 

[gaz_chops]

You're welcome, Marcel's solution would also provide the same result.

 

[Fishboy]

Just a wild shot: how about always using ROUNDDOWN and use MAX for a minimum result of 1, like

... MAX(1,ROUNDDOWN( ...

This might work, depending on your data.

Thanks Marcel, although I ended up using gaz_chop's solution I still wanted to show my appreciation for your advice.

 

[gaz_chops]

Just one thing I noticed, the "50%" won't work properly as it is text, try changing it to just 50%, like

=IF(L13=0,0,IF(F13="Standard",L13,IF(F13=50%,(MAX(1,FLOOR(LEFT(L13,FIND(" ",L13&" ")-1)/2,1))&" "&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")))))

 

[Fishboy]

Just one thing I noticed, the "50%" won't work properly as it is text, try changing it to just 50%, like

=IF(L13=0,0,IF(F13="Standard",L13,IF(F13=50%,(MAX(1,FLOOR(LEFT(L13,FIND(" ",L13&" ")-1)/2,1))&" "&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")))))

Hi gaz,

Actually the "50%" as text is not an issue as the IF statement relating to it is only checking this value is TRUE before dividing L13 by 2. The divide by 2 rule is exactly that, unaffected by percentage. If I remove the quotation marks from around the 50% the formula stops working.

 

[gaz_chops]

Hi gaz,

Actually the "50%" as text is not an issue as the IF statement relating to it is only checking this value is TRUE before dividing L13 by 2. The divide by 2 rule is exactly that, unaffected by percentage. If I remove the quotation marks from around the 50% the formula stops working.

Ah your value in F13 must be text, I entered it as .5 (50%) and it wouldn't work.

As long as it works .......

Gaz