Square Import - money( rounding


#1

Hi,

I’m dealing with Square transaction exports. Exported fields are limited to groups, any one of which doesn’t include all the data I need.

I’m compromising and having to calculate the merchant fee on my own.

I’m using val(money(«Net Sales»*.0275)).
The issue is if Net Sales - 5,910.00, the result of «Net Sales»*.0275 is 162.525.

The money function converts this to 162.52 and Square actually took 162.53.

Can someone show me a different function to use or a formula which will round up if it ends in 5 as Square is doing?

Thanks,
Jeff


#2

I would just add a very small number to tip the scale.

«Net Sales» * .0275 + 0.0000001

instead of

«Net Sales» * .0275

#3

I think the round( function can replace both the money( and val( functions,

round(«Net Sales»*.0275) = 162.53

#4

I thought of that. The problem is that the round( function is doing floating point math. The rounding errors you typically see there could still tip the scales the wrong way. I still think you need to add a tiny fudge factor to be sure.


#5

Thanks, Gary

I had tried that but as you have written it, round(«Net Sales».0275), I get an error " Incorrect number of parameters for the round( custom function". I had used round(«Net Sales».0275,2) but it gets rid of the decimal


#6

It should be

round(«Net Sales»*0.0275,0.01)

You are rounding to the nearest multiple of 0.01


#7

Oops, I forgot to add the step parameter:

round(«Net Sales»*.0275,0.01) = 162.53

#8

Super, thanks Gary and Dave. And hello after so many years. : )

Jeff


#9

I noticed a few days ago when you renewed your Panorama X account, and meant to drop you a note. So welcome back!! :smiley:


#10

Thanks, Jim

I need to go back through the training videos. I’m pretty vague with everything I had learned about Panorama X.