TOTAL OF A SINGLE CELL DIGIT TO SPECIFIC NUMBER

[Ripote]

Dear Sir / Madam,
i have a question , i want all digit total of a single cell to a specific number e.g.
i am preparing a proposal for a customer wherein my selling price total should be 9, please see below sample pricing of Gold & Diamond Ring

Gold Value $ 147
Diamond Value $1000
Total Cost $1147 + Labor+mark-up = my actual selling price comes to $ 1286.93 ; but i want to tag it as $ 1287.00.
request you to kindly advise a formula , where in i can get final selling price which has a total 9 as shown above (+1+2+8+7) instead of manual calculation

 

[jasonb75]

Where are you getting 9 from?

(+1+2+8+7)

That = 18, not 9

 

[Peter_SSs]

That = 18, not 9

I'm guessing that again sum the digits
1+8 = 9

 

[jasonb75]

I was thinking that it looked too simple to be correct but if that is the case then

=CEILING(1286.93,9)

possibly should suffice.

 

[Dave Patton]

Possibly

T202108a.xlsm
	A	B	C	D
1
2		1286.93	1287	1287
3		1287.93	1288	1296
4		1282.93	1283	1287
5
2b

Cell Formulas
Range	Formula
C2:C4	C2	=ROUNDUP(B2,0)
D2:D4	D2	=C2+9-(C2-FLOOR(C2-1,9))

 

[Peter_SSs]

Possibly ...

Dave, perhaps I'm missing something but doesn't that 2-step process produce exactly the same results as Jason's very simple 1-step formula?

 

[Ripote]

thanks Jason & Peter for your valuable answer ; it help me a lot.
can i ask one more question ? , using the above given formula definitely i got what i am looking for.
i just wanted to know if there is any other formula which give me final selling price in decimal
for example :
actual selling price of an product is USD 66.66 , but i want to make it as USD 66.69 (6+6+6+9=27; 2+7=9).
could you please help me

thanks

 

[jasonb75]

You could do that by changing the significance of the CEILING function.

=CEILING(66.66,0.09)

As far as I can see, this only works with 9, no other number.

 

[Ripote]

Thanks Jason