Using ROUNDDOWN in pivot table calculated field

[geolac82]

Hi,

I would like to use the ROUNDDOWN function in a calculated field on a pivot table but I am not having much luck nesting the function with the formula of the calculated field.

The calculated field formula currently looks like this:

= COLUMNX/COLUMNY

Column X contains hh:mm:ss data formatted as seconds only. Column Y contains whole numbers. How would I incorporate round down into this calculated field so the calculation rounds down the result?

Thanks
Damian

 

[RoryA]

Welcome to the forum.

What level of precision are you trying to round to?

 

[geolac82]

Welcome to the forum.

What level of precision are you trying to round to?

Thanks. I'd like to round down to the nearest whole number i.e. 64.5 down to 64. The number of decimal places the calculated field can produce varies though.

I wasn't sure whether the issue I was having was due to the fact that one field is in time format?

Thanks in advance.

 

[RoryA]

The format isn't really relevant. The function will work on the actual values returned. If those are times, you need to be aware that they are actually stored as fractions of of one day.

 

[geolac82]

Too easy. I've actually converted the time format to be seconds only because I display the values in the pivot table as seconds only. So when I type the formula in the calculated field as follows:

=ROUNDDOWN(COLUMNX/COLUMNY,1)

It zeroes out all the data in my pivot table.

 

[RoryA]

I've actually converted the time format to be seconds only

If all you've done is changed the formatting, then you're still dealing with really small numbers. One second is stored as 1/86400 or roughly 0.00001157.

You'd probably be better off multiplying the time values by 86400 to get a true number of seconds, divide that by your other field and then round down.