formula to roundup to the nearest 9

[Guest]

I have marked up prices with 50% plus 14% vat and then need to round up to
the nearest nine. Please help.
Many thanks
Warm regards
Sue

 

[Ron de Bruin]

Hi Sue

Can you give more information.(example number and the result you want)
It is not clear to me what you want

 

[Ragdyer]

9 cents
9 pence
9 dollars
9 pounds
9 yen

9 WHAT ? ? ?

 

[Fred Smith]

Try

=roundup(a1*1.5*1.14,-1)-1

 

[Guest]

My cost is 50 and I add 50% markup and 14% vat. =(a1*1.5*1.14). The answer is
85.50 and I want it to read 89. I want all the prices to end in a 9. So in
effect I want to round all the numbers to end in nine. Hope you can help me.
Are you from Holland. Your surname is very common here in South Africa.
Regards
Sue

 

[Guest]

9 rand (south african) my formula is =(a1*1.5*1.14) if a1 is R50 then the
answer is R85.50 and I want it to end in 9 so that all the prices end in 9.
Can you help
Regards
Sue

 

[Ron de Bruin]

You can try this with the value in A2

=LEFT(INT(A2),LEN(INT(A2))-1)&9

Are you from Holland

Yes

 

[RagDyer]

You already have your answer from Fred,
But FWIW:

=CEILING(A1*1.5*1.14+1,10)-1

 

[Epinn]

Sue,



Are you aware that all three formulae give different results?



(1) =LEFT(INT(A2),LEN(INT(A2))-1)&9



(2) =ROUNDUP(A1*1.5*1.14,-1)-1



(3) =CEILING(A1*1.5*1.14+1,10)-1



(1) = text format (2) and (3) = number



If after 50% markup and 14% VAT, the value is 99.99, formula (1) and (2) will give you 99 whereas formula (3) will give you 109. The discrepancy may not show unless certain values are hit.



Just want to highlight this fact for all readers.



Epinn



I have marked up prices with 50% plus 14% vat and then need to round up to
the nearest nine. Please help.
Many thanks
Warm regards
Sue