Complicated ROUNDUP / ROUNDDOWN issue

Fishboy · Jun 9, 2015

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?

gaz_chops · Jun 9, 2015

When you say "decimal less than 1" do you mean 1 decimal place? Maybe post a couple of examples showing the results you expect.

Fishboy · Jun 9, 2015

gaz_chops said:
When you say "decimal less than 1" do you mean 1 decimal place? Maybe post a couple of examples showing the results you expect.

Hi gaz_chops, thanks for your response. To clarify what I meant by "decimal less that 1" was a result of 0.1 to 0.9. I have included a grab of what I am working on below:

Excel 2010

B

C

D

E

F

G

H

I

J

K

L

M

N

6

7

Encounter Level:

L16

Roll Outcome

Loot Rule

Raw Loot Outcome

Modified Loot Outcome

8

L16CoinsRange

L16CoinsRule

L16CoinsOutcome

9

Coins:

x3

37

1d12 1,000 gp

5000 gp

15000 gp

10

L16GoodsRange

L16GoodsRule

L16GoodsOutcome

11

Goods:

None

63

4d6 Gems

17 Gems

None

12

L16ItemsRange

L16ItemsRule

L16ItemsOutcome

13

Items:

50%

84

1d3 Medium

1 Medium

0.5 Medium

14

Generator

In the above example the formula is getting the rule from F13.
It is applying the rule to L13.
The output of this rule is in N13.

This is the formula in N13:

=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")))))

Currently N13 is showing as "0.5 Medium" - which I would like to ROUNDUP to "1 Medium"
If however N13 was showing a decimal answer greater than 1 (i.e "1.1 Medium" or higher), I need this to ROUNDDOWN to "1 Medium"

So to recap, 0.1 to 0.9 needs to ROUNDUP and include the word from the end.
1.1 and above needs to ROUNDDOWN and include the word from the end.

I am sorry if this isnt making any sense, if so just let me know and I will try and rephrase the request into something easier to understand.

gaz_chops · Jun 9, 2015

As an example is this correct

Code:

[TABLE="width: 130"]
<tbody>[TR]
[TD="width: 65, align: right"]0.5[/TD]
[TD="width: 65, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0.7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2.2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2.7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3.25[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

MarcelBeug · Jun 9, 2015

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

Code:

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

This might work, depending on your data.

gaz_chops · Jun 9, 2015

Similar idea

=MAX(1,FLOOR(A1,1))

=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 · Jun 9, 2015

gaz_chops said:

As an example is this correct

Code:

[TABLE="width: 130"]
<tbody>[TR]
[TD="width: 65, align: right"]0.5[/TD]
[TD="width: 65, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0.7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1.9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2.2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2.7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3.25[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Hi again gaz, the above table is correctly handling the decimals, yes.

gaz_chops · Jun 9, 2015

Fishboy said:
Hi again gaz, the above table is correctly handling the decimals, yes.

Then try my last formula.

Fishboy · Jun 9, 2015

gaz_chops said:
Similar idea

=MAX(1,FLOOR(A1,1))

=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")))))

I have just tried replacing my formula with the bold part from the quote above, however I get the following error when I press enter:

"The formula you typed contains an error. For help...yadda yadda yadda"

I didn't know what the =MAX(1,FLOOR(A1,1)) was or where it would go. Was I wrong to only use the bottom part of your post?

gaz_chops · Jun 9, 2015

Fishboy said:
I have just tried replacing my formula with the bold part from the quote above, however I get the following error when I press enter:

"The formula you typed contains an error. For help...yadda yadda yadda"

I didn't know what the =MAX(1,FLOOR(A1,1)) was or where it would go. Was I wrong to only use the bottom part of your post?

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")))))

Complicated ROUNDUP / ROUNDDOWN issue

Fishboy

Well-known Member

Excel Facts

gaz_chops

Well-known Member

Fishboy

Well-known Member

gaz_chops

Well-known Member

MarcelBeug

Well-known Member

gaz_chops

Well-known Member

Fishboy

Well-known Member

gaz_chops

Well-known Member

Fishboy

Well-known Member

gaz_chops

Well-known Member

Similar threads

Forum statistics

Share this page

Complicated ROUNDUP / ROUNDDOWN issue

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock