# New uses for old functions



## UniMord (Sep 16, 2008)

I'm interested in finding new ways to use functions in ways possibly not intended. Why? Because it gets boring using the same functions over and over again - even if they work just fine. There must be some gems hiding among the specialized functions that most of us, myself included, never use, for example, the majority of the statistical and engineering functions.

I'm a total dunce when it comes to statistics, and for that matter, my work never requires it, so I was pleased to be able to squeeze a use out of VAR(). I needed to determine if all the values in a row are the same. I don't really care what the value is, so long as they're all equal or empty.

Just to demonstrate:

*=IF(COUNT(A1:A10), IFERROR(IF(VAR(A1:A10), "Not all the same.", "All the same."), "Single entry."), "No entries.")*

In my particular situation, I didn't have to test for single or no entries, so the formula was just a simple: *IF(VAR(A1:A10), ...*

Anyway, if anyone cares to take this up and contribute something clever or wacky I'd appreciate it.


----------



## Oaktree (Sep 17, 2008)

The one that immediately comes to mind is the Easter date calculation.

Where A1 has the year, you can use:

=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (from j-walk.com)

or, the formulas/UDF posted on Chip Pearson's site here:

http://cpearson.com/excel/holidays.htm#Easter


----------



## UniMord (Sep 17, 2008)

That _is_ an odd use for DOLLAR(). Thanks!

I have among my favorites, a formula I picked up at XL-Logic.com, it is credited to James Cullingham. 

Given 2 sets of dates, determine how many days are common between them. He uses the intersection operator to pull this off. Certainly a creative example of productively misusing Excel's features!


<SCRIPT language=JavaScript src="<a href=" popup.js" colo puremis sun target="_blank" href="http://www.interq.or.jp" www.interq.or.jp http:></SCRIPT><CENTER><TABLE" target="_blank">http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE< a>cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" colSpan="5" bgColor="#0c266b"><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left>Microsoft Excel - intersection.xls</TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 12.0 : OS = Windows XP </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" colSpan="5" bgColor="#d4d0c8"><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</TD><TD vAlign=center align=right><FORM name=formCb059465><INPUT onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' type=button value="Copy Formula" name=btCb290334></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" colSpan="5" bgColor="white"><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION value="=NOT(OR(MIN(A2:B2)>MAX(A3:B3), MAX(A2:B2)<MIN(A3:B3)))*(((OR(AND((MIN(A2:B2)<=MIN(A3:B3)),MAX(A2:B2)>=MAX(A3:B3)),AND((MIN(A2:B2)>=MIN(A3:B3)),MAX(A2:B2)<=MAX(A3:B3)))*(MIN(B2-A2,B3-A3)+1)*NOT(OR(MIN(A2:B2)>MAX(A3:B3), MAX(A2:B2)<MIN(A3:B3))))=0)*(MIN(MAX(A2:B2)-MIN(A3:B3),MAX(A3:B3)-MIN(A2:B2))+1)+OR(AND((MIN(A2:B2)<=MIN(A3:B3)),MAX(A2:B2)>=MAX(A3:B3)),AND((MIN(A2:B2)>=MIN(A3:B3)),MAX(A2:B2)<=MAX(A3:B3)))*(MIN(B2-A2,B3-A3)+1))" selected>D2<OPTION value='=ROWS(INDIRECT("A"&A2&":A"&B2) INDIRECT("A"&A3&":A"&B3))'>D3</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>*=*</TD><TD align=left bgColor=white><INPUT size=80 value="=NOT(OR(MIN(A2:B2)>MAX(A3:B3), MAX(A2:B2)<MIN(A3:B3)))*(((OR(AND((MIN(A2:B2)<=MIN(A3:B3)),MAX(A2:B2)>=MAX(A3:B3)),AND((MIN(A2:B2)>=MIN(A3:B3)),MAX(A2:B2)<=MAX(A3:B3)))*(MIN(B2-A2,B3-A3)+1)*NOT(OR(MIN(A2:B2)>MAX(A3:B3), MAX(A2:B2)<MIN(A3:B3))))=0)*(MIN(MAX(A2:B2)-MIN(A3:B3),MAX(A3:B3)-MIN(A2:B2))+1)+OR(AND((MIN(A2:B2)<=MIN(A3:B3)),MAX(A2:B2)>=MAX(A3:B3)),AND((MIN(A2:B2)>=MIN(A3:B3)),MAX(A2:B2)<=MAX(A3:B3)))*(MIN(B2-A2,B3-A3)+1))" name=txbFb965317></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle">

</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">To</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">From</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Days in

Common</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">9/1/1999</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">9/30/1999</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">Hideous Long Formula :</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">15</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">8/15/1999</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">9/15/1999</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">Range Intersection :</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">15</TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan="5"><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left>ComDays</TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
</CENTER>


----------



## Oaktree (Sep 17, 2008)

Yeah, that is an interesting approach.  Though, I think you can just go with =MAX(0,MIN(B2:B3)-MAX(A2:A3)+1) for that one


----------



## UniMord (Sep 17, 2008)

Your formula works fine - the intersection approach's advantage is that it keeps things interesting.


----------



## UniMord (Feb 14, 2011)

A minor innovation that occurred to me...

I see that SLN() is nothing more than: *(a - b) / c*.

That being the case, there are plenty of opportunities to misuse this financial function. I used it to determine how many complete weeks in a specific period.

Where *A1 = Later Date* and *B1 = Earlier Date*,

*=INT(SLN(A1, B1, 7))* will do the trick.

I admit, this little gimmick is less than scintillating, but I'm hoping to prime the pump and induce more talented members to toss their hats into the ring.


----------



## PaddyD (Feb 14, 2011)

"I needed to determine if all the values in a row are the same..."

=stdev(range)=0


----------



## UniMord (Feb 14, 2011)

How does that improve on the formula using VAR(), mentioned at the beginning of the thread?


----------



## shg (Feb 14, 2011)

Or =max(range)=min(range)


----------



## UniMord (Feb 14, 2011)

That actually has the advantage of not erroring when there are only 1 or 0 entries.


----------



## Peter_SSs (Feb 14, 2011)

"I needed to determine if all the values ... are the same... I don't really care what the value is, so long as they're all equal or empty"

None of the suggestions worked correctly for me. 
(A1:A9 = "a", A10 = "b")  

So maybe not what the thread is about, but for this job I would propose another possibility
=COUNTA(A1:A10)=COUNTIF(A1:A10,LOOKUP(2,1/(1-ISBLANK(A1:A10)),A1:A10))

This seems to work for numeric, text and also error values.

To try to add something that is relevant to the topic, perhaps this for checking if a value (given the above I better specify numerical  ) lies within a given range (including endpoints) or not

=MEDIAN(A1,100,200) = A1


----------



## DonkeyOte (Feb 15, 2011)

Similar to Peter's but not as robust (underlying errors)


```
=SUMPRODUCT((SUBSTITUTE(A1:A10,A1,"",1)="")+0)=ROWS(A1:A10)
```



			
				UniMord said:
			
		

> I'm interested in finding new ways to use functions in ways possibly not intended.



SUMPRODUCT is the classic case - compare Help File examples to 95% of on line examples.


----------



## Peter_SSs (Feb 15, 2011)

DonkeyOte said:


> Similar to Peter's but not as robust (underlying errors)
> 
> 
> ```
> ...


It also gives the wrong answer (False) if A1 is blank and any non-blank cells are all equal.


----------



## DonkeyOte (Feb 15, 2011)

Fair dinkum...


```
=SUMPRODUCT((SUBSTITUTE(A1:A10,LOOKUP(2,1/(A1:A10<>""),A1:A10),"",1)="")+0)=ROWS(A1:A10)
```

but obviously this is even more ridiculous than before and still doesn't account for errors


```
=SUM((FREQUENCY(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0)),ROW(A1:A10)-ROW(A1))>0)+0)<2
```

but again would not account for errors and is arguably being used as intended 

note to self: don't post within 15 minutes of waking up...


----------



## Peter_SSs (Feb 15, 2011)

DonkeyOte said:


> note to self: don't post within 15 minutes of waking up...


It's over half an hour so you're fair game again now! 

The first one also errors when the range is empty (refer post #10).

The second seems to error if A1 is empty no matter what is in the rest of the range.


----------



## DonkeyOte (Feb 15, 2011)

I failed to point out that the second is an Array though still not as robust as your earlier suggestion.


----------



## UniMord (Feb 15, 2011)

> =COUNTA(A1:A10)=COUNTIF(A1:A10,LOOKUP(2,1/(1-ISBLANK(A1:A10)),A1:A10))


 
Peter,

That is one cool and robust formula! I'm popping that one straight into my bag of tricks!

Is there a special reason why you chose 2 as LOOKUP's first argument, rather than 1?


----------



## Jon von der Heyden (Feb 15, 2011)

Ignore...


----------



## DonkeyOte (Feb 15, 2011)

1 would suffice but 2 is conventional - more "transparent"


----------



## PaddyD (Feb 15, 2011)

"How does that improve on the formula using VAR(), ..."

Apologies.  I simply read the thread title, and must somehow have missed that you intended this to be a competition.


----------



## UniMord (Feb 15, 2011)

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 (Feb 15, 2011)

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 (Feb 15, 2011)

"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).


----------

