Excel Features you wish Existed

Just wondered what your wish list was!

Mine is

1) Speed up VBA and add better security to projects/Files
2) A way of defining parts of a calculation for cases like

= if(iserror(Formula), "",Formula)

itwould be nice to do
=Define (MyFormula,A1+A2+A3) if(iserror(Myformula,MyFormula))

besides speeding it up as it would only have to calculate Myformula once and use the result! Especially if myformula is long and repeated 4 or 5 times in the main formula!

Originally Posted by squiggler47

Just wondered what your wish list was!

Mine is

1) Speed up VBA and add better security to projects/Files
2) A way of defining parts of a calculation for cases like

= if(iserror(Formula), "",Formula)

itwould be nice to do
=Define (MyFormula,A1+A2+A3) if(iserror(Myformula,MyFormula))

besides speeding it up as it would only have to calculate Myformula once and use the result! Especially if myformula is long and repeated 4 or 5 times in the main formula!

You can define formulas.. Insert|Name|Define (it's not only for naming ranges).

Chip's site shows an example of using defined formula

http://www.cpearson.com/excel/nested.htm

I know you can create a named formula, but it would be nice to do it within the formula itself!

That way the named formula only exists during the calculation, and if I delete the formula I'm not left with defined names that are not referenced anywhere!

1. Certainly a more robust protection would be nice. Not sure about the speed issue. I guess a lot of that is down to coding and task being performed.

2. in xl2007 you would use IFERROR, no?

=IFERROR(myformula,"")

I dont know if you ever timed a UDF

Function Test()

END Function


the overhead of the call takes longer than the actual function call, this is the part that needs speeding up a little!

and the IF() was just a simple example, i just did a formula for a pricelist that was 1800 characters long, 2 parts of the formula where repeated about 4 times each, i was tempted to use a UDF but I wanted to avoid VBA and the speed hit would have been about 400%!

It did say depends what you are doing...

Have you been to Charles Williams' site
http://www.decisionmodels.com/calcsecretsj.htm

built in ability to add text /numbers before or after existing data would be handy
a simplified regex function where you could just put in a string and you could select bits and it would do it for you.
all of the morefunc addin functions as standard!
ability to include cell formating for use in functions
return multiple result from vlookup instead of using helper columns/arrays

How about a single function for index-Match!

I wonder how many Array/Sumproducts are used on a regular enough basis and could benefit from a dedicated function to do a similar thing!

I agree re: INDEX/MATCH ie

=RETRIEVE(criteria,criteria range,result range)

where the ordering of the ranges is irrelevant and binary search is not employed ... the only 1 function alternative to INDEX/MATCH that I can think of is probably using LOOKUP in the form of

=LOOKUP(2,1/(criteria range = criteria),result range)

but this is a slow approach and is really no shorter character wise than typing the 2 function INDEX/MATCH call:

=INDEX(result range,MATCH(criteria,criteria range,0))

and obviously LOOKUP returns the last valid reference whereas INDEX/MATCH will return the former as per default VLOOKUP / INDEX&MATCH approaches.


Re: Sumproducts etc... the introduction of SUMIFS / COUNTIFS in XL2007 will / should reduce the amount of Sumproducts needing to be used but obviously unlike SUMPRODUCT you can't manipulate the datasets being used... it would be nice to be able to manipulate the data within a standard COUNTIF(S)/SUMIF(S) etc...


IMO IFERROR is the best introduction in XL2007 and long overdue.

Re: Sumproducts etc... the introduction of SUMIFS / COUNTIFS in XL2007 will / should reduce the amount of Sumproducts needing to be used but obviously unlike SUMPRODUCT you can't manipulate the datasets being used... it would be nice to be able to manipulate the data within a standard COUNTIF(S)/SUMIF(S) etc...


IMO IFERROR is the best introduction in XL2007 and long overdue.

yes IFERROR is great apart from the fact I can only use it in my own sheets, as everyone else i do sheets for now uses XP/2002!


perhaps a function like :

Array(Data,Selection Criteria1,[more criteria])


or even A((Data,Selection Criteria1,[more criteria])

that returned a subset of an array or data to use in a function! which also allows you to use data*5 like sumproduct!

so you could

sum(A(A1:A1000,"<" & Today()))

min(A(A1:A1000,"<" & date(2009,1,5),">" & date(2009,5,5)))

average(A(A1:A1000,NOT(ISBLANK(A1:A1000)))



Perhaps a variation of IFERROR

IFBLANK(test,"")

and default for them to

IFerror(TEST) - to default as IFERROR(TEST,"")

I'd say the most requested topic on the boards, maybe aside from timesheets, is a formula to transfer data by criteria to a new sheet without blank rows.

Just playing with MiniTab 15 and they have WORD function with the format =WORD(text,# word, delimiter (default = Space))
That would be a nice Excel function, maybe extending it with the additional argument of how many words to extract. So if A1 contains, "This is a nice example" In B1

Please Login or Register  to view this content.

Beats all those LEFTS, RIGHTS, SUBSTITUTES, etc

Chemist, ask and ye shall receive...

Please Login or Register  to view this content.

Example Usage:

=WORD(A1,5,2," ")

A1 = str = original phrase/sentence
5 = startNum = starting word # based on delimeter
2 = wordCount = number of words to return based on delimiter
" " = delim = character of delimiter

Updated (7/15/09): Added Dim for variable i; Changed Dim for "updtStr" to "updStr" as it's used in the code; Made last two arguments optional and set defaults if they were not included in the function. (I really need to start using Option Explicit!!!)

Thanks Paul ! ! ! !
You're better than Microsoft.