How to set the formula - part 2?

E

Eric

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
 
J

John C

=ROUND(--REPLACE(--SUBSTITUTE(A2,".",""),IF(--LEFT(--SUBSTITUTE(A2,".",""),2)<36,4,3),0,"."),0)

I think this formula will give you the result you want. If you want to know
how it works, you could always use Evaluate Formula on the Formula Auditing
Toolbar, and it will take you step by step through it. Or you could just ask
back.
 
S

Shane Devenshire

Hi,

Since I haven't seen part 1, I'm not sure I understand this question, but

=ROUND(A1,A1/100,0)

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Z

zorzal

I checked one or two but I believe this formula will give you what you
need:

=10^-(INT(LOG(A1))-2)

HTH

Zorzal
 
J

John C

This fails for the 94213 data, you need to modify formula to change the -2 to
a -1 if the first two significant digits are >=36
 
J

John C

The second set of data fails on this for the same reason as vezerid's, the -2
needs to be changed to a -1 if the first 2 signficant digits are >=36.
 
R

Rick Rothstein

This will do what you want, although I think it can be streamlined more (so
check back later to see if I manage to do it or not)...

=IF(--LEFT(TEXT(A1,"0.00E+0"),4)>3.6,SUBSTITUTE(LEFT(TEXT(A1,"0.0E+0"),3),".",""),SUBSTITUTE(LEFT(TEXT(A1,"0.00E+0"),4),".",""))
 
R

Rick Rothstein

This formula combines some of my formula with that of Zane's...

=ROUND(A1*(10^-(INT(LOG(A1))-1-(--LEFT(TEXT(A1,"0.00E+0"),4)<=3.6))),0)
 
R

Rick Rothstein

This formula combines some of my formula with that of Zane's to produce a
slightly tighter formula...

=ROUND(A1*(10^-(INT(LOG(A1))-1-(--LEFT(TEXT(A1,"0.00E+0"),4)<=3.6))),0)
 
V

vezerid

My apologies to the OP and the forum, once more I was hasty in not
reading the entire post. I believe the following modification will do:

=ROUND(A1*10^-(INT(LOG(A1))-2+(A1*10^-(INT(LOG(A1))-2)>360)),0)

HTH
Kostis
 
E

Eric

Thank everyone very much for suggestions

I need to restate my following statement,

I would like to return 0.01 in cell B1 instead of 327.

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.

If the given number is 3265.4 in cell A1, then 0.1 should be returned in cell
B1.

I would like to return 0.001 in cell B1 instead of 94.

If the given number is 94213 in cell A1, then 0.001 should be returned in
cell B1.

If the given number is 9421.3 in cell A1, then 0.01 should be returned in
cell B1.

Does anyone have any suggestions?
Thank everyone very much for any suggestions
Eric
 
J

John C

Then adding some logic to zorzal's function:
=10^-(INT(LOG(A2))-IF(--LEFT(--SUBSTITUTE(A2,".",""),2)<36,2,1))

Though I cannot understand why if you are just using that to subsequently
round the number that you would still want a 'helper' formula before you
round.
 
E

Eric

Thank everyone very much for suggestions

When I insert 0.06 in cell A2, it return 10000 instead of 1000.
When I insert 0.061 in cell A2, it return 1000, which is OK.
When I insert 0.03 in cell A2, it return 10000, which is OK.
Do you have any suggestions on how to fix it?
Thank everyone very much for any suggestions
Eric
 
J

John C

It's because of a single digit issue, try:
=10^-(INT(LOG(A2))-LOOKUP(--LEFT(--SUBSTITUTE(A2,".",""),2),{0,1;10,2;36,1}))
 
J

John C

Nope, still not right...
=10^-(INT(LOG(A2))-LOOKUP(--LEFT(--SUBSTITUTE(A2,".",""),2),{0,2;4,1;10,2;36,1}))
 
R

Rick Rothstein

Try this...

=10^-(MID(TEXT(A1,"0.00E+0"),6,4)-1-(--LEFT(TEXT(A1,"0.00E+0"),4)<=3.6))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to set the formula? 4
-/+ forumla 4
How to determine the value? 8
Need to Capture the initial value of a cell. 1
omit numbers past decimal in formula result 6
Time date stamp 0
How to set the formula (26 Nov)? 3
Finding the date of the previous Thanksgiving Day 11

Top