New uses for old functions

UniMord

UniMord

Active Member
Joined
May 6, 2002
Messages
311
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
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>
 
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 ;-)
 
Last edited:
Your formula works fine - the intersection approach's advantage is that it keeps things interesting.
 
Last edited:
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. :)
 
"I needed to determine if all the values in a row are the same..."

=stdev(range)=0
 
How does that improve on the formula using VAR(), mentioned at the beginning of the thread?
 
Or =max(range)=min(range)
 
That actually has the advantage of not erroring when there are only 1 or 0 entries.
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,761
Messages
6,174,342
Members
452,556
Latest member
Chrisolowolafe

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top