View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eric Eric is offline
external usenet poster
 
Posts: 1,670
Default How to set the formula - part 2?

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

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