New uses for old functions

[UniMord]

Not so much a competition as an appeal!

I'm seeking ways to repurpose functions in ways not necessarily intended by the nice people at Microsoft.

Think of it as a talent show for the dreary, neglected, Statistical, Math and Financial functions. Perhaps one of them could be the next SUMPRODUCT (especially now that SUMIFS has taken away a lot of SUMPRODUCT's attraction).

 

[schielrn]

One of my favorites seen recently was using vlookup and choose to look to the left:

http://www.mrexcel.com/forum/showthread.php?t=518075&highlight=vlookup+choose+left

 

[PaddyD]

"I'm seeking ways to repurpose functions in ways not necessarily intended by the nice people at Microsoft."

Perhaps the best place to look for a concentrated source is in the old EEE newsletters, which JWalk archives:

http://spreadsheetpage.com/index.php/eees

...which had a section more-or-less devoted to 'hey - bet you didn't know you could do this with that". It includes what remains my favourite 'not how the manufacturer intended', which is the use of subtotal() to help return an array of the visible cells in a filtered column (see issue 20).