# Off Topic > The Water Cooler >  >  what do you lack in excel?

## tim201110

Maybe some MS executives will see it  :Smilie: 
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

----------


## Tony Valko

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()

----------


## tim201110

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

----------


## snb

> 4 the possibility of using built-in lists (months, weekdays) without creating them in a WB






```
Please Login or Register  to view this content.
```

----------


## AliGW

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

----------


## tim201110

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

----------


## tim201110

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

----------


## snb

> 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.
```

----------


## Whizbang

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

----------


## AliGW

> 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?  :Wink:

----------


## Richard Buttrey

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.

----------


## Logit

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.    :Cool:

----------


## AliGW

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.

----------


## FDibbins

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



it already is

----------


## Pete_UK

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

----------


## FDibbins

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

----------


## MrShorty

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%.

----------


## FDibbins

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

----------


## MrShorty

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

----------


## Pete_UK

> 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

----------


## CK76

> 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.
```

----------


## Whizbang

> it already is



This kludge-y thing? I don't see anywhere to resize it. I have Office 2013.

----------


## FDibbins

OK, Pete, understand now  :Smilie:

----------


## FDibbins

> 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

----------


## AliGW

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

----------


## FDibbins

eval.JPG
hope this comes out.

You mean this?

----------


## Whizbang

> Attachment 495715
> hope this comes out.
> 
> You mean this?



No, I mean if you go to Formulas -> Formula Auditing -> Evaluate Formula

----------


## FDibbins

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

----------


## AliGW

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

----------


## FDibbins

dont use it, but to each their own  :Smilie:

----------


## AliGW

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

----------


## Tony Valko

> 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.

----------


## :) Sixthsense :)

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  :Frown:

----------


## tim201110

> 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

----------


## ChemistB

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

----------


## JapanDave

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.

----------


## MrShorty

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."

----------


## tim201110

> 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

----------


## tim201110

> 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  :Smilie:

----------


## teylyn

> 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

----------


## Pepe Le Mokko

Vielen Dank Teylyn

----------


## tim201110

> 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

----------


## Doc.AElstein

> ...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

----------


## AliGW

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!  :Wink:

----------


## jeffreybrown

> 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.

----------


## Doc.AElstein

> 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..  :Frown:    .. so I get caught sometimes   :Wink:   ! 

_ Escaping just now actually  :Wink:   :Smilie:  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

----------


## shg

> 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

----------


## jeffreybrown

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.

----------


## tim201110

> 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

----------


## shg

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

----------


## shg

> 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

----------


## ChemistB

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

----------

