Time for blowing my own trumpet and patting my own back over my pointy hair. I feel very proud to announce that our little community at Pointy Haired Dilbert now has its five thousandth member.

Take a minute and pat yourself on the back. This is an achievement because of you. Go ahead, I am waiting.

Ok, enough patting. Time for some gifts and fun.

We have 2 contests to celebrate the occasion. This is the first one. I will announce the second contest tomorrow.

### Twitter formula contest.. What?

It is as simple as leaving a twitter. All you have to do is write a formula less than 140 characters and tweet it. It could be a complex array formula to solve the world hunger, or just a regular vlookup with wild card search.

**Just follow these guidelines:**

- The formula should be self explanatory
- Or the formula should be short so that you can squeeze the explanation in the tweet itself
**Either include @r1c1 in the tweet or post the permalink to your tweet in the comments**. Otherwise I cant locate your tweet and hence you wont get the prizes- Dont post formulas that are way simple like sum(1,2,3)
- Finally, if you don’t have a twitter account, you can post your formula in the comments. Character count still remains.
- You can post as many tweets as you want.
- Winners will be selected randomly. So post anything as long as it is good.
**The contest is closes on 15th August**midnight (at where I sleep)

### What are the prizes?

There are two prizes.

**Excel Dashboard Bundle sponsored by Bonavista Systems. **

Andreas, who owns the company has been kind enough to sponsor this prize. The dashboard bundle includes two kickass products from BonaVista systems – Excel Microcharts and Chart Tamer [My review of chart tamer here].

This prize is worth $200.

**Excel 2007 Formulas by John Walkenbach**

J Walk, who probably authored a zillion excel books provides a complete reference of Excel 2007 formulas in this wonderful book. The book is a must have for both excel beginners and more advanced users. And it is just a tweet away to become yours.

This prize is worth $28.

### Any doubts?

Leave a comment or tweet me @r1c1.

### No doubts?

Good, what are you waiting for then? Get tweeting.

## 17 Responses to “Twitter Formula Contest – We are 5000 strong now”

Here’s my little contribution (previously posted 😉

Named Ranges (should be dynamic, but….)

Ship $A$2:$A$8

Captain $B$2:$B$8

flights $C$2:$C$8

in F:F

Summary_ship $F$2:$I$2

this 3:3

Summary_Captain $E$3:$E$6

data is in range A1:C8, and summary is in E1:I6.

=SUMPRODUCT((Ship=in Summary_ship)*(Captain=this Summary_Captain)*(flights))

rgds,

Martín

=NORMDIST(-1*ABS((Z27-AE27)/AG27),0,1,TRUE). Calculate p-value for t-statistic based on means in Z27 and AE27 and the std err of mean in AG27.

=LEFT(A1,FIND(" ",A1)-1)

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Split first names and last names.

This formula determines the Active (="T") status or otherwise of Employees in an Excel spreadsheet:

=IF(AC2="","X",IF(AND(AC2=500000,AD2=""),"T",IF(AND(AC2500000,AD2""),"F","Pls Enter Leaving Reason!!")))

=FREQUENCY(DY5:DY118,EU4:EU14) for creating frequency distributions. I can't believe I went so long before discovering that there's an easy built-in array function that does this. Constructing the distribution by hand was always a pain.

To return the full Path+Filename of your (saved) workbook (and dropping the [] characters) to get, for example, C:\Data\ExcelFiles\MyWorkbook.xls:

=SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))),"[",""),"]","")

For me, this does not work in Excel 2010. What exactly is in $a$1?

@Gary

RE: $A$1

Have a read of http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/

To return the name fo the current worksheet, e.g. "Sheet1":

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))

To return the name of the workbook only, e.g. MyWorkbook.xls:

=MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1))+1,FIND("]",CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1))-1)

[...] and to celebrate that we have launched a series of contests. Yesterday we have announced the twitter formula contest. Today it is time to show your [...]

Probably a easier way of doing this , extracting the month from a date as text. A1 is date

=TEXT(DATE(0,MONTH(a1),1),"mmmm")

This formula looks up data from another sheet considering three parameters keeping into account the column A and column B with sub-components (both on another sheet) and matching them up with the heading on both sheets.

=OFFSET('Data Sheet'!$C$1,MATCH(D$2,'Data Sheet'!$A$2:$A$140,0)+MATCH($B5,'Data Sheet'!$B$2:$B$20,0)-1,MATCH(D$3,'Data Sheet'!$C$1:$J$1,0)-1)

This formula extracts data from a dynamic data range and returns a zero value if there is an #N/A error.

=IF(ISNA(HLOOKUP($A14,Data!$AB$2:$AW$9,MATCH("P",Data!$AB$2:$AB$2,0),0)),0,HLOOKUP($A14,Data!$AB$2:$AW$9,MATCH("P",Data!$AB$2:$AB$2,0),0))

[...] David on PHD comments =NORMDIST(-1*ABS((Z27-AE27)/AG27),0,1,TRUE). Calculate p-value for t-statistic based on means in [...]

A shorter formula than Alan's (with one less function call as well) for extracting the month name for the date in A1...

=TEXT(28*MONTH(A1),"mmmm")

Forget the nonsense I just posted. Alan, if A1 contains a date, then all you need is this...

=TEXT(A1,"mmmm")

What I was thinking (and screwed up) is that if A1 contained the month number, then you could get the month name from it using this...

=TEXT(28*A1,"mmmm")