Got any Useless Formulas?

Does anyone else come up with unlikely scenarios and problems to solve using formulas as a way to educate yourself, but realistically probably serve no purpose?

Post your "useless formulas" below, maybe someone will find them useful or at least educational!

Mine...for today: I know they are not particularly efficient, I'm still playing with them

Enter each into an area of 1 Column, 26 Rows (CSE)


This one will list out which capital letters, in Alphabetical order, are missing from a cell (F4) of any length of any characters

=IFERROR(CHAR(SMALL(IFERROR((0/(N(IFERROR(1/(FREQUENCY(IFERROR(SMALL(IFERROR(VLOOKUP(CODE(MID(F4,ROW(INDIRECT("1:"&LEN(F4))),1)),CHOOSE(COLUMN(A:B),ROW(65:90),ROW(97:122)),1,0),""),ROW(INDIRECT("1:"&LEN(F4)))),""),ROW(65:90))>0)*ROW(65:90),"")<>ROW(65:90))*ROW(65:90))+1)*ROW(65:90),""),ROW(1:26))),"")


This one does the same for Lowercase letters in the same cell (F4)

=IFERROR(CHAR(SMALL(IFERROR((0/(N(IFERROR(1/(FREQUENCY(IFERROR(SMALL(IFERROR(VLOOKUP(CODE(MID(F4,ROW(INDIRECT("1:"&LEN(F4))),1)),ROW(97:122),1,0),""),ROW(INDIRECT("1:"&LEN(F4)))),""),ROW(97:122))>0)*ROW(97:122),"")<>ROW(97:122))*ROW(97:122))+1)*ROW(97:122),""),ROW(1:26))),"")


This will list in alphabetical order which Capital letters are used in a cell (F4)

=IFERROR(CHAR(SMALL(IFERROR(1/(FREQUENCY(IFERROR(SMALL(IFERROR(VLOOKUP(CODE(MID(F4,ROW(INDIRECT("1:"&LEN(F4))),1)),ROW(65:90),1,0),""),ROW(INDIRECT("1:"&LEN(F4)))),""),ROW(65:90))>0)*ROW(65:90),""),ROW(1:26))),"")


This will do the same for Lowercase letters in (F4)

=IFERROR(CHAR(SMALL(IFERROR(1/(FREQUENCY(IFERROR(SMALL(IFERROR(VLOOKUP(CODE(MID(F4,ROW(INDIRECT("1:"&LEN(F4))),1)),ROW(97:122),1,0),""),ROW(INDIRECT("1:"&LEN(F4)))),""),ROW(97:122))>0)*ROW(97:122),""),ROW(1:26))),"")

This formula was the solution I came up with to a couple of questions posted in forums over the years.

You have a matrix of cells that contain some data. You want to know which cell contains the lookup value as a location number within the matrix. There are no duplicates in the matrix.

Data Range

	A	B	C
1	82	32	53
2	89	39	96
3	52	72	16
4	15	86	85
5	34	8	14
6	------	------	------
7	Lookup	86	11

The lookup value 86 is in cell number 11 of the matrix. The lookup direction is left to right, top to bottom.

=MATCH(1,FREQUENCY(1,--(A1:C5=B7)),0)

We can even do it from top to bottom, left to right:

Data Range

	A	B	C
1	82	96	86
2	32	52	85
3	53	72	34
4	89	16	8
5	39	15	14
6	------	------	------
7	Lookup	86	11

Array entered**:

=MATCH(1,FREQUENCY(1,--(TRANSPOSE(A1:C5=B7))),0)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

the sequence number seems to me to be hardly informative,

I'd prefer:

Formula:

Please Login or Register  to view this content.

Originally Posted by snb

the sequence number seems to me to be harly informative

That's what the OP asked for.

If you just want the cell address...

Array entered**:

=ADDRESS(MAX(IF(A1:C5=F1,ROW(A1:C5))),MAX(IF(A1:C5=F1,COLUMN(A1:C5))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Took me a second to realize what was going on in those tables Tony, very neat, x = in the nth cell of an array if read like a book left to right one row at a time. that is indeed fairly useless in any application I can think of ^-^.

Here's another.

Array entered:
=CHAR(ROUNDDOWN(AVERAGE(SUM(CODE(MID(E6,ROW(INDIRECT("1:"&LEN(E6))),1)))/LEN(E6)),0))

This will give you the average character of a string entered in E6. so AC will return B, but A C will return 6, because the space changes the average.

Originally Posted by Tony Valko

That's what the OP asked for.

Which OP ?

Originally Posted by snb

Which OP ?

The one that asked the question.

No arrayformulas needed:

Formula:

Please Login or Register  to view this content.

No arrayformulas needed:

Formula:

Please Login or Register  to view this content.

Methinks the intention for this thread is being misunderstood

Originally Posted by Speshul

Methinks the intention for this thread is being misunderstood

Ya think?

Ok, so I've been toying with formulas again with no real goal as to what I'm working on, and came up with something that gives results I don't quite understand:

The formula is CSE:
=IFERROR(CHAR(LARGE(--(MID(A1,COLUMN(INDIRECT("1:"&LEN(A1))),1)=CHAR(ROW(1:255)))*ROW(1:255),ROW(INDIRECT("1:"&LEN(A1))))),"")

Enter it into a column of a bunch of rows, I'm using 20.

Type some random string into A1, letters, numbers, whatever. It lists them all out in order by their code, which is similar to something I did in another formula above.

The thing is, it doesn't seem to consistently line up with what you actually type into the cell? Why?

Note: This one lags my excel a lot, fair warning.


cse63.PNG

I didn't want to start a new help thread because this formula serves no purpose and I don't care if it works or not, I'm more curious about what it's doing to give me the result it is giving me

From what I can tell, it is giving me a number of capital letters equal to the number of spaces in my string in A1. But still, W T S S? or CHAR's 87 84 83 83, ?!

Well - it's not formulae, but a rather useless little system to emulate the ring of an old-fashioned phone

Please Login or Register  to view this content.

I find this extremely useful, but it's also useless.

Count down how many seconds, in decimal, you have until you go home
Quittin time goes in red
Todays date in the blue



=HOUR(DATE(2014,8,28)+TIME(17,30,0)-NOW())*60*60+MINUTE(DATE(2014,8,28)+TIME(17,30,0)-NOW())+SECOND(DATE(2014,8,28)+TIME(17,30,0)-NOW())

Edit: Actually no, it doesn't even work. Now I have something to do though! Will edit in a working one...

If you use this formula

=MOD("17:30"-NOW(),1)

and custom format cell as [s] then you get the number of seconds until the next instance of 17:30

I'm not sure if that qualifies as "useless" or not......