LinkBack URL
About LinkBacks
Pretty simple question but can't find out the solution. Say I have a number in scientific notation, like;
1.23E+04
I need a formula for that would display the "4" only, which is the power.
Pretty simple question but can't find out the solution. Say I have a number in scientific notation, like;
1.23E+04
I need a formula for that would display the "4" only, which is the power.
Shouldn't that be common log of the number, then round down to the nearest integer? =ROUNDDOWN(LOG10(value),0)
Originally Posted by shg
see attachment
Edit: with formula MrShorty
Edit2: @AliGW : as I use a dutch version, the formula isn't ok, the dutch version isEnglish version after macro-recording, not 100%
Please Login or Register to view this content.
Last edited by bsalv; 09-24-2021 at 05:55 PM.
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
@bsalv
Sorry for off-topic interjection:
Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks! It works for almost everything but not those with a negative sign...
.. .. .. .. ..
Last edited by bsalv; 09-25-2021 at 06:10 AM.
Your only example was for a number greater than 10, so I did not account for different behavior. ROUNDDOWN() always rounds down. INT(), on the other hand, always rounds towards 0, which, I think, is the behavior you want =INT(LOG10(value)).
.. .. .. .. ..
Last edited by bsalv; 09-25-2021 at 06:08 AM.
@bsalv
Try bookmarking this to use to translate formulae from Dutch to English: https://en.excel-translator.de/
Excel INT function translation to Ducth is INTEGER, not GEHEEL. GEHEEL = TRUNC in English.
Oh... Ali's post #9 came in while I was typing.
As long as all the numbers the formula will be applied against are always displayed in E-notation, you can use this simple formula...
=0+MID(TEXT(A2,"#E+000"),3,4)
Note: Excel will try to be "helpful" and set the cell format to Scientific so you will have to manually set it to General after you enter the formula into its cell.
EDIT NOTE:
=====================
You could also write this formula like this...
which I note is similar to what bsalv posted in Message #3 except his formula has (1) a syntax error - he added a closing square bracket where none is needed, (2) returns text instead of a number and (3) would not handle up to the maximum possible exponent as written.Please Login or Register to view this content.
Last edited by Rick Rothstein; 09-25-2021 at 03:44 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks