Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding IF statement?
Hello,
I'd like to setup a roundup or rounddown if statement stating: If the value of A1 is x.1 to round up to the next whole number; ie (if A1 = 2.1 to round up to 3) but if A1 is x.0x to round down to the next whole number; ie ( if A1 = 2.09 to round down to 2) Is this at all possible? TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding IF statement?
try this...
=IF(MOD(A4,1)<0.1,ROUNDDOWN(A4,0),ROUNDUP(A4,0)) -- HTH... Jim Thomlinson "Matt" wrote: Hello, I'd like to setup a roundup or rounddown if statement stating: If the value of A1 is x.1 to round up to the next whole number; ie (if A1 = 2.1 to round up to 3) but if A1 is x.0x to round down to the next whole number; ie ( if A1 = 2.09 to round down to 2) Is this at all possible? TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding IF statement?
Hi Matt,
This will work but there's probably a daintier way to do it. =IF(A1-INT(A1)<0.1,ROUNDDOWN(A1,0),ROUNDUP(A1,0)) HTH Martin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding IF statement?
=INT(A1+.9)
Regards, Bernd |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding IF statement?
Bloody Hell Bernd,
I know I said "daintier", but that is just downright immoral!! Martin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding IF statement?
LOL
thanks everyone! They all seem to be doing the trick. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding IF statement?
How precisely do you need to recognize the boundary condition? Excel works
in binary, and the decimal fraction .1 has no exact representation in binary, so writing formulas that will always to the right thing at the boundary is not trivial. Jim and Martin's solutions round down in many case where they should round up, such as 4.1. Bernd's solution rounds up in many cases where it should round down, such as 0.0999999999999999. I think that =IF(A1-(INT(A1)+0.1)<0,ROUNDDOWN(A1,0),ROUNDUP(A1,0)) will always correctly recognize the boundary. Jerry "Matt" wrote: Hello, I'd like to setup a roundup or rounddown if statement stating: If the value of A1 is x.1 to round up to the next whole number; ie (if A1 = 2.1 to round up to 3) but if A1 is x.0x to round down to the next whole number; ie ( if A1 = 2.09 to round down to 2) Is this at all possible? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL concatenation statement | Excel Discussion (Misc queries) | |||
SET statement tutorial | Excel Discussion (Misc queries) | |||
If statement w/ rounding but not all numbers | Excel Worksheet Functions | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions |