Here is a list of formulas I wish MS Excel had. Alas, we need to build some work-arounds to solve them though.
- CONCATENATE (range): that can take a range of cells and churn out a big text combining all of them. Of course, here a VBA UDF alternative to concatenate range of cells.
- SPLIT (text, delimiter, part): that can take a text and split it based on the specified delimiter. We will have to use a formula based alternative to split text
- UNIQUE(list, number): that takes a list and returns the unique items based on the number specified. Well, for the time being we can get unique items with formulas.
- ISBETWEEN(number, first, second): to check whether “number” is between “first” and “second”. You can do this using AND() formula like AND(number>=first, number<=second).
- FINDLAST(findthis, text): that can find the last occurrence of a particular text in another text. I don’t know any good formula for this.
What are the formulas you wish Excel had ?
40 Responses to “What are the formulas you wish Excel had ?”
One that I've hoped for before and in fact wanted again this morning for generating a list of pseudo phone numbers
=CUSTOMRAND(min, max, number format)
@Gordon: that is a good one, you can of course get the same effect using randbetween() and numberformat function text() like this: =text(randbetween(min, max),number-format)
@Mattyb: that is a nice one, subtotalif() would surely help
@Gabriela: I havent used DSUM, DGET much till now. I will experiment these functions 🙂
@Leonel: Filename and sheetname are possible through cell information function cell(). Have you tried it before ?
@MikeTheActuary: hmm, isFormula() would have been very helpful.
@Derek: yeah, blank() or even a better treatment of empty values in chart plotting would have been better. Excel 2007 has a built in feature to omit empty values.
Also, have you considered writing some VBA to automate the task of handling missing values ? It looks like a good candidate for a macro.
@Mike: thank you so much. That is very nice. But, I am afraid, your function doest work. Here is an alternative I wrote that does the findlast() using VBA
Function FindLast(thisText As String, inThis As String) As Integer'returns the position of last occurance of thisText in inThis
Dim count, prevcount
count = 0
Do
prevcount = count
count = InStr(count + 1, inThis, thisText)
Loop While count <> 0
FindLast = prevcount
End Function
I might have been mistaken in using your code. Let me know if you see your code working or something needs to be changed.
@Harvey: while array formulas are really powerful and useful, not everyone are comfortable with them. I for one could never really master them. May be my mind is not tuned to understand them 😀
@Doug: I totally agree with you. While providing excellent basic functionality, excel also has the VBA for people like you and me to tweak and add additional stuff. Thanks for sharing your ideas 🙂
Btw, what are the import functions you were referring to ?
@Galen: Can you elaborate what you meant by a Cartesian lookup ? Do you mean a 2d or 3d lookup ?
subtotalif...that way we can choose any of the subtotal functions such as average, max, min, etc. - while also being able to limit the result with a filter on the data.
weightedaverage(range,weight_range). You can do this with sumproduct and sum, but it would be nice to have a dedicated function.
Functions like DSUM, DGET, etc. allowing using a criteria of a no continous range, like the titels in the row1 and the criteria in the actual row, to be able to copy the formulas as many rows as needed.
Those functions work fine, but I still want my subtotalif 🙂
It would just be easier!
FILENAME()
SHEETNAME()
SHEETOFFSET(range, offset) - Returns a range that is a specified number of sheets from a range.
LASTROW(range) - Last filled row in the specified range column.
The world is not perfect. Regards.
Excel comes with =isText() and =isNumber(). However, on more than one occasion, I've had to add a VBA function for =isFormula().
BLANK()
This would be treated as an empty cell by any Excel function. ISBLANK(cell) returns TRUE when the cell contains this function. Functions such as AVERAGE() do not count cells containing this function, as cells containing FALSE are not counted at present. Also, graphs do not count cells containing this function as having any value, as NA() is disregarded at present.
It's frustrating having to maintain two ranges of data, one containing FALSE values for statistics, and the other containing NA() values for graphing.
Chandoo: How to split names in a cell into first name, middle, last name
the names are separated by " " i mean space......
i know abt text to column......however i want to know if it can be done through formulae.......am able to extract first and last but stuck up in middle name...........
thanxx in advance
@Azmat:
you can get some ideas from
http://chandoo.org/wp/2008/09/02/get-initials-from-name-excel-formula/
Let me know if you still need some help 🙂
9.99999999999999E+307
chandoo: what does this value does in Lookup formula
Here is my version of Findlast not very sophisticated but effective
Function FindLast(cString As String, C As String, Optional s As Integer) As Integer
Dim C_Array() As String
L = Len(cString)
ReDim C_Array(L) As String
cnt = 0
For i = 1 To L
If Mid(cString, i, 1) = C Then
cnt = cnt + 1
C_Array(cnt) = i
End If
Next i
' FindLast = cnt ' count the number of Cs in cString
If s = 0 Then s = cnt
FindLast = C_Array(s) ' give the position of the s-th occurance of c in cString
End Function
I'd definately agree on the 'concatenate' limitations. Personally it's the lack of array concatenation that is definately lacking. However http://www.cpearson.com/Excel/stringconcatenation.aspx is an excellent solution to fill the gap. Keep up the good work.
Leonel Quezada - LASTROW(range) isn't really necessary, what's wrong with something along the lines of ... {=MAX(IF(A1:A10"",ROW(A1:A10),0))}
Array's really are underexploited.
Luckily Excel has an easy to use programming language, so anyone can easily write their own functions, but there are a number that are needed sufficiently ofteh that it would be worthwhile having them built in. Of the ones I've written, those that might be of sufficient general use to justify Microsoft including them in Excel include:
=interp(xrange, yrange, xval) - piecewise linear interpoloation
=ip(line1, line2) - intersection point of two lines
=area(datarange) - area of a shape defined by 2D coordinates
=areaC(diameter) - area of a circle
=RtoP(X,Y, optional X0, Y0) - convert rectangular XY coordinates to polar, optionally moving the origin. This will also find the distance and angle between two points. Perhaps for general use =Dist(X1,Y1,X2,Y2) would be better.
=Quad(A,B,C) and possibly = Cubic(A,B,C,D) and =Quartic(A,B,C,D,E) - solution of quadratic, cubic, and quartic equations.
=Simultaneous(datarange1, datarange2) - solution of simultaneous equations for people who don't know how to use Minverse() and MMult().
That's the main ones that spring to mind at the moment.
It's a mystery to me why Microsoft have chosen not to provide these very basic and useful functions, but do include things like the Bessel function, which surely anyone who needs it could write their own. I don't even know what it does.
By the way, I was sure that =Isbetween() was already a built in function, but checking I see it isn't. I was thinking of Lotus 123, which has had it for over 10 years.
Also I think text import functions are very useful, for instance for reading data from a specific position in a comma or space delimited text file, without going through the business of opening it and converting it into a spreadsheet file. There are examples of all these at Newton Excel Bach for anyone interested.
A cartesian lookup formula.
@Chandoo
Funnily enough I was poking about in the Analysis ToolPak later that day and found the RANDBETWEEN function and felt a bit daft for proposing it here! I've either never seen this or forgotten all about it.
However, the problem with ToolPak functions is that not everyone has the add-in installed so you end up getting queries on #NAME? appearing for some users. It would still be nice to have this functionality in vanilla Excel as this is the usage average users seem to expect/require.
I also though of another function that would be useful. Instead of having to use IF & ISERROR to test for valid data, something that requires the condition to be evaluated twice if the test is valid, how about a formula that just gives an option to display an alternative if the condition is false?
So instead of:
=IF(ISERROR(GETPIVOTDATA("Time",Pivot!$A$3,"Other task",B14)),0,GETPIVOTDATA("Time",Pivot!$A$3,"Other task",B14))
you simply have:
=HIDEONERROR(GETPIVOTDATA("Time",Pivot!$A$3,"Other task",B14),0)
No doubt someone will tell me there is already a far better way of doing this 😉
HIDEONERROR is a great idea!
iferror(value,valueiferror) is in excel 2007. If the formula is not an error it uses the original reference. If it is an error, it uses your error value
mattyb, I'm against the proliferation of functions that are redundant, and yours looks just like the real function IF(ISERROR(value),valueiferror,value)
Speaking of proliferation, I'd like to *reduce* the set of Excel functions, especially the mess of different lookup functions, that are nothing but duplicates of INDEX-MATCH, yet which lack the versatility of OFFSET-MATCH-MATCH.
Also, for hide on error, I'd like an enhancement of the custom number formatting protocol, instead of a function. Currently it's
positive;negative;zero;text
I'd like it to be
positive;negative;zero;text;error
@Gordon: I agree about the limitations of analysis tool pak... may be the reason why some of the common functions have been included by default in excel 2007.
Also, hideonerror() sounds like a good idea. Another simpler way to handle it could be using conditional formatting. Since most of the times you dont really want to hide an error but you want to tackle it or know why it is happening. CF can help you do that.
checkout this post to learn more on using CF to tackle errors (#5) http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/
@mattyb: thanks for pointing to iferror() which has been added in excel 2007.
@derek: on the redundant functions front, various lookup functions can often lead to confusion and extra learning time. For long time I really didnt bother to go beyond vlookup and offset.
And a great suggestion on number formats. I hope someone from MS picks it up 🙂
Late entry in "functions I wish Excel had":
QUANTILE(array,n,k)
QUANTILE(array,4,k) would act exactly like QUARTILE(array,k), and QUANTILE(array,100,k) would act exactly like PERCENTILE(array,k).
You can use PERCENTILE to do quintiles and deciles, and maybe octiles as well, but I recently had a reason to do sextiles, and there's no good function in Excel for that, so I used SMALL instead (the distribution always had exactly seven values in it, being days of the week).
I know I'm in favor of reducing redundancy, but I'd do that by retiring the QUARTILE and PERCENTILE functions, if the business users would let me.
How about using the VBA InStrRev function?
Function FindLast(thisText As String, inThis As String) As Integer
FindLast = InStrRev(inThis, thisText)
End Function
{=TABLESUM( ... )}
similar to the table function, but it sums the results across all the entries. this would mean you could have a model with 20 model points and sum the results in a single function, rather than having to write a macro that goes copy/paste special add etc.
Here's my formula equivalent (in B2:B11) of Mike's FindLast VBA:
=IF(A2=$A$1,IF(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$26,A2),"Last "&"'"&$A$1&"'",""),"")
This also works:
=IF(AND(A2=$A$1,COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$26,A2)),"Last "&"'"&$A$1&"'","")
This (in say B1) will give you the position in the list of the last occurrence of the text:
=MATCH(IF(AND(A2=$A$1,COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$11,A2)),"Last "&"'"&$A$1&"'",""),$B$2:$B$11)+1
And this (in say C1) will give you the row number:
=MATCH(IF(AND(A2=$A$2,COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$11,A2)),"Last "&"'"&$A$1&"'",""),$B$2:$B$11)+2
So, with keyword in A1, text list in A2:A11, formula in B2:B11, and the MATCH formulas in B1 and C1, you get:
no 8 9
no
yes
maybe
no
yes
maybe
no
no Last 'no'
yes
maybe
I'm sure the MATCH formula could be tidied up, but I haven't time at the moment...
However, a FindLast() function WOULD be good! On the other hand, see derek's "proliferation of functions" (Jan 19th) comments. One man's food...
I use the dimensional aggregate fuctions a lot (good for dashboards with dropdown lists)
These are very useful but clunky, the criteria [Field, value in Field column] must be a continous range, meaning that to return 1 result requires 2 (or more) rows of criteria
The fucntions are very memory hungry (like vlookup etc, they blow up the file size)
One of the biggest problems with these is that the criteria works on near match not exact match.
Very fustrating when criteria are string values with a small difference at the end of the string.
An implementation of Gabriela's suggestion, with allowance for the criteria to be the result of a formula, would go a long way to improving these functions. As would an exact match switch.
Function Findlast(Ptext As String, SText As String) As String
Preverse = StrReverse(Ptext)
Sreverse = StrReverse(SText)
Tl = InStr(1, Sreverse, Preverse)
Findlast = Len(SText) - (Tl + Len(Ptext) - 2)
End Function
The shortest function, i believe 🙂 to find the last text in a textstring
Nice site
How can i generate an array of data( eg 15 values) to produce a bell curve(normal distribution) if i have only the peak and min value ?
@Simon
A1: Min
A2: =A1+(peak-min)/14
Copy A2 down to A15
B1: =NORM.DIST(A1,$A$8,$A$8/3,TRUE)
Copy B2 down to B15
C1: =NORM.DIST(A1,$A$8,$A$*/3,FALSE)
Copy C2 down to C15
Whats going on?
A Normal Distribution will typically have a Range between Min and Max of 6 Standard Deviation.
Column A is breaking your Range into 15 slots
The middle Slot A8 is the mean
Excel has a Function NORM.DIST(Value,Mean,Stand Dev,Cumm/Not)
In your case the value is each value in Column A
The Mean is $A$8 (Max-Min)/2
The Standard deviation is a third of the Mean
Cumm/Not - Do you want the actual Distribution or Cumulative Distribution (do both)
@Harry,
I am pretty sure this is the shortest function to find the location of the last occurence of one piece of text within another...
Function Findlast(Ptext As String, SText As String) As String
Findlast = InStrRev(SText, Ptext)
End Function
Note that, of course, you don't need to create a function housing for this as the InStrRev function does finds the last occurence directly. Also note that InStrRev, like its cousin InStr, has optional arguments so the search can be made case insensitive if desired.
An expansion of "correl" to give the correlation between two text strings. The result would be equivalent to converting every number or letter of two text strings into their codes, and then running the correlation between the two arrays of codes. I often want to find mixed text/number strings like part-numbers where someone has used an extra hyphen or a space but most of the string is correct. Another way to look at this would be an "exact" function that allowed a parameter for some number of characters that were incorrect, where an optional zero would be literally exact. A way to use this as a criteria in "vlookup" and various "countif" and "sumif" functions.
... of course the trouble with the "exact" option would be how to make it compensate for an extra or missing character. Say a 12 character function with the second character being an extra space, you would not want the remaining characters to fail to match. May be too much to ask that an enhanced "exact" would re-sync after a missing/extra character in two otherwise identical strings. Hence, maybe, the advantage of the "correl" implementation.
I have never used VB with Excel, but am VERY experienced with complex built-in formulas and have some C programming experience, (though that's a bit stale). What is the best resource to quickly help me get up to speed writing other functions in VB for Excel?
OK, regarding the "FINDLAST(findthis, text)" issue i can propose this function:
Say Large Text is in A3
text to find is in A5 (small text)
then in B3 write: {=LARGE(IF(IFERROR(SEARCH($A$5;MID($A$3;ROW($A$1:$A$250);500));0)>0;ROW($A$1:$A$250);0);1)}
according to length of text in A3 fix 250s to cover the entire length, and fix 500 according to expected distance of matches within A3.
this will find the Position of the last occurrence of A5 within A3...
it ain't a nice one but i think it does the job. Enter it as an array...
cheers
I want a way to declare variables inside cells, so I don't have to repeat myself inside a formula
eg =IF(Z311=0,"",Z311)
becomes ={X:=Z311;IF(X=0,"",X)}
Simple eg, but it would be more useful with some of the long formulae that we discuss on here..
@Paul
Paul you can use Named Formulas for this