Pfgope
December 21, 2020, 1:07pm
1
Hello Folks
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
cnr
December 21, 2020, 3:59pm
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.
1 Like
Pfgope
December 21, 2020, 4:13pm
3
Hello and thank you for answer @cnr ! Oh yeah, forgot to share the link : https://coda.io/d/Copy-of-Reporting_dq8HSZSWhc4
cnr
December 21, 2020, 4:46pm
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)
1 Like
Pfgope
December 21, 2020, 5:14pm
5
Thank you very much for your help @cnr
1 Like
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?
1 Like
cnr
January 9, 2022, 6:12pm
7
Breno_Nunes:
List(1,3,4,5).Sum()/List(2,3,4).Sum().Round(2)
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
Correct me if I’m wrong but chaining is executed from left to right.
.round()
should be the last action to take place.
cnr
January 10, 2022, 4:19am
9
Ah nope. Remember PEMDAS from school? There’s a similar order of operations in Coda.
Something like
Dot operators
Parenthesis
Multiplication | Division
Addition | Subtraction
Probably not 100% right here, but you get the gist