+ Reply to Thread
Results 1 to 52 of 52

what do you lack in excel?

  1. #1
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    what do you lack in excel?

    Maybe some MS executives will see it
    my list is the following:
    1 function CONCATENATE for a range, not array
    2 AGGREGATE for SUM as arrayfunction
    3 i want always see the first row in autofilter dropdown
    4 the possibility of using built-in lists (months, weekdays) without creating them in a WB
    5 built-in state holidays list
    6 the possibility of coping a string from system messages
    7 function TODAY not like a function
    8 i do not want to create a named range for a grath as following: sheetname!name

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: what do you lack in excel?

    In no particular order...

    A simple function to return the sheet name(s): =SHEETNAMES()

    A simple function to return the file name: =FILENAME()

    An efficient function to count unique entries: =COUNTUNIQUES()

    An efficient function to conditionally count unique entries: =COUNTUNIQUESIFS()

    An improved SUBSTITUTE function that will accept arrays or ranges:
    =SUBSTITUTE(A1,{"A","B","C"},"")
    =SUBSTITUTE(A1,B1:B5,"")
    =SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),"")

    There needs to be a whole array of functions dedicated to filtered data:
    =COUNTIF.FILTER()
    =COUNTIFS.FILTER()
    =SUMIF.FILTER()
    =SUMIFS.FILTER()
    =MAXIF.FILTER()
    =MAXIFS.FILTER()
    =MINIF.FILTER()
    =MINIFS.FILTER()
    =COUNTUNIQUES.FILTER()
    =COUNTUNIQUESIFS.FILTER()
    =VLOOKUP.FILTER()

    There should be conditional rank functions: =RANKIF(), =RANKIFS()

    There should be a multi-instance version of V/HLOOKUP:
    =VLOOKUP.MULT()
    =HLOOKUP.MULT()
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: what do you lack in excel?

    Agree
    But the most part of users even do not get what we are talking about

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: what do you lack in excel?

    4 the possibility of using built-in lists (months, weekdays) without creating them in a WB
    Please Login or Register  to view this content.
    Last edited by snb; 11-20-2016 at 06:47 AM.



  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,602

    Re: what do you lack in excel?

    I'd like it to be much easier to update conditional formatting rules using find and replace.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: what do you lack in excel?

    snb,
    thanks a lot!
    But this code created them in a WB and do not name them
    i want for example: =INDEX(weekday,2)

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: what do you lack in excel?

    AliGW,
    I like your way of phrasing it: I'd like to
    Don't you pay for MSO?

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: what do you lack in excel?

    Quote Originally Posted by tim201110 View Post
    snb,
    thanks a lot!
    But this code created them in a WB and do not name them
    i want for example: =INDEX(weekday,2)

    Please Login or Register  to view this content.
    Last edited by snb; 11-20-2016 at 06:11 PM.

  9. #9
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: what do you lack in excel?

    The Evaluate Formula window needs an update. At the very least it needs to be resize-able.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,602

    Re: what do you lack in excel?

    Quote Originally Posted by tim201110 View Post
    AliGW,
    I like your way of phrasing it: I'd like to
    Don't you pay for MSO?
    What are you implying? Doesn't software evolve and improve over time? Am I not just expressing what I'd like to see improved?

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,165

    Re: what do you lack in excel?

    Can I turn this on its head and ask what do you wish wasn't there?

    The obvious one IMO is the Merge Cells functionality.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  12. #12
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,248

    Re: what do you lack in excel?

    Since we are wishing ...

    A built-in SIRI that, when the VBE highlights your errors, SIRI sternly says: "Hey dummy, that's not the way it's done. Let me show you how ..."

    Kinda like a verbal auto-correct ?

    Ok, let me get back to studying and learning more about VBA.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,602

    Re: what do you lack in excel?

    I wouldn't mind the merge cells option if Excel assumed that every cell in the merged range contained the same data, as that would circumvent many of the problems with it.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: what do you lack in excel?

    Quote Originally Posted by Whizbang View Post
    The Evaluate Formula window needs an update. At the very least it needs to be resize-able.
    it already is
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: what do you lack in excel?

    Years ago I thought it would be helpful if VLOOKUP (and other lookup functions, including MATCH) had another optional parameter to specify that the data is sorted. Often you want to look for an exact match, but Excel then assumes that the data is not sorted and so uses a sequential search algorithm. If you were to set this other parameter to TRUE then it would use the binary search algorithm to look for an exact match, thus speeding things up.

    Pete

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: what do you lack in excel?

    Pete, may be misunderstanding, but isnt that what the last argument in VLOOKUP does anyway?

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,371

    Re: what do you lack in excel?

    What do I wish was in Excel:

    1) More number formatting options. Years ago, I asked here about getting a ppm number format (the number 0.000452 would display as 452). Or how about a per cent format that does not include the % symbol (0.52 would display as 52 and not 52%). Sometimes I wish number formatting had more in common with FORTRAN FORMAT statements.
    2) IMO, the heart and soul of spreadsheet programming is the built in list of functions. One of the things I have always noticed is that most of the new functions with each version of Excel have been database and business functions -- not math and engineering functions. One of the reasons that almost all of my VBA experience is programming UDF's for my own use is to provide myself with the functions that I wish MS would natively provide.
    3) Bring back the chart wizard.

    As for Richard's question -- what do I wish wasnt' there?
    1) Most Auto-formatting.
    1a) I hate the auto-column width thing that Excel does.
    1b) Just because I use scientific format (1.2345E6) to enter a number does not mean that I want the cell to adopt that format
    1c) I have had trouble with cells formatted as per cent. If I enter 0.95, I get 95%, but if I enter 1, I get 1% when I wanted 100%.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: what do you lack in excel?

    Another "leave out" - when excel assumes that when you enter 1/5, you intended that to be a date, although, that is also kinda a useful feature, but it needs to be controlable

  19. #19
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,371

    Re: what do you lack in excel?

    Amen, FDibbens. I had forgotten the whole "how to enter fractions issue".

  20. #20
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: what do you lack in excel?

    Quote Originally Posted by FDibbins View Post
    Pete, may be misunderstanding, but isnt that what the last argument in VLOOKUP does anyway?
    Hi Ford,

    no, that just tells Excel that it has to continue its search (all the way through the table) until it finds an exact match or until it runs out of data - a sequential search will (on average) examine half the number of data items in the lookup list, assuming the lookup value is there. If you can tell Excel that the data is sorted, even with a sequential search, then the algorithm can terminate if it finds a value that is larger than the lookup value, so that would be quicker than a sledgehammer sequential search, but with a binary search it would be a lot quicker (e.g. in 1024 sorted items, you would only need to examine 10 values to determine if the value is present or not).

    If you set the final parameter of VLOOKUP to TRUE (or 1), it will not tell you if the lookup value is absent - it will return data for the highest value in the lookup list which is less than or equal to the lookup value.

    Pete

  21. #21
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: what do you lack in excel?

    A built-in SIRI that, when the VBE highlights your errors, SIRI sternly says: "Hey dummy, that's not the way it's done. Let me show you how ..."
    It won't show you how... but I've done something like below for prank.
    Please Login or Register  to view this content.

  22. #22
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: what do you lack in excel?

    Quote Originally Posted by FDibbins View Post
    it already is
    This kludge-y thing? I don't see anywhere to resize it. I have Office 2013.
    Attached Images Attached Images

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: what do you lack in excel?

    OK, Pete, understand now

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: what do you lack in excel?

    Quote Originally Posted by Whizbang View Post
    This kludge-y thing? I don't see anywhere to resize it. I have Office 2013.
    from 2010 (I think) onwards, you can grab the bottom edge and drag it down - to pretty much the entire window

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,602

    Re: what do you lack in excel?

    It's the evaluation window itself that doesn't resize.

  26. #26
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: what do you lack in excel?

    eval.JPG
    hope this comes out.

    You mean this?

  27. #27
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: what do you lack in excel?

    Quote Originally Posted by FDibbins View Post
    Attachment 495715
    hope this comes out.

    You mean this?
    No, I mean if you go to Formulas -> Formula Auditing -> Evaluate Formula
    Attached Images Attached Images

  28. #28
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: what do you lack in excel?

    OK, that is something I never use, I prefer to use the "insert function (fx)" next to the formula bar, which is what I showed

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,602

    Re: what do you lack in excel?

    I find the evaluate function feature invaluable. I agree that the evaluation window is far too small, especially when evaluating array formulae.

  30. #30
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,926

    Re: what do you lack in excel?

    dont use it, but to each their own

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,602

    Re: what do you lack in excel?

    Of course! Nobody said you should use it!!!

  32. #32
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: what do you lack in excel?

    Quote Originally Posted by AliGW View Post
    I find the evaluate function feature invaluable. I agree that the evaluation window is far too small, especially when evaluating array formulae.
    Same here.

    In fact, I like to think that much of what I know about formulas I learned by testing them through the Evaluate Formula command.

  33. #33
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,685

    Re: what do you lack in excel?

    Alternatively we can use F9 to evaluate the formula manually.(In Formula Editing Mode)

    But F9 will lose the actual formula and returns results only


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  34. #34
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: what do you lack in excel?

    Quote Originally Posted by FDibbins View Post
    it already is
    MSO 2013, nothing
    if we expand this to Windows:
    -disc clean-up dialog is very small
    -there is no checkbox "mark all" in the above

  35. #35
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,525

    Re: what do you lack in excel?

    I'd like to see some sort of significant digit Function which counts from the left starting with first non-zero
    =SigDigits(A1,4)
    A1= 14356.3, B1 = 14360
    A1 = 1.22567 B1 = 1.226
    A1 = 0.023539 B1 = 0.02354
    ChemistB
    My 2Ē

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  36. #36
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: what do you lack in excel?

    Well, I would like to see a proper compiler, something that will let you know when possible 'Run Time' errors may occur or other possible errors.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  37. #37
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,371

    Re: what do you lack in excel?

    I like that idea, too, ChemistB. There have been many times I have also wished for a "format with n significant digits and decimal align."

  38. #38
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: what do you lack in excel?

    Quote Originally Posted by AliGW View Post
    What are you implying? Doesn't software evolve and improve over time? Am I not just expressing what I'd like to see improved?
    IMHO
    MS talk a lot, but do very little
    they even don't pay much attention to valid complaints

  39. #39
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: what do you lack in excel?

    Quote Originally Posted by ChemistB View Post
    I'd like to see some sort of significant digit Function which counts from the left starting with first non-zero
    =SigDigits(A1,4)
    there is a function VPA in Matlab for this

  40. #40
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: what do you lack in excel?

    Quote Originally Posted by tim201110 View Post
    Maybe some MS executives will see it
    That is a pretty slim probability if you keep your ideas in this forum. But if you post them on https://excel.uservoice.com/ you can be sure that the Excel team looks at them. You can see what other people suggested and vote for those ideas that you support.

    The more votes an idea has, the more likely it is that the Excel team will start working on its implementation. Some ideas suggested in the last few months can already be seen in Excel 2016 with Office 365.

    cheers, teylyn

  41. #41
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,052

    Re: what do you lack in excel?

    Vielen Dank Teylyn

  42. #42
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: what do you lack in excel?

    Quote Originally Posted by teylyn View Post
    That is a pretty slim probability if you keep your ideas in this forum. But if you post them on https://excel.uservoice.com/ you can be sure that the Excel team looks at them. You can see what other people suggested and vote for those ideas that you support.
    teylyn,
    thanks for the link
    sorry, found nothing of interest
    and I bet that the most part of this thread can motivate them to do something

  43. #43
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: what do you lack in excel?

    Quote Originally Posted by tim201110 View Post
    ...I bet that the most part of this thread can motivate them to do something
    Hi tim,
    The only way I have found to motivate Microsoft to do anything is to show them a mistake and tell them the solution. – Recently I commented on some errors in some of their literature available in the Web and gave them the correct form. Fairly quickly after that the literature on the Web was changed. I think that is about as close you can get to any response or feedback from Microsoft, Lol...
    I get the impression that they are not investing much in VBA, so for that reason they may not be too interested in suggestions..
    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  44. #44
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,602

    Re: what do you lack in excel?

    Tim does not appear to realise that this forum is not a direct link to Microsoft: the likelihood that any MS engineers/developers are reading this thread must be next to zero!

  45. #45
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: what do you lack in excel?

    Quote Originally Posted by :) Sixthsense :) View Post
    Alternatively we can use F9 to evaluate the formula manually.(In Formula Editing Mode)

    But F9 will lose the actual formula and returns results only
    After selecting F9 to evaluate the section of formula, now select the Esc key (or Ctrl + Z) and it will retain your formula section within the formula.

    It's been awhile since I've used this, but Aaron Blood created an Add-in called Explode which might prove useful to someone.
    HTH
    Regards, Jeff

  46. #46
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: what do you lack in excel?

    Quote Originally Posted by jeffreybrown View Post
    After selecting F9 to evaluate the section of formula, now select the Esc key (or Ctrl + Z) and it will retain your formula section within the formula..........
    Yep, a very good Tip that one:

    I had the same frustrations with few other similar things:

    _ If You do an immediate evaluation of a formula in a spreadsheet cell, or part of that formula, ( by selecting the formula or part of it in the formula bar , and then Hitting F9 ), then you see the results of the evaluation , but also lose the formula view. Hitting Esc gets you the formula view back

    _ Also if you try to change part of a spreadsheet range used for the Array output of a Formula, ( after doing the CSE stuff ) , you get caught in an error situation that appears at first sight to not be able to get out of... . Hitting Esc gets you out of that also

    _ Seems like Esc “gets you back” and cancels what you did , which is I suppose what it often does to “get you out” of a situation....
    _ I try to escape a lot.. and did... .. it don’t always work.. .. so I get caught sometimes !

    _ Escaping just now actually Goodbye , I am out of here..
    Alan






    ..“A formula or part of a formula is immediately evaluated (calculated), even in manual calculation mode, when you…. For example……

    Select the formula in the formula bar and press F9 (press ESC to undo and revert to the formula),….”

    http://www.mrexcel.com/forum/excel-q...ml#post3974322
    Last edited by Doc.AElstein; 01-14-2017 at 12:45 PM.

  47. #47
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,659

    Re: what do you lack in excel?

    Quote Originally Posted by ChemistB View Post
    I'd like to see some sort of significant digit Function which counts from the left starting with first non-zero
    =SigDigits(A1,4)
    A1= 14356.3, B1 = 14360
    A1 = 1.22567 B1 = 1.226
    A1 = 0.023539 B1 = 0.02354
    Long time no speak, CB. Happy new year.

    A
    B
    C
    D
    E
    F
    1
    Num
    1
    2
    3
    4
    2
    0.000456
    0.0005
    0.00046
    0.000456
    0.000456
    B2: =--TEXT($A2, "0." & REPT(0, B$1 - 1) & "E+0")
    3
    0.002841
    0.003
    0.0028
    0.00284
    0.002841
    4
    0.012168
    0.01
    0.012
    0.0122
    0.01217
    5
    0.428059
    0.4
    0.43
    0.428
    0.4281
    6
    1.630002
    2
    1.6
    1.63
    1.63
    7
    22.51575
    20
    23
    22.5
    22.52
    8
    397.0749
    400
    400
    397
    397.1
    9
    4087.514
    4000
    4100
    4090
    4088
    Entia non sunt multiplicanda sine necessitate

  48. #48
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: what do you lack in excel?

    shg,

    Do you have one for the right of the decimal? 4 significant digits

    1.458765 >> would be 1.4590

    I know it would be 1.459, but the zero needs to be there.

  49. #49
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: what do you lack in excel?

    Quote Originally Posted by AliGW View Post
    Tim does not appear to realise that this forum is not a direct link to Microsoft: the likelihood that any MS engineers/developers are reading this thread must be next to zero!
    No, i do
    but
    the more a real profy knows the better he is, and threre is no limit.
    MS are not good profies , as I see it.

    Please read Noise Level by Raymond Fisher Jones, it is worth it

  50. #50
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,659

    Re: what do you lack in excel?

    There's no way of showing significant digits of numbers other than using scientific notation or text, Jeffrey; 1.4590 is the same number as 1.459.

    The number 100 may represent 1, 2, 3, or more significant digits. I recollect one method is via underline:

    100
    100
    100

    Scientific notation would tell you exactly (by mutual agreement):

    1E+2
    1.0E+2
    1.00E+2
    Last edited by shg; 01-15-2017 at 02:52 PM.

  51. #51
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,659

    Re: what do you lack in excel?

    Scientific notation would tell you exactly
    E.g.,

    A
    B
    C
    D
    E
    F
    1
    Num
    1
    2
    3
    4
    2
    0.000456
    5.E-4 4.6E-4 4.56E-4 4.558E-4 B2: =TEXT($A2, "0." & REPT(0, B$1 - 1) & "E+0")
    3
    0.002841
    3.E-3 2.8E-3 2.84E-3 2.841E-3
    4
    0.012168
    1.E-2 1.2E-2 1.22E-2 1.217E-2
    5
    0.428059
    4.E-1 4.3E-1 4.28E-1 4.281E-1
    6
    1.630002
    2.E+0 1.6E+0 1.63E+0 1.630E+0
    7
    22.51575
    2.E+1 2.3E+1 2.25E+1 2.252E+1
    8
    397.0749
    4.E+2 4.0E+2 3.97E+2 3.971E+2
    9
    14356.3
    1.E+4 1.4E+4 1.44E+4 1.436E+4

  52. #52
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,525

    Re: what do you lack in excel?

    Thanks shg. Nice formula

    To Jeffrey, I've done that with Conditional Formatting but it's a pain as there needs to be a rule for every likely decimal place

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Failed job interview because of lack of excel skills...
    By listerdl in forum Excel General
    Replies: 4
    Last Post: 08-17-2016, 10:37 AM
  2. Does excel really lack this function?
    By dahlniko in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2015, 03:36 PM
  3. Does excel really lack this function?
    By dahlniko in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-29-2015, 09:20 AM
  4. my lack of excel formula knowledge
    By blogger153 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2014, 12:44 AM
  5. Excel 2007 : Excel Help menu - lack of detail
    By jsgriesel in forum Excel General
    Replies: 2
    Last Post: 03-16-2010, 04:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1