

February 11, 2015

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

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
1 Guest(s)
