Formula

[Guest]

Need to "ceiling" or floor negative numbers alongwith positive numbers.
Trying the following but does not work. Any help.
=IF(MOD('Data Input'!G9,5)>=3,CEILING('Data Input'!G9,5),If(MOD('Data
Input'!G9,5)<3,FLOOR('Data Input'!G9,5)),If(MOD('Data
Input'!G9,5)<=-3,Ceiling('Data Input'!G9,5),If(MOD('Data
Input'!G9,5)>-3,FLOOR('Data Input'!G9,5))))

 

[Guest]

Hello havovie,

It's not normally a good idea to describe what you want to do by means of a
formula which doesn't work!

Your first two IF functions cover all possibilities so the second two will
never be actioned, also both MOD and CEILING can't return negative results
unless the second argument is negative.

What do you have in 'Data Input'G9, just integers? or any number? Perhaps
you need something like

=ROUND('Data Input'!G9/5,0)*5

If that doesn't work then perhaps give 7 or 8 representative examples, e.g.
what should be returned when 'Data Input'!G9 contains -7, -5, -4, -2.75, 0,
2, 2.8, 4 etc.

 

[Guest]

Hi Daddylonglegs. Thanks for the infomation.

Usually I do not have negative nos and use the formula :

=IF(MOD('Data Input'!G9,5)>=3,CEILING('Data Input'!G9,5),FLOOR('Data
Input'!G9,5))

However, whenever faced with a negative number this formula does not work.
Even though it is rare, there may be occasional negative nos and so want to
solve this formula once and for all.