CODEWITHSUNDEEP
roundingpowerquerym
Bernard Faucher
Bernard Faucher

Reputation: 149

Rounding up to the nearest $5

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

Answers (1)

Olly
Olly

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

Related Questions