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?

=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?

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?

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?

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?

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

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

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?