Log In
Register
Home
February 11, 2015
Offline
Good morning.
I have a question on a formula.
In column G, I have a formula (thanks Sunny) that pulls in the CPM from a table below. Example:
=INDEX($E$51:$J$74,MATCH(F8,$E$51:$E$74,0),MATCH(E8,$E$51:$J$51,0))
In column M, I have the following formula: It takes the total ordered times the CPM and divides by 1000.
=ROUND(L7*G7,2)/1000
24.65 x 603 / 1000 = 14.863, So I need the dollar amount to be $14.86
The total that the formula is calculating is $14.87. I have tried change from Round, to Roundup and Rounddown, but nothing is working. I then change the CPM from the formula to hard coding in the CPM. This worked. Is there a way to make the formula work but pull in the correct decimal?
I have put in an example of some lines are pulling in the correct decimal others are not.
Any advice would be much appreciated.
Thanks
Amy
VIP
April 21, 2015
Offline
As far as I can see it's maybe best to round also the complete formula in column G to 2 decimals.
Now G6 is giving $ 24,65404 etc. and it calculates with that amount in column M.
Of you round to two decimals in G6 it becomes $ 24,65000 and the outcome in M is correct.
Same for G9 and M9, so I think that helps. But maybe somewhere on the way you still got a rounding problem.
Anyway less than now when you change the G formula!
Frans
February 11, 2015
Offline
Frans,
I figured out my issue. The pricing table below that is pulling in the CPM price has decimals ranging from 0 to 10 places. Once I fixed all of the down to two decimal places, it fixed the issue. Thanks for all your help.
Amy
1 Guest(s)
