How to round down at 0 but up at >=1

RudeClown · Aug 6, 2018

I have a question about rounding. The exact numbers displaying in the cell in question will vary depending on the value of other cells, but to cite a specific example, I'll use the following numbers. The formula in the cell is returning $322.8703689...., which is correct. It is displaying $322.88 due to the roundup function I am using, which is a penny off of what I want it to show. I am using roundup, because (using this example) $322.871.... or above should be rounded up. But, $322.870.... should be rounded down to $322.87. Is there a way I can accomplish this? The formula in the cell is:

=IF(OR($B$3="",$B$4=""),"",ROUNDUP(IF(OR($B$3="",$B$4=""),"",IF($B$4="Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/52)*(1+$B$7)),IF($B$4="Bi-Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/26)*(1+$B$7)),IF($B$4="Semi-Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/24)*(1+$B$7)),IF($B$4="Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/12)*(1+$B$7))))))),2))

I appreciate any assistance you can offer!

fhqwgads · Aug 6, 2018

hope this works

Code:

=IF(OR(MID(IF(OR($B$3="",$B$4=""),"",IF(OR($B$3="",$B$4=""),"",IF($B$4="Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/52)*(1+$B$7)),IF($B$4="Bi-Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/26)*(1+$B$7)),IF($B$4="Semi-Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/24)*(1+$B$7)),IF($B$4="Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/12)*(1+$B$7)))))))),FIND(".",IF(OR($B$3="",$B$4=""),"",IF(OR($B$3="",$B$4=""),"",IF($B$4="Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/52)*(1+$B$7)),IF($B$4="Bi-Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/26)*(1+$B$7)),IF($B$4="Semi-Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/24)*(1+$B$7)),IF($B$4="Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/12)*(1+$B$7)))))))))+3,1)="0",MID(IF(OR($B$3="",$B$4=""),"",IF(OR($B$3="",$B$4=""),"",IF($B$4="Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/52)*(1+$B$7)),IF($B$4="Bi-Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/26)*(1+$B$7)),IF($B$4="Semi-Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/24)*(1+$B$7)),IF($B$4="Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/12)*(1+$B$7)))))))),FIND(".",IF(OR($B$3="",$B$4=""),"",IF(OR($B$3="",$B$4=""),"",IF($B$4="Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/52)*(1+$B$7)),IF($B$4="Bi-Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/26)*(1+$B$7)),IF($B$4="Semi-Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/24)*(1+$B$7)),IF($B$4="Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/12)*(1+$B$7)))))))))+3,1)=""),IF(OR($B$3="",$B$4=""),"",ROUNDDOWN(IF(OR($B$3="",$B$4=""),"",IF($B$4="Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/52)*(1+$B$7)),IF($B$4="Bi-Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/26)*(1+$B$7)),IF($B$4="Semi-Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/24)*(1+$B$7)),IF($B$4="Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/12)*(1+$B$7))))))),2)),IF(OR($B$3="",$B$4=""),"",ROUNDUP(IF(OR($B$3="",$B$4=""),"",IF($B$4="Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/52)*(1+$B$7)),IF($B$4="Bi-Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/26)*(1+$B$7)),IF($B$4="Semi-Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/24)*(1+$B$7)),IF($B$4="Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/12)*(1+$B$7))))))),2)))

i was trying to make a logical test to see if the third digit after the decimal was a 0 or blank but it was harder to implement it into your case than i thought. maybe i'm just over complicating things

Code:

=OR(MID(I13,FIND(".",I13)+3,1)="0",MID(I13,FIND(".",I13)+3,1)="")

StephenCrump · Aug 6, 2018

Your formula would be simpler using a lookup:

Try: =ROUNDDOWN(((B3-B5/(1+B7))*B9-B8)*(1+B7)/VLOOKUP(B4,MyTable,2,)+0.009,2)

where:

Book1

A

B

1

My Table

2

Weekly

52

3

Bi-Weekly

26

4

Semi-Monthly

24

5

Monthly

12

Sheet1

RudeClown · Aug 10, 2018

Thanks fhqwgads, that formula seems to work correctly, I have tested it with several examples, and has returned the desired answer on all of them, including the example I was having trouble with. I will test further to make sure, but thank you!!!!

joeu2004 · Aug 10, 2018

RudeClown said:
The formula in the cell is returning $322.8703689...., which is correct. It is displaying $322.88 due to the roundup function I am using, which is a penny off of what I want it to show. I am using roundup, because (using this example) $322.871.... or above should be rounded up. But, $322.870.... should be rounded down to $322.87. Is there a way I can accomplish this?

KISS! In general, ROUNDUP(ROUNDDOWN(expression,3),2).

RudeClown said:

The formula in the cell is:

Rich (BB code):

=IF(OR($B$3="",$B$4=""),"",ROUNDUP(IF(OR($B$3="",$B$4=""),"",
IF($B$4="Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/52)*(1+$B$7)),
IF($B$4="Bi-Weekly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/26)*(1+$B$7)),
IF($B$4="Semi-Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/24)*(1+$B$7)),
IF($B$4="Monthly",(((((($B$3-$B$5)+($B$5-($B$5/(1+$B$7))))*$B$9)-$B$8)/12)*(1+$B$7))))))),2))

I don't believe your ROUNDUP expression works in the first place, because ROUNDUP("",2) returns an error.

On the other hand, the red-highlighted part is superfluous; you cover it with the blue-highlighted part below.

I suspect your formula can be greatly simplified, if only by eliminating unnecessary parentheses. Perhaps:

Rich (BB code):

=IF(OR($B$3="",$B$4=""),"",
ROUNDUP(ROUNDDOWN((($B$3-$B$5+$B$5-$B$5/(1+$B$7))*$B$9-$B$8)/
LOOKUP($B$4,{"Bi-Weekly","Monthly","Semi-Monthly","Weekly"},{26,12,24,52})*(1+$B$7),3),2))

The string constants in the first parameter must be in alphabetical order.

How to round down at 0 but up at >=1

RudeClown

Board Regular

Excel Facts

fhqwgads

Board Regular

StephenCrump

MrExcel MVP

RudeClown

Board Regular

joeu2004

Banned user

Similar threads

Forum statistics

Share this page

How to round down at 0 but up at >=1

RudeClown

Board Regular

Excel Facts

fhqwgads

Board Regular

StephenCrump

MrExcel MVP

RudeClown

Board Regular

joeu2004

Banned user

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