Menu
What functions is Excel missing ?
Today a simple question, What functions do you believe Excel is missing?
In the comments below list what functions you believe Excel is missing?
They may be Mathematical, Text, Date, Statistical, Chemical, Biological in fact what ever you think Excel would be better off to have as an inbuilt function.
Please provide as much info as you think we need to understand the requirements, but don’t write a manual.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « How many hours did Billy work? [Solve this] | CP036: How to do trend analysis using Excel? » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
217 Responses to “What functions is Excel missing ?”
There should be a color function in Excel which will make easy to analyse.. Something like
If(color(C4)=color(d4) etc..
This may provide more controls.
A good function addition
This already exists though not as a standard function. You can use the Get.Cell xl4 macro function (not VB) for this. More info here:
http://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html
Ben.
not really a function, but I'd like to be able to put exclusions in a filter. Now you can filter to include only entries that contain a string, but I'd like to be able to filter to *exclude* entries that contain said string.
That already exists, at least in 2013. Under Text Filter you can select "does not contain" and type in the string.
I perform a lot of reconciliation work in Excel and need to figure out which figures in a sample set tie back to a single target number.
I'd like Excel to look at the whole sample of numbers and, in a goal seek style, try adding different combinations of numbers multiple times until it finds the subset of numbers that, when added, match my one target number. Does this already exist as a function? If not, I think that would be a valuable function.
Ben.
yes!
Hi
This facility is already available in excel. You can use SOLVER to solve this problem.
Thanks Anant Jain
Function (no VBA) to concatenate different cells.
You can already concatenate different cells, either with the formula =CONCATENATE(CellRef1,CellRef2,...) or, if your spelling is not great, =CellRef1&CellRef2&...
Or do you mean something else?
The usual preference is to be able to pass a range of cells to the function. Current CONCATENATE function makes you input each cell individually. Would like something like:
=BetterFunction(Delimiter,SomeArray)
FormulaDesk has this UDF: FD.Text.Concatenate which does what you want, I think, or not? Any suggestions for improvements are very welcome 😉 http://www.formuladesk.com
As per Luke Ms post, working with ranges a cross columns and rows
I would also like a CONCATENATE function that can take a range and allow me to choose a delimiter to put in between all the values.
Thankfully, this is coming to Power Pivot in Excel 2016.
And it's already available in Power BI:
http://blog.crossjoin.co.uk/2015/03/19/concatenatex-dax-function-in-excel-2016/
DateDiff... its in access, but something similar with ability to group by buckets would be great
DATEDIF is in Excel
Already exists in excel
DateDif(), syntax - (enter earliest date, enter latest date, "d") returns difference in days, "m" returns difference in months
How to find this function ... using version 2007 but cant locate it ... googled online says its in 2007
its a hidden function, just enter = datedif() into a cell
Just be careful with datedif and months. The number of months from
11/30/2009 until 12/31/2009 = 1
11/30/2009 until 01/31/2009 = 2
11/30/2009 until 02/28/2009 = also 2
the 28th of the month is before the 30th of the month and therefore it doesn't count as a full month.
http://www.pcreview.co.uk/threads/datedif-gives-wrong-result.3944637/
And there's even NETWORKDAYS() which will give you the amount of different work days between two dates:
http://spreadsheetpro.net/number-of-weekdays-between-two-dates-in-excel-and-google-spreadsheets/
=MEDIANIF() or =MEDIANIFS()
Like the function =AVERAGEIF() or =AVERAGEIFS() but to calculate de MEDIAN
I think dynamic concatenate functions
I'll second that!
I need both of those.
Maxif and Minif, I know you can work around with array formulae but these seem like fairly obvious formulae to build in as standards. Also, some kind of IFERROR/OR combo...so at the moment I use:
=IS(IFERROR(condition))=TRUE,"Option1","Option2". IFERROR has it's uses but it can't support an either/or scenario
You can use the AGGREGATE function (xl2010 + later) to create a MAXIF and a MINIF see article below.
http://intheblack.com/articles/2013/12/31/excel-yourself-array-for-a-good-solution
I'd like the charting functions to include boxplots. Now it is a tedious process to construct boxplots.
Boxplots will be available in Excel 2016.
You can get all new charts added in Excel 2016 here ~ http://www.exceltoxl.com/index.php?p=443&more=1&c=1&tb=1&pb=1
Oh, my god. Excel is getting waterfall charts!
Hi,
Do not make DATEDIFF as a hidden function.
The possibility to use the vba function VAL (which returns numbers into a string) in as a worksheet function.
Hoss
First VBA VAL converts numeric strings in to numeric like as "1" to 1.
Xl already have a function Value() for the same.
Thank you Deepak.
The VBA function VAL() accepts string as input and returns the numbers found in that string. Some examples:
Val("10 Main Street"). Result: 10
Val("34 10 Main Street"). Result: 3410
Val(" 34 10 Main Street"). Result: 3410
Val(" 34 - 10 Main Street"). Result: 34
Val("075"). Result: 75
A function that provide a Vector array. Something like =Vector(10) would give a Vector array 10 rows long.
I'd like to see a version of the FIND() and SEARCH() formulae that can find the nth instance of a string rather than the first. The work arounds for this are horrendous!
For example I want to find the position of the last (or 4th) "\" in "C:\Users\DeskLamp\Documents\Excel_Files"
Median calculations in Pivot Tables
"WORDING NUMBER" function.
Converts numbers to texts.
Such as; 66,513.81 $ to
sixtysix thousand fivehunderedthirteen and 81% $.
I already prepared a template for this issue.
I've just added some functions to FormulaDesk to do this for you! See a list here: http://www.formuladesk.com/functions.html
Anybody who wants to have the templates in English and in Turkish can send me an e-mail to mgolcer@yahoo.com
A way to have a volatile function like =TODAY(), that would only calculate when the file is opened or saved, and not every time the workbook recalculates anything.
My company uses an Excel Add-in that actually addresses the volatile functions (they only calculate when you update THAT particular cell), but it would be nice for Excel to have something of that type as native.
Perhaps something like: =STABLE(TODAY()) or =NV(TODAY())
FormulaDesk has a UDF called FD.Utility.RemoveVolatility(volatileCell, targetAddress)
It only updates the value of targetAddress when the value of volatileCell actually changes.
Does that work as you'd expect? Of course, a native version would always be better 😉
http://www.formuladesk.com
Yes Gareth, that is similar to what the function in our Reports add-in does. But, as you said, native is always preferable.
Thank you for pointing me to the formula desk site however, I see that it might come in handy for other things. 🙂
I guess a workaround to this is adding a macro in the Workbook_Open or Workbook_Close functions.
Something like: Range("A1").Value = Format(Date, "DD/MM/YYYY")
I know how to do this through GoTo Special (blanks), entering the formula, and Ctrl+Enter, but Excel should have a function to fill in all the cells below a populated cell with the value from that cell.
By selecting the column or columns you needed to “fix”, then clicking the button, it would fill in the blanks with the value populated in the non-blank cell above.
Twrr
Twrr gain calc function
For data visualization purpose, it would be good to have a tool in excel to auto create the dashboard based on the selected rows and columns. In the current version (even the latest 2013) there are not much sophisticated tools available, unless we buy 3rd party add-ons.
To have the option to not only put absolute references on a cell but on a whole worksheet
Dictating data into cells. Making data entry by voice
I would really like to see a "do nothing" formula for use in long IF statements. I also have co-workers that are irritated that Excel does not have a mode function.
Which cell is the cursor in/which cell has been selected.
There is a 'Crosshairs' button on the FormulaDesk ribbon tab. It draws arrows pointing to the current cell. Is that what you'd expect?
I would like something more dynamic. As an example if a user selects a cell in a table then I want to use conditional formatting to highlight the entire row of the table.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CurrRow As String
Dim CurrCell As String
CurrRow = ActiveCell.Row
CurrCell = ActiveCell.Address
With Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows(CurrRow).Interior.Color = RGB(150, 150, 150)
End Sub
This is possible by using CELL function in combination with Conditional Formatting. mgolcer@yahoo.com
I would like to see a "Contains" function in Excel, to be able to use in Formulas. As far as I can tell, there is none. I have used the ISNUMBER (SEARCH) in IF Statements though and that seems to work. But a "Contains" seems easier to me. I know you can use Contains in Filters, but I am talking about in Formulas (IF Statements, etc.). Thank you.
AVERAGEIFS
MAXIFS
MINIFS
I would like to see a Million () function which would simply convert the current number and divide it by 1000,000. At the moment I have written a simple custom function but I have to copy it in to every file.
I would also like to see a CountbyColor function. Both these functions will be very useful for dashboards
John Heinzel's comment reminded me of something else - more of a QAT fix than a function
For features like Go To Special - which have dozens of options but you may need only one repeatedly - it should be easy to add just that ONE feature (like Go To Special - Formulas - Errors) to the QAT.
So when I'm working on something and I only need to highlight the error cells repeatedly, I can do that with a single button click.
I would like to see a Million () function which would simply convert the current number and divide it by 1000,000. At the moment I have written a simple custom function but I have to copy it in to every file.
If you really want to convert it, put 1,000,000 into a cell. Copy it. Select the cells you want to convert. Paste Special. Divide.
I tend to keep my numbers unaltered but use a custom format [ #,,;-#,,] to display the numbers in millions.
1. A concatenate function that can handle arrays/ranges
2. Some way to reverse contents of cell, equivalent to VB RevStr function
Hi Luke 🙂
I was wondering for point 2. You could always have a sub macro that runs whenever you make changes to current file. It'll copy the existing sheet to a different file so when you try to roll back it'll just copy the data from the different file. Like having multiple version that overwrite each other.
This is admittedly pretty minor, but it impacts me often: I'd like to see a simple function that converts military time into "standard" time.
Do you mean 24Hr to AM/PM?
You could always directly change the number format or use a formula like
=TEXT(A1,"hh:mm:ss AM/PM")
Forgot to say to convert to time you'll need to add *1 at the end
=TEXT(A1,"hh:mm:ss AM/PM")*1
Disclaimer: I'm on 2010, so don't know if 2013 has it:
Filter for all colors. I can autofilter for any one color in my range, or for no color, but I can't filter (without a macro) for all colored cells.
Convert a filtered range to values.
Pastespecial Transpose Link. I can either transpose, or paste a link. I can't do both at once.
Protect by cell type, for instance Protect Formulas, or Protect Constants.
im very visual and take advantage of the conditional formatting to point out things by color often. One thing I hate is, that although there is a option to filter by color, it only allows you to choose 1 color. There should be an option like in the pivot filter function to "choose multiple" where it turns into check boxes and you can view multiple colors.
This may exist, but I'd like to be able to set conditional formatting to a column and not have it get screwed up by other people using copy/paste in the worksheet. Whenever someone else opens a spreadsheet I've made, I always end up getting called over to explain why my formatting doesn't work anymore. Invariably it's because they've been copy/pasting instead of filtering/sorting and the conditional formatting rules have multiplied like rabbits.
You'll need to tell them to paste as values rather than direct copy paste.
CTRL + C for copy
ALT + E + S + V for paste values
I sometimes use date math in conditional formatting functions to help track due dates and whatnot visually, like:
=DATE(YEAR(A18)+5,MONTH(A18),DAY(A18)-30)>TODAY() which would allow me to see when it's within 30 days of 5 yrs expiration date, in this example. Also, this saves from hard-coding something like "+365". This gets confusing to teach people.
Perhaps a function like: =DATETRACK(relative_date,+/-yrs,+/-months,+/-days) ... =DATETRACK(A18,5,0,-30) would yield TRUE once the actual date was greater, triggering the same desired effect
CAGR function! I know: http://chandoo.org/wp/2014/04/29/calculate-cagr-using-excel/, but it would be nice to have it in a formula.
=CountUnique(range or named range)
And
Advanced Data validation
1. -> List -> Unique entries from range
2. nested lists: aka list B depends on list A selected entry
Not a function, but a much needed feature IMO.
We all know all of the above is possible with VBA or array functions, or some PIvot table /VB combos, yet the idea here is missing direct fucntions
I suspect Chandoo is planning to create an Addon and collecting ideas/needs 🙂 which is great IMO
I was also going to propose CountUnique. But then discovered a CountDistinct function (excel 2010)
oh 😐 I need to upgrade then!!
A ReverseRange function: given an array or vector it will reverse the numbers; no sorting involved.
Example {1,12,5,3,2} ReverseRange (range) = {2, 3, 5, 12, 1}
As opposed to using a bunch of nested If statments, I would love to see something similar to this:
=SELECTCASE(criteria,value1,result1,...,valueN,resultN)
I fully second this one.
I third that. I believe that one of the advantages of a CASE statement is that, in VBA at least, Excel stops evaluating the formula when it finds the first TRUE case.
Use an inline vlookup
=vlookup ({1,2,3}, {"one", "two", "three"})
The way Excel handles complex numbers is truly pitiful. It is a hacker's afterthought. It doesn't have to be MathCad, but it could be made an awful lot smoother.
MAXIFS(), MINIFS(), MEDIANIFS(); then no need for array functions.
SHEETNAME(); I know there is a workaround for this using a combination of functions, e.g. =REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""), but it would make life easier.
USERNAME(); for this there are also workarounds, but can we do it KISS (Keep It Super Simple)?
However, functions are not my biggest challenge in Excel. There are usually workarounds.
Instead of expanding on the list of functions I would prefer Microsoft to expand on the options for protection of an Excel sheet.
For instance, I would like to partially protect a worksheet containing an Excel table that is used for data entry. Currently if you protect a worksheet containing a table, the user cannot add new rows in the table. However, if you do not protect the table columns containing formula, the user can mess-up or delete these formulas.
I also would like to have the option leave a cell unprotected for data entry but protected for the conditional formats for this cell, especially if they contain formulas. Currently a user can remove a cell format via copy/paste.
Cheers.
For username there is a simple macro I use
MsgBox Environ("Username")
I would like the ability to get chapter style numbers to go in order.
I want 1.1, 1.2, 1.3 . . ..= 1.9, 1.10, 1.11 NOT 1.1, 1.10, 1.11, 1.2, 1.3 . . . 1.9. This is really infuriating when you are trying to create nested numbering that groups like things together. As an add-on, subtotaling by each place value would also be fabulous.
Yes, please!!!!
You just need to put a 0 in front - 1.01, 1.02, 1.03 etc and it will sort properly. I used to have this problem until I did this.
In my Excel Courses I've found too many people that aren't clear about how to calculate "a percent of" (like in Pivot Tables).
I think that a function as:
PERCENTOF(value, One of: Range of all values that add to 100% Or cell with represents 100% of Values)
will be a great addition to Excel' Functions.
Carlos M.
The functionality that I miss comes from the old LOTUS 123. I still remember the keystroke sequence "/RNT" which stood for "RANGE - NAME - TABLE". This would create a two column table in the spreadsheet starting from the location of your cursor at the moment you entered "/RNT". The table listed all "range names" in column one and in the second column it listed the cell addresses covered by each range (citing the top left cell and the bottom right cell).
0. BOX PLOT!!!! Rest are nice to haves...
1. Square root / any fractional power does not operate on Complex Numbers
2. Eigenvalue and Eigenvector
3. instead of specific SUMIF, AVERAGEIF and xIFS, a CALCULATE function like we have in ppvt would be awesome
4. BLANK() and IFBLANK() would be more elegant than
=IF(x="",t_arg,f_arg)
4) There is already an ISBLANK function that gives a true/false value
I would like to see something like an IFTRUE function (similar syntax to the IFERROR function). Currently, if there is a long formula in an IF function that is evaluated to be true, and if we want to return that same value, we have to repeat the long formula. If we need to have long formulas in nested if functions, this makes for an extremely long and confusing formula. An IFTRUE function would greatly reduce the size of long formulas in this case and would be easier to follow.
Currently, a complicated nested formula looks similar to this:
=IF(evaluate_a_super_long_formula_if_it_is_true,the_true_value_is_the_value_of_the_super_long_formula_we_just_evaluated, if(evaluate_a_SECOND_super_long_formula_if_it_is_true , the_SECOND_true_value_is_the_value_of_the_SECOND_super_long_formula_we_just_evaluated ,value_if_not_true))
It would be nice if the formula would be more like this:
Basic formula: =IFTRUE(logical_test,[value_if_false]) - -- where if the logical test is true, it returns that value without rewriting the formula out again.
So, in the end, the formula of my nested formula shown above would really be:
=IFTRUE(evaluate_a_super_long_formula_if_it_is_true,IFTRUE(evaluate_a_SECOND_super_long_formula_if_it_is_true , ,value_if_not_true))
+1 !
+1
This would be amazing
yes!
Native support for regular expressions. This would include functions and regexp Find/Replace.
I love the simplicity of the “Iferror” function. I would like to see a simple short formula for if a cell is blank. Something like
=IFBLANK(value,value_if_blank)
Where it could plug in a specific value if the cell is currently blank.
You can use IF(ISBLANK
I would love it if I could set a default number format for pivot tables! Every time I create a new pivot table, I always have to change the formatting to make it readable.
You can use Debra Dalglieshes (Contextures) very useful Pivot Power add-in (very cheap and very worthwhile if you do a lot of work in pivot tables) as I do.
Ability to use RegEx functionality for validation, cleaning, and formatting.
The comment about conditional formatting mutiplying like rabbits reminds me of another annoyance. So yes, a new rule is created when lines are copied. So what happens is, a clean up is necessary after many accumulate under the rule manager. But instead of being able to select multiple duplicate rules at one time (by holding down the 'control' key one would assume) or an option to 'select all', you sit there and click each one and then click delete for about 10 min.
Don't worry lol. I wrote down that you can easily teach people how to value paste instead of direct paste
a VLOOKUP to retrieve nth occurrence:
=vlookup(a1,sheet2!B:D,3,TRUE,2)
In this instance the last parameter would say find the second value in sheet2 column B that matches the value in A1. If there isn't a second match, it would return NA just like the current VLOOKUP function does.
I fill in calendars with items using SUMPRODUCT to get multiple tasks for a certain date. It works, but confuses most people when I give it to them. A modified VLOOKUP would be intuitive and easily understood.
ISNULL
Don't see an ISNULL in Excel 2010.
There is an ISNULL but as far as I know I've only seen it VBA
I need more statistical analysis tools.
I can perform some basic stat tests in Excel, but advanced functions are not possible without significant programming.
All of the stat tests in Excel assume the data being analyzed are normally distributed. If this assumption isn't actually valid, then the results are technically skewed. That said, the first thing I'd love is a normality test such as: Anderson-Darling, Kolmogorov-Smirnov and/or others.
If the data is not normal, I'm forced to use non-parametric tests. Most social science research follows non-normal distributions.
To give some examples:
Parametric => Non Parametric
Pearson Correlation => Spearman's Rho Correlation
T-Test => Mann-Whitney U
ANOVA => Kruskal Wallis
I do know I can convert a Pearson correlation to Spearman by ranking my data and running a Pearson test on the ranks.
It would also be nice to investigate multiple arrays of data to see if they form clusters or factors. (Exploratory Factor Analysis with Eigenvalues)
It would be equally nice to be able to test the reliability of factors to produce repeatable results in follow up data. (Chronbach's Alpha)
Would also be wonderful to have a package which helps perform various Six Sigma statistical tests such as:
* Capability Analysis (Cpk/Ppk)
* Control Charts
* Experimental/Factorial Designs
Anyway, this last bit is kind of specialty stuff on the Six Sigma end, but the other stats are crucial to all social and/or applied researchers across many disciplines from psychology to business to healthcare to fill in the blank.
In the mean time, I'll keep using Excel to clean up my data, and Minitab to analyze it.
Found some here a while back (free excel addon)
http://www.real-statistics.com/
TWO FUNCTIONS ARE MISSING:
JOIN - To return a string created by joining a number of sub-strings contained in an array (incl. all Arguments).
SPLIT - To return a zero-based, 1-dimensional array containing a specified number of substrings (incl. all Arguments).
*** Both are well known & used in VBA coding but for the average user (Newbie in VBA) these two functions should be embedded as Worksheets Functions.
*** If I'm not mistaken both functions work fine in "Google Docs" spreadsheets.
------------------------------
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
FormulaDesk has this UDF:
FD.Text.Concatenate(arg, separator)
arg: Array of text values.
separator: The separator (text) to join them with.
Is this how you envisage the JOIN function working?
Structured function view, this is not a function, but is a related feature, that I'd like to have. Specialy for complex "mega formulas".
FormulaDek has a few features to help you with this: the 'Formula Explorer' will display mega formula in a very easy to understand way, with rolled-up sub-results as well.
Clicking 'Show Formula Steps' will create a new sheet and break the formula into steps.
Also, in the Formula Explorer you can right-click any reference in a formula to 'import' the formula in the referenced cell into the current formula - just in the display - you can then copy and paste if you desire. This makes it easy to create formula across many cells while developing, but then combine them into a single mega-formula later.
I'd like to be able to easily format numbers to include 'st', 'nd', 'rd', 'th' example 1st, 2nd, 3rd so on ... I know you can use choose etc but easy format would be sweet!
I've just added a function to FormulaDesk to do this for you. It's called FD.Text.NumberToOrdinal. You can see more info here: http://www.formuladesk.com
http://www.mrexcel.com/forum/excel-questions/546535-excel-add-st-nd-rd-th-number.html
Look at other sites:
* MVP sites, like dmcritchie, etc
* Google sheets
=SORT(Array, No, SortOrder, Distinct, Column/Row)
No=Column or Row number (can be an array)
with SortOrder -1,0=Not sorted,1 (opt.)
with Distinct 1,0 (opt.)
...
=CONCATENATEX(Array, delimiters, move)
with move=0,1,2,3,4 0=no movement, 1=left,2=right,3=down,4=up
=IMAGE(Array, move)
Array=one picture/binary image or many
Google function: =IMAGE(url, [mode], [height], [width])
=ITERATE(From, To, Step)
=MERGE(Array1, Array2, ...)
=FILTER()
=DISTINCT()
=FILENAME(), inkl. sheet name
* DateDiff make it official
....
A way to create a truly blank cell, not "".
A way to create a truly blank cell, not “”.
The ones I regularly use as UDF which I think should be standard functions:
IFZERO like IFERROR
COLUMN ALPHA - return "D" when =column(D1)
CELL("filename") - should have an optional switch to just show the xxx.xlsx name not the full path
For filename you could use a simple macro like
Rnage("A1").Value = ActiveWorkbook.Name
Spelt Range wrong 😛
-A much more simpler way to create waterfall charts
-The ability to break the Y axis in excel
Waterfall charts will be a built in capability in Excel 2016.
Calculate median in pivot table.
Count distinct VALUES in a column.
The MEDIAN will be available through Power Pivot for Excel 2016.
Check this link: https://support.office.com/en-us/article/MEDIANX-Function-DAX-5c719d19-1b7d-47eb-bc86-4dd30fd56886
A function for calculating TAX or Commission.
It's already there:
*
stdevifs. yeah there are formulas, but a nice clear ifs command for standard deviation and standard error would be nice.
a weighted average function
I use SUMPRODUCT() for this, but it would be great to have it all wrapped up in one function!
For instance, =SUMPRODUCT((Weighting_Values_Selection)/Sum_Of_Weight,Values_To_Be_Averaged)
I think a concatenate along with a delimiter which can be used over a range...something like SPECIALCONCAT(Range,Delimiter)
Does the FD.Text.Concatenate function in FormulaDesk do what you want? http://www.formuladesk.com
Hi Gareth - I have the formuladesk installed in my personal system..though never had the need to use this functionality at home...I am talking more in terms of having this as an inbuilt functionality in excel.. reason, though I have the formuladesk in my personal system but due to compliance reasons I can not have this installed in official system...
Hi Asheesh. Thanks for pointing that out. Of course it would be great if all there capabilities were built into Excel! But, in the meantime, what do you think it will take for your company to allow employees to install FormulaDesk? Is there anything I can do to make assure them that it's ok to install? Anyone I should contact?
Dear sir,
I think kindly develop the logistics and customs related function in excel.
Regards,
Karthik
I think there should be built-in excel function (like UDF SpellCurr) for converting amount in words.
the following also
MAXIF
MINIF
so we can avoid using combination CSE with MAX(IF(.... MIN(IF...
and:
ConcateIF
ISDATE
=Countif(Colour)
=Countif(Red)
=sumif(colour)
=averageif(colour)
we need a formula to count or take the total based on the format if the cell.
Excel does not contain calculating Root of a number,
Which makes it difficult, altough we can apply it indirectly
Measures to be taken to find nth Root of a number directly, so it is easy for finance, Mathematicians and physicians too
1. Unique Count in Pivot Table
2. A quicker way to convert crosstable into Flat tables (Currently I use Index - would prefer a quicker one button function)
Excel 2013 addresses #1
Load range into the data model and then you will have the option for Distinct Count when choosing Value Field Settings > Summarize Values By > Distinct Count (at the very bottom below Varp)
Thanks for clarifying Justin. I am currently using Excel 2010 and havent had a chance so far to work with Excel 2013. Its really good to see this option now in Pivot table. Maybe one of these days I'll see if I can get to work on 2013. Thanks again.
In Power Query ( which you can load as an Add In to 2010
https://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=899b0450-9057-44cd-ae7b-85f5b25fd604
there is a built in feature called Unpivot - which will Unpivot a range of columns for you with one click.
Unique function or distinct function without using advanced filter
Auto filter by columns
Easier way to make a dynamic range vs. using offset
More built in statistical functions similar to what the popular excel add ins provide
Counted items next to the text in slicers. Ex: South (5), North (3)
MAXIF, MINIF
1)It is possible to consolidate data appearing in different sheets in non unique form. After having done any changes in any of the sheets as to values or form, currently there is limitation as the consolidation is not done. It will be better if we refresh the data then data consolidation should be done automatically.
2) Countif ( By Color )
Countifs ( By Color )
Minif ( By Color )
Maxif ( By Color )
Maxifs ( By Color )
Averageif ( By Color )
Averageifs ( By Color )
Sumif ( By Color )
Sumifs ( By Color )
3) Financial
Simple Payback Period
Discounted Payback Period
I have loads of functions Microsoft could add.
1. Count unique values
2. An IfColour()
3. Count the No of Words in a Cell
4. A Sports category with functions for crickets stats inc Duckworth - Lewis, calculate head to heads, unique league table rankings.
Figure cannot convert into text (English)
I think "CONCATENATEIFS" is missing
To concatenate based on a condition, I'm using a VBA function, however it'd be great to have this as a default function within Excel
=isformula(A1)
I think the SUMIF(), COUNTIF() functions in 3D format so that they can be used across sheets will be very useful.
isdate()
to be able to check if a cell contains a date.
Assuming Column A has dates/string & column B has formula
=IF(ISERROR(DATEVALUE(TEXT(A1,"dd/mm/yyyy"))),"False","True")
I might be missing something, but I find extracting strings from texts difficult. If they could have a function which conditionally retrieves a string from a string, that would make things easier. In other words something that means that I don't have to nest several find()'s within a left(), mid() or right(), would be nice.
Otherwise the ability to have a multidimensional lookup ( or at least 3d) stacked between sheets.
A formula that returns a letter of the column instead of number (AA, not 27).
This formula only works up to number 256.
=ColNo2ColRef(COLUMN())
Hi,
If suppose one has to type the hours minutes seconds , Even though, the cell is formatted to accept time format, as chosen, you have to type in the format to accept the same. For example, if you choose, 01:30pm format from excel to a cell, then you have to type in the same format to accept the data in correct time format.
I want a funtion, where if you just type 130 it should be made to the above 01:30 pm, without typing the : inbetween the 1 and 30. Could you do something?
Hey,
There is a workaround for this...Make use of excel's 'Automatically insert decimal' and replace "." by ":" using find and replace.
But this works only for hh:mm format.
Regards
Rudra
Assuming A1 has Time in 24Hr format e.g. 130 = 1:30AM / 2300 = 11:00PM. And B1 will have formula
Also assumes you only uses hours & minutes. Not seconds.
=IF(LEN(A1)=3,(0&LEFT(A1,1)&":"&RIGHT(A1,2)&" AM")*1,IF(LEN(A1)=4,TEXT((LEFT(A1,2)&":"&RIGHT(A1,2))*1,"hh:mm AM/PM"),"NA"))
Hi,
I mean a function or button for doing this. Do not you know, how much this takes if one has to manually insert : in hour format in each and every cell.
If decimal could be had why not try a function for this. I am not tech savvy to do
I would love to be able to put the power of Solver in a function. Instead of running the Solver add-in manually whenever I make a change in my model, I would like a function that automatically recalculates an optimal solution.
What about a formula that magically makes a beer appear on your desk?
="Beer"
Lol 😛
I'd love to see a function that builds on Excel's ability to average data. I'd like to see a function that calculates an average monthly value based on known starting and ending points.
For example, if I want to calculate the average monthly print volume of a printer in my office. I know how to build the formula manually. I'd like to see a built in function to complete this task.
Analysis Start Date: 3/8/15
Analysis End Date: 5/13/15
Starting Page Count: 10,000
Ending Page Count: 15,000
Current Formula:
(Ending Page Count - Starting Page Count)/(12*(Analysis End Date-Analysis Start Date)/365)
Answer: 2,304
MINIFS (similar to AVERAGEIFS). And perhaps MAXIFS, but I simply had a need for MINIFS. I now solved it through an array formula (which I don't like to use....)
We have formula for networkday similarly excel should have networking hours formula.
syntex should be like this
Networkhrs( start datetime,end datetime,login time , logout time ,holidays)
LastNumberInRow
LastValueInRow
FirstNumberInRow
FirstValueInRow
same for columns
Not sure if this was mentioned yet or not (saw the MedianIf) ... but PercentileIf(s) and QuartileIf(s)
- add ability to use range name in pivot table calculated fields.
- add ability to use in pivot tables, calculated fields with a custom calculation (ex %of column) in other calculated fields
- add our own icons set in conditional formats
- vba : when driving pivotfields/pivot items or slicer items, have a way to quickly unselect all elements (Currently we are obliged to loop on all elements and unselect each element, which could be long in case of big list. There are workarounds, but it is tricky)
- slicers : filter a slicer! (Like with a standard filtered field). For example i want to only see slicer lines like FR*. In qlikview this function is very helpful
- ability to count unique values (formula)
- new built in event mouseover cell
@Laurent: Check out my post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
It turn out that you can very quickly hide all but one PivotItem programatically if you make a temp copy of the Pivot, make the field of interest in the temp into a Page field with .EnableMultiplePageItems set to False, and then hook it up via a slicer to your original Pivot. This forces the original PivotField to have the same filter setting – just one item visible. But it doesn’t make that original Pivot have the same layout. So the original pivot can still be say a Row field where you can then merrily make additional items visible.
SWITCH-CASE (or select-case, whatever)
For that matter - why not a custom function builder without VBA? Like we can store our custom charts - should be able to store our custom functions as well!
"Search as you type" in slicers. If there is a long list in a slicer, this would add great functionality to reach the desired value.
Similar functionality in Validation Data Lists is also needed.
Not a function as such, more functionality. I would like to be able to click on one chart and format paint all the styles to another. I am still using Excal 2007, so this may be available by now. I know about saving a chart as a template!
Along with excel I also work in google spreadsheet and am fond of google's function like filter, sort, split etc. Would be nice if excel incorporate these function in next version.
Not function but I would also like to have:
1. Filter by multiple colors
Regards
Rudra
Sorry I forgot to add,
Extract Number only and text only from alphanumeric string would be great.
There is a way out in excel using formulae..
I think the filtering functionality could be made more user friendly.
If there was an option to invert the selections in the filter list it would be faster.
Also, when there is a huge list of data being filtered, and you have to scroll all the way to the bottom to get "Blanks". It would help if it was at the top.
I agree, Nick. I've implemented something to do this with PivotTables: see http://dailydoseofexcel.com/archives/2013/12/03/inversely-filter-a-pivot-based-on-an-external-range/
I'm working on a freemium add-in that will include this functionality for PivotTables. I'm yet to look at options for Tables.
3D scatter plots
negative time
Count unique Function
I forgot one:
Bubble charts : be able to change color of bubbles with negative values
Hi-
One function that I'd like in excel would be to convert numbers to words. This function is used in many places and like invoicing, Salary letters sent to banks for salary transfer, etc,etc.
CONCATENATEIF(S)
a "put" function.
places a value (from a dashboard, perhaps) into any designated cell row, column, any page, any other file.
a "put" function
Not new functions as such - just the ability to use all existing functions in 3D (like basic functions SUM, AVERAGE etc can) to calculate through sheets. SUMIF and SUMIFS won't work in 3D.
Hi Team,
there should be something like
=sumproductif()
=reverse()
=NumToText()
=running() ( for i.e. 1st,2nd,3rd)
- Ability to create search box (like google) in particular cell
-complex formulas can be used in Pivot Table
Thanks
Istiyak
It would be of great value to chart fanatics like myself to have Excel provide the functionality of specifying a column of points used to color the data points. The colors would be representative of either discreet values or a user-specified binned range of values. As an example, suppose you want to plot a person's age (column A) versus income column (b) versus the year of car that they drive (column C).
The points would be colored by either the discreet values in (C) or a user specified bin of years, say, for a 5 year binning, 1960-1965, 1965-1970, etc. I frequently do this using scientific data (e.g. rock porosity vs. permeability discriminated by radioactivity) with my own developed tools, but it sure would be nice if Excel offered this internally.
Better scenario manager. Would like automated outputs for several scenarios across several input variables. Want to fully define format of output to corporate format. Scenario manager, data tables or offset work around doesn't cut it!
@Rob
Data Tables can handle any number of inputs and outputs when setup correctly and so becomes a great scenario manager?
Conditional formatting improvement to easily apply format across a row (without special tricks).
=IFS(A1="Apple";123;A1="Banana";456;789)
Same as
IF A1="Apple" THEN 123
ELSEIF A1="Banana" THEN 456
ELSE 789
This is already present with nested If functions
=If(A1="Apple",123,IF(A1="Banana",456,789))
If you want something more complex then use Index/Match to lookup a value in a range from a key in another range.
People will allways copy/paste ALL instead of paste values.
So the formating and the conditional formating will be gone evantually.
That is why I always hard code the conditional formating into the macro's worksheet activate event.
What I need is a worksheet protect option which will lock the paste all, and which will only allow to paste values. Voila half of my issue has been solved.
After copying a complete row range or a whole sheet sheet, I would love to have Row Widths as a Paste Special Option to work just like Column widths or a paste special fit to size.
more missing options that I will like to see are:
- To be able to insert picture within the cells.
- Under number format, to be able to see more than 20 digits, not only 15, even if our number start with a 0, be visible.
Example: 08456891070060510302
If you enter it in a cell you will see: 8456891070060510000
- a command button to be able to insert multiple checkboxes in the column range that we specify.
I second your first and third points
(not that i dont with the second, but i dont care ;))
Issamecolor(A1;B1)
Issamefontsize(A1;B1)
Issameshading(A1;B1)
Issameborder (A1;B1)
Issameconditionnalformattingrule(A1;B1)
Better indirect conditional formatting that adjusts for added columns. I've tried removing the "$"s and they just come back. Invariably someone will add a column to one of my worksheets and viola, there goes my CF- now it's behaving very strangely. Also the ability to do indirect by row, not just be column. There may be a way to do this latter part, but I'm not sure.
Conditionally color and name worksheets
Easier way to work with times.
Add a picture conditionally, perhaps with a VLookup. I have seen ways to do it, but they are very complicated.
A simpler shortcut for Format Paint.
A function that would build smaller lists (subsets) of a larger list, based on specified criteria. For example, if there was a long list of car owners with make, model, owner's name, etc... I want smaller lists created based on make for example, a list of all Toyotas, another of all Nissans, etc...
Merge two columns of data with a specified separator, and without having to concatenate first and then paste special over the top.
The ability to have multiple filters on a page. I'm talking about how you can filter a list, but then if there is another list further down on the same worksheet, and I filter that list, the upper list is no longer filtered.
Add Rounding to the formulas of a lot of cells at once, "Round(###,2)"
Conditional Print Features that would change the orientation based on defined parameters, and also change to "Fit Sheet on One Page," etc...
Protect and unprotect multiple sheets at once.
Advanced character remove or replace.
Some of these options are available via add-ons, but I would prefer they were native to Excel.
Conditionally hide or unhide worksheets
Conditional print areas, as well as conditionally print to a variety of printers.
Conditionally group or ungroup, as well as expand and collapse.
A wizard for creating formulas to pull data out of pivot tables. These formulas can get very long, based on the number of criteria, so a wizard to step through creating them would be great.
Replace one color, or formatting, for another in a selected or specified range.
Conditionally format part of a formula cell, i.e. a cell that pulls different text based on other conditions, and some of the words in those text strings could be bold, a different color, or underlined.
A calendar that could be inserted in a cell that would allow for selecting a date rather than typing one. I know there are ways to do this in VBA, but I'm not sure why there isn't a simple one in Excel.
More advanced protect features, like allowing hide/unhide, or other things, in a protected sheet, or protecting/unprotecting based on user name, or other criteria.
I could probably come up with more, but these are some off the top of my head, and it's probably enough.
Gary: That's a great list. I've got some questions regarding some things on it:
Invariably someone will add a column to one of my worksheets and viola, there goes my CF- now it’s behaving very strangely.. Yeah, I've had that happen soon. I have a plan to create a better CF dialog that will let you save and restore CF formatting. The current dialog is crap, as per my recent post at http://dailydoseofexcel.com/archives/2015/06/08/easy-way-to-back-up-cf-formats/
A function that would build smaller lists (subsets) of a larger list, based on specified criteria. For example, if there was a long list of car owners with make, model, owner’s name, etc… I want smaller lists created based on make for example, a list of all Toyotas, another of all Nissans, etc… . Would a combination of PivotTables and Dynamic PivotTable Names do what you need here? I'm working on an add-in for creating bulletproof Dynamic PivotTable Names, as per a proof-of-concept I posted sometime back here at http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/ . Maybe that would also handle your requirement for A wizard for creating formulas to pull data out of pivot tables. . I'm not entirely sure what you mean by These formulas can get very long, based on the number of criteria, so a wizard to step through creating them would be great.
Re The ability to have multiple filters on a page. I’m talking about how you can filter a list, but then if there is another list further down on the same worksheet, and I filter that list, the upper list is no longer filtered.. Excel Tables (introduced 2007) let you do that, unless I'm misinterpreting what you want.
Thank you for the follow-up questions Jeff. I took a look at your proof of concept on structured references, but the workbook I downloaded didn't seem to act like yours in the example. It was set up slightly different, with different states showing, and the number at the top was "1," and stayed "1" even if I added more cities.
I consider myself fairly competent with Excel, but alas pivot tables are one of my weakest points, so I am not sure if structured references would solve my problem or not. I can elaborate more one what I'm trying to do, but perhaps that should be outside of the forum unless you think another very long post is okay.
What I mean by "these formulas can get very long" is this
"=IF(ISERR(GETPIVOTDATA("Discount Amt",'License Data'!$A$6,"Category",$Q$1,"Country",$B$2,"Day",DAY($B18),"Month",MONTH($B18),"Year",YEAR($B18),"Currency","US Dollar","Is New",$E$5)),0,GETPIVOTDATA("Discount Amt",'License Data'!$A$6,"Category",$Q$1,"Country",$B$2,"Day",DAY($B18),"Month",MONTH($B18),"Year",YEAR($B18),"Currency","US Dollar","Is New",$E$5))"
That is one of the formulas in my workbook, and there are literally thousands of these in the entire workbook.
Thank you for the information about Excel Tables and filtering. I don't use tables that often, but I suppose I could probably use them whenever I want to filter two different sections in the same workbook.
Gary
Gary: Use the new IFERROR funciton to cut the size of those formulas in half. And by 'new' i mean Excel 2007 or later.
You should use Tables any time you have a block of structured data sitting somewhere. Then you never have to repoint your formulas or charts again, because Tables have built in dynamic ranges that are handy and bulletproof.
Re the structured pivottable reference download, I see that the formula in A2 was screwed up. Sholud have been =COUNTA(Sheet1.Jeffs_Pivot.Sum_of_Kilos)
Put that in, and you should see it works fine.
Okay, I have another one.
It would be really cool if you could insert windows within a worksheet to other places in the workbook, or even other open workbooks, that would allow you to view and edit those areas right on screen. And it would be really helpful if they were floating so they could be drug around. I know you can view other areas of a workbook via the camera, but I would also like to be able to edit them.
@Gary
Open your file
Goto the View Tab
New Window
Now resize the new window to be smaller than the original window
As you type in the new window it is immediately updated in the main window
=Multivlookup() should be built in.
1 - Add recognition of color option to the counting type functions (like: Countif/s Averageif/s Sumif/s)
so we could have:
countif(a1:a200,"Blue")
2 - word manipulation functions, where a "word" is defined as delimited by a space or character specified in the function
something like:
wordcount(range,delimiter)
word(range,word #,# of words)
findword(find_word,within_text,start_num)
searchword(find_word,within_text,start_num)
3 - unique processes including a count and a listing function
something like:
uniquecount(range)
uniquelist(range)
4 - A Select function rather than using series if nested IFs
something like:
Select(logical_test_1,value_if_true,logical_test_2,value_if_true,logical_test_3,value_if_true,...,value_if_false)
5 - native calendar drop down added to the data validation options
6 - Add Pivot Table names/fields so they can be used similarly to named ranges, and additionally be able to grab the subtotal information in a similar way
Dear Sir,
Please find herein below formula. I need help for the below.
When I enter 13.5% it has to show 1.75% But it shows only 1.00%.
Please help me. I am using Excel 2007.
=IF(ISBLANK(C10),"",IF(C100.12,C100.125,C10=0.131,C10<=0.136),(((C10-0.13)-(0.005*(ROUNDDOWN((C10-0.13)/0.005,0))))*1.5)+0.01,"Drying")))))
(If Moisture is upto 12 % - No deduction
If Moisture is between 12 % to 13% - straight deduction
If Moisture is between 13.1 % to 13.5% - x 1.5% deduction
If Moisture is above 13.6 - Qty after drying).
Hi all
Currently, it's next to impossible to dynamically freeze panes or row or columns i.e., to create a stack flow horizontally or vertically.
Yes, it is claimed it could be done through VBA coding, but having an option under Freeze Panes would make life a lot easier.
Check out this link if you do not follow the function I'm talking about: http://stackoverflow.com/questions/11266012/dynamically-freezing-panes-in-excel
@Neelkanth I've added the ability to freeze columns and rows to the FormulaDesk add-in. You can freeze and columns and they will be frozen on the right-hand-side of the worksheet. Similarly, rows will be frozen at the bottom of the worksheet. http://www.formuladesk.com
Count unique
[…] Read the full article here: What functions is Excel missing? […]
Hi, everyone!
I think that good function could grouping sheets into sections. Sometimes when I analyse couple of companies I need couple sheets of (revenue, costs, SG&A and others), so if i could group my sheets into sections (company 1, 2, 3 .... X), include into sections necessary sheets it probably could give me better navigation.
Ruslan
Excel is missing Number2Text function as a builtin.
for example
Rs. 5,342.00
=Number2text(5,342.00)
Result = Five Thousand Three Hundred Forty Two
A few things that I know there are workarounds, but would be good if they could just be part of the vlookup formula
Vlookup that works both right to left as well as left to right, perhaps with a negative number in the formula to tell it which way to go.
In Vlookups if the result is an empty cell choosing if it gives a blank cell or a 0.
A more radical idea would be to have independent page-lits or something similar in dashboards. If you have a number of data reports in a dashboard, they often don’t have the same number of cells, cell widths etc. Ideally you could design the pagelit you want and then putting this where it fits in the dashboard, without warring about how this lines up with cells above or below it. Currently this is usually done with merging cells, but that often is a pain when you try to change things etc.
RSS root sum square
RMS root mean square
Some functions can be added to MS EXCEL are as follows.
1. RANKIFS(.....)
2. STDEVIFS(.....)
3. Conditional Page Break Insertion
Some other functions can also be added to MS EXCEL are as follows.
4. Maxifs(........)
5. Minifs(.........)