UNIQUE Random Strings of Letters

This is about creating strings of up to 26 letters with no repeats of any letter, using an array formula. Here's the setup:

In Reverse: Going Backwards with ARRAY formulas

Do you need to type or use expressions or numbers in reverse?

Trivial or not, in fact we need sometimes to display or read some numbers or text / expressions in reverse. There are several options available in Excel to do just that. You can do it in a macro way or using some known Excel formulas. E.g. you may use the following ARRAY formula for reversing digits in a number:

=SUM(VALUE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))*10^(ROW(INDIRECT("1:"&LEN(B2)))-1)) 

However, at a closer look, the formula works fine with digits only. If you enter 12481632 into cell B2, it returns 23618421, but after entering 2.35 it returns error #VALUE! . In addition, it doesn't return trailing zeroes.

So, after some experimenting, I've created my own dynamic ARRAY formula. Here is its exemplary application:

The formula reverses whatever string, number or a mix of letters and digits you'd like to reverse. Just remember: this is an ARRAY formula, so - after typing it in - you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER on your keyboard.

 

Reference the last cell in a column

If you need to reference the last cell value in a given column, use the following array formula:

 =INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))

It returns the value held in the last filled cell in column G.

If you need to find out also what the row number of the cell keeping the value is, then this array formula can be used: 

=CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>""))))

You may want to get both the value and location / row number of the last cell. In such a case you can combine the above formulas into one, like in this example:

=INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))&"  ==> "&"Row "&CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))) 

Sometimes the formula can return an error value. It may happen, if any of the cells in the column displays an error. To make you aware of such an error, you can include error checking part, e.g.:

=IFERROR(INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))&"  ==> "&"Row "&CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))),"Error in the column!")

Replace G in the formulas with your actual column of interest, as needed, and keep in mind that these are all ARRAY formulas, so you need to hold simultaneously CTRL+SHIFT keys and press the ENTER key to create them.

 

Date to date

Date functions in Excel can answer a whole plethora of questions we are dealing with in everyday life. Some of them are quite straightforward, and some not quite, but all have practical applications. 

Couple of notes, before you try to use any of the formulas:

• MONDAY is designated here as the first day of the week
  
• Cells with the formulas shown here must be in General format, not Date format.
• Cells A2:C2 are presented in Custom format: "dddd dd/mm/yyyy"

Useful tip: you can enter Current date using this shortcut: CTRL+; (semicolon)

Formulas presented here, when supported with a simple setup in a worksheet, like this one:

will help you to find many of your questions answered. Here are some examples:

➤ How many days left in this calendar year:    =DATE(YEAR(A2),12,31)-A2

➤ Number of specific week days between two dates, the second date inclusive. Use the following formulas for the number of:

• Mondays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=1)*1)
• Tuesdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=2)*1)
• Wednesdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=3)*1)
• Thursdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=4)*1)
• Fridays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=5)*1) 
• Saturdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=6)*1) 
• Sundays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=7)*1) 

➤ Number of weekdays (Monday-Friday) between two dates, the second date inclusive:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={1,2,3,4,5})*1)

➤ Number of weekend days (Saturday-Sunday) between two dates, the second date inclusive: 

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={6,7})*1)

➤ Number of business days, with holidays excluded:

=NETWORKDAYS.INTL(A2,B2,1,Freedays), where 'Freedays' represents a range of holiday dates set up and named so in your worksheet (if needed).

➤ Number of days survived since somebody's birthday (entered in cell C2):

 =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C2&":"&A2)),2)={1,2,3,4,5,6,7})*1)-1

➤ Number of days left till the end of this century (including 31 Dec, 2100):

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(TODAY()&":"&DATE(2100,12,31))),2)={1,2,3,4,5,6,7})*1)