| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
=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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Eric" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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 "Eric" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
=ROUND(A1*10^-(INT(LOG(A1))-2),0)
HTH Kostis Vezerides On Nov 14, 4:44*pm, Eric wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
I checked one or two but I believe this formula will give you what you
need: =10^-(INT(LOG(A1))-2) HTH Zorzal |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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 -- ** John C ** "vezerid" wrote: =ROUND(A1*10^-(INT(LOG(A1))-2),0) HTH Kostis Vezerides On Nov 14, 4:44 pm, Eric wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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. -- ** John C ** "zorzal" wrote: I checked one or two but I believe this formula will give you what you need: =10^-(INT(LOG(A1))-2) HTH Zorzal |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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(TEX T(A1,"0.0E+0"),3),".",""),SUBSTITUTE(LEFT(TEXT(A1, "0.00E+0"),4),".","")) -- Rick (MVP - Excel) "Eric" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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) -- Rick (MVP - Excel) "John C" <johnc@stateofdenial wrote in message ... 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. -- ** John C ** "zorzal" wrote: I checked one or two but I believe this formula will give you what you need: =10^-(INT(LOG(A1))-2) HTH Zorzal |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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(TEX T(A1,"0.0E+0"),3),".",""),SUBSTITUTE(LEFT(TEXT(A1, "0.00E+0"),4),".","")) -- Rick (MVP - Excel) "Eric" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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 On Nov 14, 5:24*pm, John C <johnc@stateofdenial wrote: 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 -- ** John C ** "vezerid" wrote: =ROUND(A1*10^-(INT(LOG(A1))-2),0) HTH Kostis Vezerides On Nov 14, 4:44 pm, Eric wrote: 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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 "John C" wrote: =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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Eric" wrote: 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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. -- ** John C ** "Eric" wrote: 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 "John C" wrote: =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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Eric" wrote: 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
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 "John C" wrote: 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. -- ** John C ** "Eric" wrote: 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 "John C" wrote: =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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Eric" wrote: 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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
It's because of a single digit issue, try:
=10^-(INT(LOG(A2))-LOOKUP(--LEFT(--SUBSTITUTE(A2,".",""),2),{0,1;10,2;36,1})) -- ** John C ** "Eric" wrote: 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 "John C" wrote: 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. -- ** John C ** "Eric" wrote: 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 "John C" wrote: =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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Eric" wrote: 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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
=10^-(INT(LOG(A1))-2+(A1*10^-(INT(LOG(A1))-2)360))
HTH On Nov 14, 5:46*pm, Eric wrote: 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 "John C" wrote: =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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Eric" wrote: 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 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
Nope, still not right...
=10^-(INT(LOG(A2))-LOOKUP(--LEFT(--SUBSTITUTE(A2,".",""),2),{0,2;4,1;10,2;36,1})) -- ** John C ** "Eric" wrote: 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 "John C" wrote: 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. -- ** John C ** "Eric" wrote: 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 "John C" wrote: =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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Eric" wrote: 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 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the formula - part 2?
Try this...
=10^-(MID(TEXT(A1,"0.00E+0"),6,4)-1-(--LEFT(TEXT(A1,"0.00E+0"),4)<=3.6)) -- Rick (MVP - Excel) "John C" <johnc@stateofdenial wrote in message ... Nope, still not right... =10^-(INT(LOG(A2))-LOOKUP(--LEFT(--SUBSTITUTE(A2,".",""),2),{0,2;4,1;10,2;36,1})) -- ** John C ** "Eric" wrote: 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 "John C" wrote: 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. -- ** John C ** "Eric" wrote: 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 "John C" wrote: =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. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Eric" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
| IF formula help part two | Excel Worksheet Functions | |||
| change color of all cells with formula or are part of a formula | Excel Discussion (Misc queries) | |||
| 2 Part Formula | Excel Worksheet Functions | |||
| Looking for formula part 2 | Excel Discussion (Misc queries) | |||
Linear Mode
