9

I'm doing a bit of math with AMPscript, and I'd like to be able to round a number DOWN

eg: I would like 5.5, 5.6, 5.7 etc.. to become 5, not 6

The standard FormatNumber() function will automatically round a number UP if the decimal point is greater than or equal to .5

I've tried finding a Floor() function or some option to FormatNumber() that will yield the rounded-down number, but I haven't had any luck finding a solution.

Does anyone have a suggestion for obtaining the floor value of a number?

10

You can still use FormatNumber-- just subtract 0.5 from the number before feeding it into FormatNumber. Something like this would probably work:

FormatNumber(@OriginalNumber-0.5, "N0", "en-US")

Since you subtract 0.5, a number such as 1.99 becomes 1.49 which would round down, while 2.49 would become 1.99 and round up.

4
  • Simple and elegant. Dec 2 '14 at 0:41
  • Impressive. The force is strong with this one!
    – Timothy
    Dec 2 '14 at 15:28
  • 1
    this works: Set @rounded = FormatNumber(Subtract(@OriginalNumber,.5), "N0", "en-US")
    – Snowalker
    Dec 7 '17 at 14:36
  • @Snowalker's answer is correct. You have to use the subtract function in AMPscript. Jul 24 '19 at 13:44
3

The other answers are ok, but I would like to add mine as it still uses FormatNumber and also supports negative numbers.

%%[ 
set @number = '4.500000001'
set @mod = Mod(@number, 1)

if @mod > .5 then 
    set @roundedDown = FormatNumber(Subtract(@number,1), 0)
elseif @mod < 0 AND @mod > -.5 then // handle negative values
    set @roundedDown = FormatNumber(Subtract(@number,1), 0)
else
    set @roundedDown = FormatNumber(@number, 0)
endif
]%%

Original Value: %%=v(@number)=%%
<br />
Rounded Down: %%=v(@roundedDown)=%%
3

Another thought. You could use server side javascript instead of AMPscript, and just use math.floor.

2
  • This will certainly work in other scenarios, but my situation involves sending content in an email (through ExactTarget), so the javascript won't execute Dec 1 '14 at 19:25
  • 1
    @ampersandre - Server Side Javascript is available to you in email sending. See help.exacttarget.com/en/documentation/exacttarget/content/… for more info. Use of SSJS in email sends is extremely useful, however, more in-depth data retrieval can slow down sending. MATH.floor would not cause any harm here. Dec 2 '14 at 0:36
0

This may seem a little hack-ish, but I would do it like this.

%%[ 
SET @DynamicField1 = 127.35
SET @DynamicField2 = Substring(@DynamicField1, 1,  Subtract(IndexOf(@DynamicField1, "."), 1) )
]%%

@DynamicField2 now is the floor. Good luck

1
  • I changed 127.35 to 127.51 and it results into 127 instead of 128
    – Snowalker
    Nov 22 '18 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.