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

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?

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

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

.... and please don't SHOUT.
--
David Biddulph

"BROCK8292" wrote in message
...
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?

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

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

--
Don Guillett
SalesAid Software

"BROCK8292" wrote in message
...
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?

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

On Tue, 20 Feb 2007 07:50:23 -0800, BROCK8292
wrote:

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

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

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"BROCK8292" wrote in message
...
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?

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

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" wrote:

On Tue, 20 Feb 2007 07:50:23 -0800, BROCK8292
wrote:

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

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

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

"BROCK8292" wrote:

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?

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

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?

--
Don Guillett
SalesAid Software

"BROCK8292" wrote in message
...
thank you, that was helpful
and i apologize for my bad netiauette, im new and didnt know any better

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"BROCK8292" wrote in message
...
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?