0
I'm trying to output retail prices rounded to either a 4 or an 8 from various formulas
Is is possible to round one figure to the nearest of each of these?
john
Posted 2014-12-17T11:55:58.853
Reputation: 1
Open Office Calc, Round up or down
Answers
0
Normally, a ROUNDUP or CEILING would go to the next multiple but you are asking to roundup to the next number with a 4 or 8 as last significant digit and the whole number may or may not actually be a multiple of 4 or 8. To do this, peel off the last digit and choose an appropriate addend to be used to adjust the original number.
The formula in B1 is,
=A1 + CHOOSE(INT(RIGHT(A1;1))+1; 4; 3; 2; 1; 0; 3; 2; 1; 0; 5)
Fill down as necessary.
Jeeped
Posted 2014-12-17T11:55:58.853
Reputation: 2 435
Could you give an example with input values and desired output? E.g., given a retail price of 112 $, what's the expected result of the formula you're looking for? – tohuwawohu – 2014-12-17T13:08:32.737
Are you referring to whole dollar amounts, only? You're looking for the last position to be a 4 or 8 rather than the price being a multiple of 4 or 8 ($32 is a multiple of 4 or 8 but doesn't end with 4 or 8)? By "round", you mean up or down to the closest 4 or 8? What would you want the result to be for a price of $11? – fixer1234 – 2014-12-17T19:06:57.983
hi thanks for your replies. yes looking for the last position digit to be a 4 or 8 so for example 29 it would go up to 34 or 35 would go to 38 – john – 2014-12-18T17:26:50.733