• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Opensource UDF Libraries?

S

Sherwood Botsford

New Member
I'm looking for additions to excel. Some of these I've mentioned to members of this group in private emails. Rather than learn VBA then reinvent the wheel, I'm hoping that these wheels are kicking around somewhere.

Most of these ideas have as a goal making formulas simpler. Some of these INDEX MATCH MATCH COUNTIF formulas really make my brain hurt. Excel already has a reputation for being a write only language.


Some of these exist in Google Sheets
=FILTER (Range, criteria1, [criteria2, criteria3...]) returns rows (usually) that meet the criteria.)
=UNIQUE(Range,[list of columns or rows]) With no option parameter uses all columns, otherwise only checks the ones in the list.
=SORT(Range,[list of columns or rows])

The same sort of thing that in excel takes these two columns is:

=IF(COUNTIF($D4,$P$4 & "*")>0,$D4 & " " & $E4,"")

=INDEX($Q$4:$Q$25,MATCH(0,INDEX(COUNTIF($R$3:$R3,$Q$4:$Q$25),0,0),0))
in sheets is FILTER(Q4:Q25, REGEXMATCH(P4,Q4:Q25))

To get a sorted unique list
= SORT(UNIQUE(FILTER(Q4:Q25, REGEXMATCH(P4,Q4:Q25))))

But even this exceeds most users grasp. (If a formula has more that 3 sets of parentheses make simpler syntax)

Better: =UNIQUELIST(RANGE,columns,sort options)
Range can be 2d, columns are range relative, but can be + or -, a minus column is sorted descending.

*****
Here's another idea dead simple to implement.
For all the IS* booleans provide a negated version
So NOT(ISBLANK(foo)) becomes NOTBLANK(foo) Reduces the parenthesis count, improves clarity.
*****



=TIMESTAMP(Range,options) returns a time when one of the options is true.

=LASTEDIT(Range,"Row"|"Col") returns absolute row or column number of the last edited cell in the range.

=HEAD(range,count) returns "count" from the start of the range.
=TAIL(range,count) returns "count" rows from the end of the range.

Combine head, with sort and unique, to get a real time lists of important changes.

SMALLTEXT(range,cardinal,options) LARGETEXT(range,cardinal,options) works like SMALL and LARGE but for text. Options control sorting. Default is dictionary order for your locale.

REGEXMATCH(Expression, Cell or Range) The expression is in quotes. Uses full regular expressions to match. Does NOT have the -1,0,1 option, but may have an option for 'greedy' vs 'minimal' match, as well as an option for plain or extended regular expressions. I suggest you follow pcre syntax. (www.pcre.org)

VFetch/HFetch have well known limitations, and INDEX MATCH are often used as a more robust replacement. But Match returns a relative number, which then to use you need offset

I would like to see something like
=VFETCH(what to look for, column range to search, column to return, options)
options: Cardinal: which one to return -- also accepts the words, "First" and "Last"

E.g. VFETCH("aardvarks",F2:F60,A2:A60,2)
Searches for the 2nd occurrence of aardvark in F2:F60, and returns the corresponding element from A2:A60.

Column range is potentially a 2d range.

In array context returns all of the matches starting with cardinal.

This has a simpler syntax, and since you have explicit references to columns, not relative ones in ranges, it should be more robust against inserting a new column.

Similarly HFETCH

***

Combine VFETCH and HFETCH for two way lookups.

=INTERSECT(item in column, item in row,Colrange to search, Row range to search, options)

***

RFETCH(item to look for, 2D range,options) returns the row number.
options: row or column. search by row or by column
Cardinal: is which one to return, default 1, can also be "Last"

Similarly CFETCH

Returns the absolute row/column so OFFSET isn't needed.

***

QUERY https://support.google.com/docs/answer/3093343
This is one that I think there is in Windows Excel but you have to add on the ODBC data package, unavailable on Mac.

In essence you can treat a sheet or block as a database and run what amounts to a database SQL lookup on it.
 
Hi Sherwood. Excellent list above, and I'll be working my way through it in detail.

Re your comment
Rather than learn VBA then reinvent the wheel, I'm hoping that these wheels are kicking around somewhere.
Click to expand...
...that is exactly the premise of a book I'm writing called Excel for Superheroes and Evil Geniuses. This book has a section on UDFs that no respectable Excel install should be without. So this is very timely for me.

Here’s some I think would be handy from the time I’ve spent looking at real-world problems faced by analysts in reconciling/cleaning data. THere is some crossover to some of yours. Some of these are dead simple, and are already widely covered on the net. Some are new.

1. Better Concatenate

2. Split Function.

3. ShowFormula

4. Distinct/Duplicates/Unique UDFs to return Distinct/Duplicates/Unique items from array.

5. GetPivotRange. Allows you to reference any part of a PivotTable in the same way that Tables can be dynamically referenced

6. Partial Match/Categorisation formula (Note that this is NOT Fuzzy matching. E.g. =PartialMatch(Lookup_Value, SearchTerm_Array, [Category_Array])
Performs a Partial match. Checks whether anything in the SearchTermArray exists within the LookupValue, and returns the position(s) of the match or optionally the corresponding CategoryArray value(s). Useful when trying to categorize things like expenses based on substrings within the expense description. You can achieve a similar result using one of the following Array formulas:
=INDEX(Categories[Category],MATCH(FALSE,ISERROR(SEARCH(Categories[SearchTerm],[@Transactions])),0))
=INDEX(Categories[Category],MATCH(1,IFERROR(MATCH("*"&Categories[SearchTerm]&"*",[@Transactions],0),0),0))
…however these won’t alert you in the case that multiple matches are found, and large SearchTerm lists will result in slow performance.

7. FuzzyLookup. =ClosestMatch(LookupValue,LookupArray,[MatchWarning],[MappingTable],[IgnoredSubstrings],[Fuzzyness])
[MatchWarning] would give some kind of additional information on how close a match was made, or warn user that multiple matches were made. For instance, If “Excel” was matched with both “Excel 2003”, “Excel 2007”, “Excel 2010” and “Excel 2013” then a MatchWarning of 1 would mean these were all returned as a pipe delimited list i.e. ““Excel 2003|Excel 2007|Excel 2010|Excel 2013”
[MappingTable] is range user-generated mappings of common terms such as LTD = LIMITED.
[IgnoredSubstrings] is array of things to be substituted out from both LookupValue, LookupArray, and MappingTable. E.g. {2003, 2010} would mean “Excel 2003” would be matched with “Excel 2010”.
[Fuzzyness] is some kind of parameter that determines how exact a match we need, for matches not captured by MappingTable or IgnoredSubstring matches. Would use off-the-shelf method like MS use at http://www.microsoft.com/en-nz/download/details.aspx?id=15011 or like example posted on MrExcel forum

8. CascadingVLOOKUP =MultipleVLOOKUP(LookupTerm,LookupArray1,LookupArray2...LookupArrayN,MatchType). Allows users to easily do cascading VLOOKUPS rather than having to specify nested VLOOKUPS.
 
1. Better Concatenate

I could see a syntax more like cat(" ",reference one, reference 2...)
STring literals in quotes, ranges would iterate over each element of the range. First string is what you use to separate them. Please include the convention \t for tabs, and \n for newline or carriage return/ line feed.

2. Split Function.

Again: Don't reinvent that wheel. PCRE is your friend.
3. ShowFormula

E.g. =ShowFormula(C1) so that you can audit the damn things WITHOUT having to painfully copy them with the = sign?



4. Distinct/Duplicates/Unique UDFs to return Distinct/Duplicates/Unique items from array.

What is the difference between Distinct and Unique?

5. GetPivotRange. Allows you to reference any part of a PivotTable in the same way that Tables can be dynamically referenced

Yes!

6. Partial Match/Categorisation formula (Note that this is NOT Fuzzy matching. E.g. =PartialMatch(Lookup_Value, SearchTerm_Array, [Category_Array])
Performs a Partial match. Checks whether anything in the SearchTermArray exists within the LookupValue, and returns the position(s) of the match or optionally the corresponding CategoryArray value(s). Useful when trying to categorize things like expenses based on substrings within the expense description. You can achieve a similar result using one of the following Array formulas:
=INDEX(Categories[Category],MATCH(FALSE,ISERROR(SEARCH(Categories[SearchTerm],[@Transactions])),0))
=INDEX(Categories[Category],MATCH(1,IFERROR(MATCH("*"&Categories[SearchTerm]&"*",[@Transactions],0),0),0))
…however these won’t alert you in the case that multiple matches are found, and large SearchTerm lists will result in slow performance.

See Google's regexmatch for some of this functionality.

7. FuzzyLookup. =ClosestMatch(LookupValue,LookupArray,[MatchWarning],[MappingTable],[IgnoredSubstrings],[Fuzzyness])
[MatchWarning] would give some kind of additional information on how close a match was made, or warn user that multiple matches were made. For instance, If “Excel” was matched with both “Excel 2003”, “Excel 2007”, “Excel 2010” and “Excel 2013” then a MatchWarning of 1 would mean these were all returned as a pipe delimited list i.e. ““Excel 2003|Excel 2007|Excel 2010|Excel 2013”
[MappingTable] is range user-generated mappings of common terms such as LTD = LIMITED.
[IgnoredSubstrings] is array of things to be substituted out from both LookupValue, LookupArray, and MappingTable. E.g. {2003, 2010} would mean “Excel 2003” would be matched with “Excel 2010”.
[Fuzzyness] is some kind of parameter that determines how exact a match we need, for matches not captured by MappingTable or IgnoredSubstring matches. Would use off-the-shelf method like MS use at http://www.microsoft.com/en-nz/download/details.aspx?id=15011 or like example posted on MrExcel forum

google the source code for agrep (approximate grep) agrep measures the number of substitutions it takes in the target to match the sought for item. agrep won't do all of this, but it may give you a start on the fuzzy aspects.

8. CascadingVLOOKUP =MultipleVLOOKUP(LookupTerm,LookupArray1,LookupArray2...LookupArrayN,MatchType). Allows users to easily do cascading VLOOKUPS rather than having to specify nested VLOOKUPS.

Nested VLOOKUPS... ugh...

I prefer the google sheet query approach

If my data is

Class | Genus | Species
Conifer | Pine | Ponderosa
Conifer | Pine | Scots
Conifer | Pine | Austrian
Conifer | Pine | Jack
Conifer | Spruce | Colorado
Conifer | Spruce | Black Hills
Deciduous | ...

Then my first choice is
C3=UniqueList(Class)
And my second choice is
D3=QUERY(PlantTable,select unique GENUS, where CLASS=C3)
And the third choice is
E3=QUERY(PlantTable, select unique Species where Class=C3 AND Genus=D3)
 
Hi Sherwood / Jeff ,

I like the thought that has gone into your posts , but I don't agree with the intention !

On one hand you say that the goal is to make things easier for the end user , since anything that has more than 3 parentheses in it makes the brain hurt ; fair enough , but I would like the same user to go through Jeff's explanation of how the Fuzzy match UDF will work ; my opinion is their brain might explode.

The point is that any programming language , and even Excel's Worksheet and VBA functions/keywords are one kind of programming language , starts of with the premise that a basic set of building blocks need to be provided to do a minimum set of constructs ; more complicated constructs can be built using the basic set of building blocks.

Providing more building blocks is not necessarily going to make the learning curve less steep ; it might surprise you to know that the game Go , which involves exactly 2 pieces and 2 rules , has more combinations than Chess ! ( http://en.wikipedia.org/wiki/Go_(game) )

Narayan
 
The point is that any programming language , and even Excel's Worksheet and VBA functions/keywords are one kind of programming language , starts of with the premise that a basic set of building blocks need to be provided to do a minimum set of constructs ; more complicated constructs can be built using the basic set of building blocks.

Narayan
Click to expand...

I do not agree. This argument is like saying that you should only have the C compiler, and that everyone needs to build their own version of stdlib. This is like saying that the only thing an operating system should provide is I/O, permissions, and scheduling.

With excel we are faced with reinventing the wheel over and over again. I've yet to find providers of libraries. I'm going to buy Jeff's book when it comes out JUST to get the functions. (JEFF: CD or weblink. I'm lazy and don't want to type them all in by hand. [although I might learn more if I did])

Excel is already complex due to it's non linear nature. It's FORTRAN 66 with 6 character identifiers, 66 character statements done on punch cards and GOTO. Every non-data cell is a program. Not a very big one, but there are hundreds or thousands of them. There are perhaps 1% of excel programmers that can rattle off an Index/Match/Countif like the one I gave an example of earlier. That kind of code is opaque, hard to write, harder to maintain, even harder to debug.

If I am faced with a new Perl program I can puzzle it out in a fairly straight forward manner, if it uses meaningful variable names, and is commented. It's chunked. Stuff is abstracted out into subroutines. Stuff I do over and over can be done in a general subroutine that can be called from multiple places. If it's sufficiently useful, I can create a module for it, and use it in many programs. CPAN has tens of thousands of these modules so that I don't have to reinvent the wheel, but can spend my time putting a fancy skateboard on flashy trucks that have dual race ball bearing wheels.

I mentioned PCRE in a note to Jeff earlier. PCRE is "Perl Compatlble Regular Expressions" Regexes are hard. Generally they are right up there with that Index/match/countif that I fume about. The punctuation to letter ratio is high. Regexes come in 3 flavours. Standard, Extended, and Perl. Perl introduced a bunch of extensions that made writing them a lot easier. Shortcuts: \d for [0-9] \s for any form of white space. A multi-line syntax so you could spread them out and chunk them. A provision for commenting them.

Story time: (And this reveals what an old fart I am)

Wirth in ancient times invented a language called Pascal, a strongly typed language intended for teaching programming on paper. Because it was a paper language it had miserable I/O.

University of California at San Diego (I think...) came out with USCD Pascal which actually had a compiler. It ran on 2 floppy equipped PCs. It still had miserable I/O.

Microsoft jumped on the bandwagon and produced a pascal compiler. They charged $700 for it at a time when people worked for $2.50 an hour. It was only of interest to professionals, and there was little professional interest in Pascal.

Then Borland came out with Turbo Pascal. They charged $49 bucks for it. It came on 15 floppies and half of foot of documentation. No one pirated it. You couldn't photocopy the manuals for what you paid for it. The compiler was blindingly fast, and the program came with good libraries. It also had a good IDE, an easy to use debugger/trace, and the built in editor used wordstar key bindings which everyone and his dog knew. With it, I was able to write meaningful arithmetic drill programs and tutorials on my then snazzy CP/M computer.

The Borland people realized that the standard definition of Pascal was weak, and so extended the syntax, and provided for modules that could be precompiled then linked at run time. Purests decried the version as being "non-standard" Didn't matter. Within two years there were more copies of TP out there than all other versions of Pascal put together. TP became the defacto standard for Pascal.

Excel needs a borland.
 
Both ends of premises are quite interesting. On one hand it's like telling Jeff that there are already 100 books, why write one more? Or Just because Amazon needs something on their shelves? But then there are situations which Jeff's book will cover better than the rest have done so far. Reason that prompted Jeff to go and write a book. Jeff, good luck for your book.

Taking sides really doesn't matter much. It is judicious use of both that makes you reap dividends. Sometimes the natives will win the match while sometimes you will need VBA powers.

In general, the UDFs are slower than the native. Unless there's a good reason to deploy they shouldn't be used. No matter how smart your UDF is the user will still come up with different needs.

I have used Excel since 2003 and more extensively since 2009. When I began, it was VBA that fascinated me as it allowed me to play with other applications through it including Win APIs. But then I saw many people come with strange( :D ) formulas that did the same in native Excel for which I thought I'd need VBA. So I started spending time on them and those strange looking things were a world unto themselves. So now I rely more on mix of things to handle my situations.

Keep in mind, there can never be just one way of doing things (in Excel) because there still remains a way which probably is better (which we may not be aware of). Just my two cents :)

Edit: If it is storytelling time then here's a horrible UDF story:
Once I saw a UDF which was called LVLOOKUP which used same arguments like VLOOKUP but managed to look on the LEFT side of the range. It was clear that he had never found a MATCH in INDEX. Go figure!
 
Hi Sherwood ,

I do not agree with a lot that you have said , but that is neither here nor there.

I will merely say that :

I was never and am not against libraries and UDFs ; I was only pointing out that your comparison of Excel and Google Sheets using a couple of specific functions was not reasonable. With every new version of any product , there are bound to be improvements ; Excel 2013 is bound to be better than Excel 2003 , though one may disagree on the improvements that have been made , on the improvements that could have been made but have not been done , and on what has been discarded.

Excel is a general-purpose product , and any lay user is not going to use even a fraction of the native functions ; thus even the UDFs you speak of are going to be used by a small set of users ; Laurent Longre's MOREFUNC add-in has been around for almost 10 years ; what is the fraction of Excel users who even know about it , leave alone use it ?

Narayan
 
Hi Shrivallabha ,

I would like to clarify that my intention in posting my first comment was not to dissuade Jeff from his intention of writing UDFs or books.

Narayan
 
Hi Geniuses,

I am not into full throttle programming, now started practicing VBA and used Turbo C, Borland C++ and BASIC (in the past) :) (Expertise level on BASIC (DOS MODE) was up to creating a jpg image pixels by pixels B&W by reading a scanned Jpg file, running mouse on DOS mode, Creating a menu driven software giving information about Environment or City traffic system, in short I went up to creating GUI on DOS mode) but this was way back in the year 2000. Than I started doing all this on C, and than I left programming :( (I still miss those days).

As Narayan Sir pointed out MOREFUNC, I found FAST EXCEL at below link to be truly amazing product in EXCEL. It consists of lots of function. It comes with 15 days free trial, if you guys had not used it, try it. It come with many more features apart from UDF's.

http://www.decisionmodels.com/fastexcel.htm

Regards,
 
There's some great stuff above.

@Narayan: regarding your excellent point that
The point is that any programming language , and even Excel's Worksheet and VBA functions/keywords are one kind of programming language , starts of with the premise that a basic set of building blocks need to be provided to do a minimum set of constructs ; more complicated constructs can be built using the basic set of building blocks.
Click to expand...

I largely agree. The problem is, in 99.99% of cases, noone has taught users how to program Excel (and I'm not just talking about VBA here). Noone has even told the users that they are programmers, or that formulas are subroutines. (There is a really, really, really good presentation on this at http://www.slideshare.net/Felienne/spreadsheets-are-code-online that everyone reading this should check out.)

So I believe we need to consider where end users are on the spectrum from user (someone who uses spreadsheets designed by others) to programmers (someone who puts spreadsheets together, or who makes changes to existing spreadsheets designed by others). And we need to give people towards the user end some tools that allow them to efficiently do what a competent programmer can do.

@shrivallabha: re your point
In general, the UDFs are slower than the native. Unless there's a good reason to deploy they shouldn't be used.
Click to expand...

That needs slight rewording. In general, a well-written UDFs will be slower than a well-constructed formula combination. But often times, a well-written UDF will kick the arse of a badly-constructed formula combination. (And as I've outlined above, many non-trivial spreadsheets are filled with tens of thousands of badly-constructed formula combinations, because the spreadsheet designer does not know how to program Excel efficiently with formulas or otherwise). And in specific cases, a well-written UDF will be faster than a well-constructed formula combination. (And in general, a PivotTable will kick the arse of both, if your goal is to filter and aggregate)

@Somendra: Fast Excel is great. But it's niche. Because it ain't free. I'm not suggesting that Charles should offer it for free. I'm suggesting that Microsoft should offer some of this functionality for free. In fact, a whiles back I wrote a post at http://dailydoseofexcel.com/archives/2013/11/05/excel-isnt-fully-cooked/ that asked this:
Why don’t MS supplement their great unfinished app by building and offering to users useful workarounds in one of the most agile-ready platforms there is…VBA? Why aren’t they monitoring forums and blogs for the best and brightest productivity enhancements, and buying the IP from content creators at a song, then offering it to users as add-ins that plug the gap until they get around to finishing Excel?
Click to expand...

That would address Sherwood's very astute concerns about Excel.

Lastly, here's a teaser from my book:
While the things that Excel can do are cool, Excel often makes us jump through an awful lot of hoops – and click through an awful lot of dialog boxes - in order to actually do them. At the same time, there’s lots of things we routinely do that Excel simply doesn’t provide tools handy tools for. The end result is this: for every millisecond that Excel actually does some real work, we’ve probably spent hours ‘prepping’ it to do it.

Whenever we have to do lots of manual steps in order to leverage Excel’s cool inbuilt functionality, then Excel is programming us. It’s like some kind of epic experiment in behavioural psychology; and we’re the mice. It should be the other way around.

To my mind, many of Excel’s great tools haven’t really been designed with the end use – and the end user – firmly enough in mind. They don’t store your default settings. They don’t handle the way you’re data is laid out. They don’t take any account of the context in which you call them up. Don’t get me wrong…they’re still pretty awesome. But right out of the box, they’re pretty inflexible, too.

At the same time, Excel’s most powerful tool – it’s mighty Formula engine – is so easy to use that we often misuse it. Sometimes so badly that things slow to a crawl. And then we blame Excel. But here’s the dirty truth: Excel is blazingly fast…unless we’ve programmed it very inefficiently. (That’s right, you’re a programmer, and Excel’s formulas are subroutines.) Excel only stumbles if we’ve added the formula equivalent of Kryptonite to it.

This book is going to address those shortcomings on Microsoft’s part, as well as the shortcomings on our part.
Click to expand...
 
@shrivallabha: I love this story:
Once I saw a UDF which was called LVLOOKUP which used same arguments like VLOOKUP but managed to look on the LEFT side of the range. It was clear that he had never found a MATCH in INDEX.
Click to expand...

Can I quote that in the book?

@Narayan: One further thought re this:
The point is that any programming language , and even Excel's Worksheet and VBA functions/keywords are one kind of programming language , starts of with the premise that a basic set of building blocks need to be provided to do a minimum set of constructs ; more complicated constructs can be built using the basic set of building blocks.
Click to expand...

I think of formulas like I do DNA: just by assembling 4 base-pairs together in the right order, you can build a mouse. Or a Human, with a few extra tweaks.

But even nature doesn't get it right: much of our DNA is junk DNA. So I won't hold my breath while users cobble together formula base-pairs without the requisite understanding of how Excel works under the covers and/or without knowing what some of those basepairs actually do. (The CHOOSE Function? What's that?)

Rather, I'll give them the REPRODUCTION() function, so they can blindly build something very complex, without even knowing how it works.
 
Also, some explanation about the book title: Excel for Superheroes and Evil Geniuses. The 'Superhero' bit refers to knowing how Excel works under the covers, and knowing what tools the interface puts at your fingers right out of the box. And the 'Evil Genius' bit refers to leveraging off technology (Point-and-Click macros as well as UDFs) to do things with a single formula/click that would otherwise require lots of manual clicks or megaformulas.

So UDFs is just one part of the equation, and in fact macros probably get equal - if not greater - treatment. In particular I focus on macros that pimp PivotTables to the extreme. For instance, the types of helper routines I've previously covered at:

In fact, my draft blurb sums it up nicely:
Superheroes can leap over things is a single bound while the rest of us take thousands of stairs. They run much faster and are built much tougher than mere mortals. They can bend steel with their bare hands. And they always get the girl.


Evil Geniuses build incredible gadgets to transform themselves from frustrated nerd into technological superhuman. They use their enhanced machines to directly control minions and mere mortals. They do via cunning technological prowess what superheroes do via tenacity, skill, and courage.


This book will give you the missing Excel knowhow and technological gadgetry you need to become both super hero and evil genius, where Excel is concerned. There will be no stopping you, whether you want to clean data for the purposes of good, or smash it to smithereens.


You’ll be able todo things in a single click that would take ordinary folk thousands, thanks to some killer point-and-click Macros. You’ll be able to crunch numbers faster than you thought possible, thanks to a much better understanding of Excel’s strengths. You’ll be able to write much clearer, concise formulas thanks to some killer new functions that we’ll be adding to Excel. And you’ll learn how to access free resources and incredible online support that will help you build bulletproof spreadsheets.


You won’t always get the dame. But you’ll always get the pay rise.

 
@Sherwood:
What is the difference between Distinct and Unique?
Click to expand...

Distinct means the same thing as in SQL i.e. "give me a list of all the names, but with no repeats."

Unique means "give me a list of just the unique names i.e. where there is just one instance of it"
 
Excel is a general-purpose product , and any lay user is not going to use even a fraction of the native functions ; thus even the UDFs you speak of are going to be used by a small set of users ; Laurent Longre's MOREFUNC add-in has been around for almost 10 years ; what is the fraction of Excel users who even know about it , leave alone use it ?
Click to expand...

Agreed, only a fraction of users will want to use UDFs. But the same could be said about most of Excel's native functions.

Regardless, a small fraction of 750 million people is not a small number in absolute terms. And regardless of how long MOREFUNC has been around, the success or otherwise of MOREFUNC has as much to do with marketing and packaging as it does with anything else.

I didn't know about it till now. Tried to install it on 2 PCs running 2013 32 bit. Didn't work. Apparently it works on 2010 32 bit.

I don't think MOREFUNC should be used as a benchmark of how popular UDFs are or are not.
 
Hi Jeff ,

I think ( ! ) your last sentence correctly summarizes the discussion so far :
I don't think MOREFUNC should be used as a benchmark of how popular UDFs are or are not.
Click to expand...
What it comes down to is just opinion ; yours , mine , Sherwood's , Shrivallabha's , Misra's ,...

Obviously you will go with yours. I will certainly wish you all the best.

Narayan
 
Sherwood: Am going though your suggested functions in greater detail.

I like the idea of your UNIQUELIST function. I'd probably rename it to EXTRACT or something, and give it another argument so that users can choose whether they want Unique items (items that only appear once), Distinct items (All items, listed only once) or duplicates (i.e. only the items that appear more than once).

I agree that having NOT versions of all IS functions will help real humans to write and understand formulas.

The TimeStamp idea is nice, but pretty niche. I occasionally use VBA to add a timestamp, so might as well add it as a UDF.

I wouldn’t personally use the LASTEDIT, but I guess it might be handy to a few people.

On a related note, I think Excel needs some non-volatile date/time functions. This is covered at http://fastexcel.wordpress.com/2012...functions-and-function-arguments/#comment-446 where Harlan Grove says:

“…it seems a shame the volatile functions NOW, TODAY and RAND at least don’t have optional parameters to make them nonvolatile. That is, if their argument were TRUE or missing, they’re volatile; but when their argument were False, they’d be non-volatile”

…and Charles Williams adds “I agree, except that the new calculation mode sounds complicated.How about just having static versions which are non-volatile, but with an argument to trigger a refresh? So you would get the value when entered but it would not change on a recalc unless you changed the triggering argument (or did a full calculation).”

I like the SMALLTEXT/LARGETEXT idea. Currently you have to use this:

IF(SomeText>SomeOtherText,SomeText,SomeOtherText)

…which isn’t too onerous, mind.

I’ll take a look at the REGEXMATCH(Expression, Cell or Range) idea. It would be good if there was an option so that if multiple things matched, a delimited list of them was returned.

Love the FETCH("aardvarks",F2:F60,A2:A60,2) idea. (Note I dropped the V and H…can’t see why you can’t determine that from the arguments). The INTERSECT idea also simplifies something potentially quite tricky.

Re SQL lookups – they can be very slow in Excel, so it’s probably better and more robust to do stuff using VBA if you can. For an example of a real world example, check out http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/

That said, the QUERY function is a great idea. Whether or not you used SQL to implement it, I think SQL syntax is a good idea.


Here’s a new idea:
=BLOCK(Reference)

Returns the square formed by the Reference cell, and across/down from the reference cell for as long as there is contiguous data. Much the same as what an Excel Table does i.e. a dynamic named range that is robust to any insertion/deletion of data surrounding the block. Doing this with formulas is EXCEEDINGLY hard, because the usual approach is to use COUNTA, and the user may break the integrity of the dynamic range if they add or remove any data surrounding the range that is being referenced, or if the anchor cell is moved somewhere else.

Keep your ideas/feedback coming. Much of this will end up in the book.
 
Hi Jeff,

By all means, you can publish it. I just have one condition. Don't ask me to reveal the name and place where I stumbled on it. I am not asking too much I hope:)

I wrote my experience of Excel for one particular reason. I am neither novice nor expert. But I have my opinions which may or not be acceptable to others.

Regarding applications in general, I have finally accepted one truth which almost sums up everything. All apps and software follow GIGO rule.
Garbage In >> Garbage Out
There's no way to control the bad input behavior.
 
You must log in or register to reply here.
Back
Top