View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_] Fred Smith[_4_] is offline
external usenet poster
 
Posts: 2,389
Default How to set the formula?

Try the following for your first request:

=ROUND(A1*100/(10^INT(LOG(A1))),0)

Regards,
Fred.

"Eric" wrote in message
...
Thank everyone very much for suggestions

I would like to find the number in cell A1 into 3-digit number under 360.
If the given number is 32654 in cell A1, then 0.01 should be returned in
cell
B1, round(32654*0.01,0)=327

If the given number is 3265.4 in cell A1, then 0.1 should be returned in
cell
B1, round(3265.4*0.1,0)=327

If the given number is 326.54 in cell A1, then 1 should be returned in
cell
B1, round(326.54*1,0)=327

If the given number is 32.654 in cell A1, then 10 should be returned in
cell
B1, round(32.654*10,0)=327

If the given number is 3.2654 in cell A1, then 100 should be returned in
cell
B1, round(3.2654*100,0)=327

If the given number is 0.32654 in cell A1, then 1000 should be returned in
cell
B1, round(0.32654*1000,0)=327

If the given number is 0.032654 in cell A1, then 10000 should be returned
in
cell
B1, round(0.032654*10000,0)=327


If the converted 3-digit number is greater than 360, then I would like to
convert it into 2-digit numbers.

If the given number is 94213 in cell A1, then 0.001 should be returned in
cell
B1, round(94213*0.001,0)=94

If the given number is 9421.3 in cell A1, then 0.01 should be returned in
cell
B1, round(94213*0.01,0)=94

If the given number is 942.13 in cell A1, then 0.1 should be returned in
cell
B1, round(942.13*0.1,0)=94

If the given number is 94.213 in cell A1, then 1 should be returned in
cell
B1, round(94.213*1,0)=94

If the given number is 9.4213 in cell A1, then 10 should be returned in
cell
B1, round(9.4213*10,0)=94

If the given number is 0.94213 in cell A1, then 100 should be returned in
cell
B1, round(0.94213*100,0)=94

If the given number is 0.094213 in cell A1, then 1000 should be returned
in
cell
B1, round(0.094213*1000,0)=94

Does anyone have any suggestions on how to do it?
Thank everyone very much for any suggestions
Eric


"Stefi" wrote:

Maybe you mean this:

=ROUND(IF(ROUND(LEFT(A1*10^6,5)/100,0)360,ROUND(LEFT(A1*10^6,5)/100,0)/10,ROUND(LEFT(A1*10^6,5)/100,0)),0)

Regards,
Stefi

€žEric€ť ezt Ă*rta:

Does anyone have any suggestions on how to set the formula?

I would like to convert any number in cell A1 into 3-digit number under
360.
If the given number is 32654 in cell A1, then 327 should be returned in
cell
B1.

If the given number is 3265.4 in cell A1, then 327 should be returned
in
cell B1.
If the given number is 326.54 in cell A1, then 327 should be returned
in
cell B1.
If the given number is 32.654 in cell A1, then 327 should be returned
in
cell B1.
If the given number is 3.2654 in cell A1, then 327 should be returned
in
cell B1.
If the given number is 0.32654 in cell A1, then 327 should be returned
in
cell B1.
If the given number is 0.032654 in cell A1, then 327 should be returned
in
cell B1.

If the converted 3-digit number is greater than 360, then I would like
to
convert it into 2-digit numbers.
If the given number is 94213 in cell A1, then 94 should be returned in
cell
B1.
If the given number is 9421.3 in cell A1, then 94 should be returned in
cell
B1.
If the given number is 942.13 in cell A1, then 94 should be returned in
cell
B1.
If the given number is 94.213 in cell A1, then 94 should be returned in
cell
B1.
If the given number is 9.4213 in cell A1, then 94 should be returned in
cell
B1.
If the given number is 0.94213 in cell A1, then 94 should be returned
in
cell B1.
If the given number is 0.094213 in cell A1, then 94 should be returned
in
cell B1.

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric