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.

  • Simple and elegant. – Kelly J Andrews 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. – Adam Spriggs Jul 24 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.

  • This will certainly work in other scenarios, but my situation involves sending content in an email (through ExactTarget), so the javascript won't execute – ampersandre 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. – Kelly J Andrews 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

  • 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.