Round formula result

1

Hello Folks :slight_smile:

Do you know how to round a formula result ?

For example my formula is :

(([Data FB Ads].Filter(Day.Matches([daterangepicker 2])).[CPM (Cost per 1,000 Impressions)].Sum()-[Data FB Ads].Filter(Day.Matches([daterangepicker 4])).[CPM (Cost per 1,000 Impressions)].Sum())/[Data FB Ads].Filter(Day.Matches([daterangepicker 4])).[CPM (Cost per 1,000 Impressions)].Sum())*100

But the result is something like : XX.XXXXXXXXXX %.

I tried the Round formula but it’s not working and I didn’t find the way to round to 2 and get a result like this XX.XX %.

Do you have a solution please ?

Thank you for your help :slight_smile:

2

Sometimes you have to add ToNumber() at the end and then round it.

There’s no way I can replicate your formula, so a screenshot or a shared doc would definitely help.

Round([My Formula].ToNumber(), 2) * 100 should work.

3

Hello and thank you for answer @cnr ! Oh yeah, forgot to share the link : https://coda.io/d/Copy-of-Reporting_dq8HSZSWhc4 :slight_smile:

4

Ok, I think I fixed it

Round(
  (([Facebook Ads].Filter(Day.Matches([daterangepicker 1])).[Amout spent].Sum()-[Facebook Ads].Filter(Day.Matches([daterangepicker 4])).[Amout spent].Sum())/[Facebook Ads].Filter(Day.Matches([daterangepicker 4])).[Amout spent].Sum())*100, 
  2)
5

Thank you very much for your help @cnr :slight_smile:

6

Hi, @cnr and @Pfgope
I want to bump this topic because I had the same issue and I had a hard time trying to figure out the problem.

The reason the formula didn’t work was probably that @Pfgope was doing math operation using *,/,-,+ and chaining round(2). in the end This doesn’t work at all

For instance,

List(1,3,4,5).Sum()/List(2,3,4).Sum().Round(2) returns 1.4444444444444

round(List(1,3,4,5).Sum()/List(2,3,4).Sum(),2) returns 1.44

Does anybody know why?

7

This is because Round is only being applied to the second argument in your first example

To test this, try it with closer numbers, e.g:

2 / List(1.11111111).Sum().Round(2) => 1.8018018018018016
Round(2 / List(1.11111111).Sum(), 2) => 1.8
8

Correct me if I’m wrong but chaining is executed from left to right.
.round() should be the last action to take place.

9

Ah nope. Remember PEMDAS from school? There’s a similar order of operations in Coda.

Something like

  1. Dot operators
  2. Parenthesis
  3. Multiplication | Division
  4. Addition | Subtraction

Probably not 100% right here, but you get the gist