CAN I ROUND TO THE NEAREST # ENDING IN 99 FOR EXAMPLE

[Guest]

IM MAKING PRICE TAGS AND I WANT TO ROUND UP THE PRICES TO THE NEAREST #
ENDING IN EITHER 39,69, 0R 99
SO IF THE VALUE OF THE CELL WAS 1575 FOR EXAMPLE
I WOULD WANT TO ROUND IT UP TO 1599
IS THERE A FORMULA THAT CAN AUTOMATE THIS?

 

[David Biddulph]

=ROUNDDOWN(A1,-2)+IF(MOD(A1,100)<39,39,IF(MOD(A1,100)<69,69,99))

.... and please don't SHOUT.

 

[Don Guillett]

First, Please do NOT type in all caps in the subject or the message. It is
considered to be SHOUTING and very bad netiauette.
Try this.

=CEILING(G4,10)-1

 

[Ron Rosenfeld]

IM MAKING PRICE TAGS AND I WANT TO ROUND UP THE PRICES TO THE NEAREST #
ENDING IN EITHER 39,69, 0R 99
SO IF THE VALUE OF THE CELL WAS 1575 FOR EXAMPLE
I WOULD WANT TO ROUND IT UP TO 1599
IS THERE A FORMULA THAT CAN AUTOMATE THIS?

Perhaps:

=FLOOR(A1,100)+INDEX({39,69,99},MATCH(FALSE,MOD(A1,100)>{39,69,99},0))

will do what you want?


--ron

 

[Guest]

thank you, that was helpful
and i apologize for my bad netiauette, im new and didnt know any better

 

[Guest]

a complex formula, while i have no idea why it works, it does exactly what i
need it to do
thank you
brock

 

[Ron Rosenfeld]

a complex formula, while i have no idea why it works, it does exactly what i
need it to do
thank you
brock

Glad to help. Thanks for the feedback.

How does it work?

1. Floor(a1,100) rounds down to the nearest 100.
2. MOD(a1,100) returns the last two digits.
3. Check to see if the last two digits are greater than 39, 69, or 99.
(It'll never be greater than 99)
4. The MOD....> ... section will return an array of TRUE or FALSE depending on
what those last two digits are. For example, if the last two digits are 75,
the function will return the array {TRUE,TRUE,FALSE}

5. The MATCH(False,...) returns a number corresponding to the location of the
first FALSE. In the above example, it would return a 3.
6. The INDEX function uses that 3 to decide which of the number in the array
{39,69,99} to return and add to the first term.

If you use Tools/Formula Auditing/Evaluate Formula, you may be able to better
see the progression of the calculation.
--ron

 

[Guest]

try
=floor(a1,100)+vlookup(mod(a1,100),{0,40,70:33,69,99})

 

[Don Guillett]

That's OK. I had a typo for "netiauette". Damn a is too close to the q
Glad to help. Did you want it to work for ALL (emphasis) numbers or just
those mentioned?