| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard index form - Generating leading number (before *10^x part
Hi all! Here is a challenge for you...
Does anybody know how to combine worksheet functions to generate the leading number from the Standard Index Form version of a "normal number"? For example,... ....if the input was 340000, the output would be 3.4 ....if the input was 0.00278, the output would be 2.78 I realise that Excel can display a "normal" number in this "scientific" format, but it is for a teaching resource that I am building, so that is no use to me. Would it use the LEFT or RIGHT functions? I have at least worked out that, once I have this leading number, in order to generate the index the formula would be =LOG10(complete_number/leading_number) but I am puzzled about how to get the first bit. Many thanks in advance, Neil Goldwasser |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Standard index form - Generating leading number (before *10^x part
Try this, where A1 is where input value is:
=SUBSTITUTE(SUBSTITUTE(A1,0,""),".","")/10 "Neil Goldwasser" wrote in message ... Hi all! Here is a challenge for you... Does anybody know how to combine worksheet functions to generate the leading number from the Standard Index Form version of a "normal number"? For example,... ...if the input was 340000, the output would be 3.4 ...if the input was 0.00278, the output would be 2.78 I realise that Excel can display a "normal" number in this "scientific" format, but it is for a teaching resource that I am building, so that is no use to me. Would it use the LEFT or RIGHT functions? I have at least worked out that, once I have this leading number, in order to generate the index the formula would be =LOG10(complete_number/leading_number) but I am puzzled about how to get the first bit. Many thanks in advance, Neil Goldwasser |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Standard index form - Generating leading number (before *10^x
Unfortunately this did not work. For example, using 100 as my input, the
output was 0.1, whereas it should have been 1. Using 125 as my input, the output was 12.5, whereas it should have been 1.25. Standard index form always has one non-zero digit before the decimal point. Many thanks for your attempt though. Hopefully we'll get there... "Castell" wrote: Try this, where A1 is where input value is: =SUBSTITUTE(SUBSTITUTE(A1,0,""),".","")/10 "Neil Goldwasser" wrote in message ... Hi all! Here is a challenge for you... Does anybody know how to combine worksheet functions to generate the leading number from the Standard Index Form version of a "normal number"? For example,... ...if the input was 340000, the output would be 3.4 ...if the input was 0.00278, the output would be 2.78 I realise that Excel can display a "normal" number in this "scientific" format, but it is for a teaching resource that I am building, so that is no use to me. Would it use the LEFT or RIGHT functions? I have at least worked out that, once I have this leading number, in order to generate the index the formula would be =LOG10(complete_number/leading_number) but I am puzzled about how to get the first bit. Many thanks in advance, Neil Goldwasser |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Standard index form - Generating leading number (before *10^x part
"Neil Goldwasser" ha scritto nel
messaggio ... Hi all! Here is a challenge for you... Does anybody know how to combine worksheet functions to generate the leading number from the Standard Index Form version of a "normal number"? For example,... ...if the input was 340000, the output would be 3.4 ...if the input was 0.00278, the output would be 2.78 your number in [A1] in [B1] =A1/10^INT(LOG(A1)) or =TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000") .f |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Standard index form - Generating leading number (before *10^x
Try this slight revision to Castell's suggestion:
=SUBSTITUTE(SUBSTITUTE(A1,0,""),".","")/10^(LEN(SUBSTITUTE(SUBSTITUTE(A1,0,""),".",""))-1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Neil Goldwasser" wrote: Unfortunately this did not work. For example, using 100 as my input, the output was 0.1, whereas it should have been 1. Using 125 as my input, the output was 12.5, whereas it should have been 1.25. Standard index form always has one non-zero digit before the decimal point. Many thanks for your attempt though. Hopefully we'll get there... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Standard index form - Generating leading number (before *10^x
Thanks everyone! Problem solved... and much appreciated!
Neil Goldwasser "fernando cinquegrani" wrote: "Neil Goldwasser" ha scritto nel messaggio ... Hi all! Here is a challenge for you... Does anybody know how to combine worksheet functions to generate the leading number from the Standard Index Form version of a "normal number"? For example,... ...if the input was 340000, the output would be 3.4 ...if the input was 0.00278, the output would be 2.78 your number in [A1] in [B1] =A1/10^INT(LOG(A1)) or =TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000") .f |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Standard index form - Generating leading number (before *10^x
PS - The "winner" was.....
your number in [A1] in [B1] =A1/10^INT(LOG(A1)) This was the only one that took account of ALL decimal places needed, as well as taking account of zeros sandwiched between non-zero digits and therefore still needed (i.e. zeros that are NOT at the end of the number). For example, the leading number for 10203000 should be 1.0203, and this was the only formula to produce this. Again, many thanks for all your help! Neil Goldwasser "Neil Goldwasser" wrote: Thanks everyone! Problem solved... and much appreciated! Neil Goldwasser "fernando cinquegrani" wrote: "Neil Goldwasser" ha scritto nel messaggio ... Hi all! Here is a challenge for you... Does anybody know how to combine worksheet functions to generate the leading number from the Standard Index Form version of a "normal number"? For example,... ...if the input was 340000, the output would be 3.4 ...if the input was 0.00278, the output would be 2.78 your number in [A1] in [B1] =A1/10^INT(LOG(A1)) or =TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000") .f |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Standard index form - Generating leading number (before *10^x
Fair enough. You're right.
"Neil Goldwasser" wrote in message ... PS - The "winner" was..... your number in [A1] in [B1] =A1/10^INT(LOG(A1)) This was the only one that took account of ALL decimal places needed, as well as taking account of zeros sandwiched between non-zero digits and therefore still needed (i.e. zeros that are NOT at the end of the number). For example, the leading number for 10203000 should be 1.0203, and this was the only formula to produce this. Again, many thanks for all your help! Neil Goldwasser "Neil Goldwasser" wrote: Thanks everyone! Problem solved... and much appreciated! Neil Goldwasser "fernando cinquegrani" wrote: "Neil Goldwasser" ha scritto nel messaggio ... Hi all! Here is a challenge for you... Does anybody know how to combine worksheet functions to generate the leading number from the Standard Index Form version of a "normal number"? For example,... ...if the input was 340000, the output would be 3.4 ...if the input was 0.00278, the output would be 2.78 your number in [A1] in [B1] =A1/10^INT(LOG(A1)) or =TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000") .f |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Standard index form - Generating leading number (before *10^x
Many thanks for your help though, I really appreciate it!
Neil "Castell" wrote: Fair enough. You're right. "Neil Goldwasser" wrote in message ... PS - The "winner" was..... your number in [A1] in [B1] =A1/10^INT(LOG(A1)) This was the only one that took account of ALL decimal places needed, as well as taking account of zeros sandwiched between non-zero digits and therefore still needed (i.e. zeros that are NOT at the end of the number). For example, the leading number for 10203000 should be 1.0203, and this was the only formula to produce this. Again, many thanks for all your help! Neil Goldwasser "Neil Goldwasser" wrote: Thanks everyone! Problem solved... and much appreciated! Neil Goldwasser "fernando cinquegrani" wrote: "Neil Goldwasser" ha scritto nel messaggio ... Hi all! Here is a challenge for you... Does anybody know how to combine worksheet functions to generate the leading number from the Standard Index Form version of a "normal number"? For example,... ...if the input was 340000, the output would be 3.4 ...if the input was 0.00278, the output would be 2.78 your number in [A1] in [B1] =A1/10^INT(LOG(A1)) or =TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000") .f |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Three new functions that should be part of your standard set | Excel Worksheet Functions | |||
| Can a standard data entry form be used to change formats of cells? | Excel Discussion (Misc queries) | |||
| Replace/modify standard data form? | Excel Discussion (Misc queries) | |||
| standard form | Excel Discussion (Misc queries) | |||
| How do I set up a standard form showing incentive calculations | Excel Discussion (Misc queries) | |||
Linear Mode
