Reputation: 149
I am looking to round up prices to the nearest $5 after a price increase of 3%
I have tried the formula below
= Table.AddColumn(#"Removed Columns", "NewPrice", each Number.Round([Price]*1.03,0,RoundingMode.Up))
So a new price 0f $121 or $124, should come up as $125. Basically, I would like to re-create the Excel formula ceiling(Price*1.03,5)
Upvotes: 0
Views: 1229
Reputation: 7891
To round up to the nearest multiple of 5, you need to divide the value by 5, then round up to the nearest whole number, then multiply by 5 again.
Using Number.RoundUp
instead of Number.Round
is more efficient.
Applying these steps to your code, gives:
= Table.AddColumn(#"Removed Columns", "NewPrice", each Number.RoundUp([Price]*1.03/5)*5, type number)
Upvotes: 2