Leading the way in Microsoft Office Development

Reversing the Digits - David Hager

Using a combination of functions it is possible to reverse the digits in a number.

This formula must be entered as an array formula using (Ctrl + Shift + Enter).

Enter the number you want to reverse into cell "A1" and then enter the following array formula into cell "B1".

Reversing the Words

If you want to change the names in a column from "Lastname Firstname" to "Firstname Lastname" you can use the following formula; RIGHT("A1", LEN("A1") - SEARCH(" ", "A1") -1 & " " & LEFT("A1", SEARCH(" ", "A1") -1)

Summing cells that do not contain an error

The SUM function does not return a sum if any of the cells contain errors. You can use "=SUMIF("A2:A10",<>"#VALUE!")".

Summing values meeting two conditions

To sum values in a range that may contain errors you can use the following "=SUM(SUMIF("A2:A20","<0"),SUMIF("A2:A10",">0"))".

Reducing values by 10%

To decrease all the values in a range by 10 percent, type 0.9 into a cell, copy the cell and select the range. (Edit > Paste Special) Select Multiply.

Returning every fourth value in a list

To obtain every fourth value in a column and put them in a separate column, you can use the following formula "=OFFSET(A1, ROW()*4-1, 0).

Averaging numbers ignoring duplicates

If you want to average three numbers, but if two are the same only average two of the numbers you could use the following where Rge is a three cell range. "=AVERAGE(IF(SUM(COUNTIF(Rge,Rge)) = 3,Rge,IF(COUNTIF(Rge,Rge)>1.Rge,""))).

Averaging numbers ignoring duplicates

This formula counts the number of cells in the discontinuous range that contains a value greater than 20 "=INDEX(FREQUENCY((A1,A3,A5),20),2)".

Returning an array of worksheet names

This formula returns an array of worksheet names in the active workbook "=RIGHT(GET>WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("1",GETWORKBOOK(1)))).

Position of the active worksheet in the workbook

This formula returns the position of the active sheet in the workbook as an integer "=GET.DOCUMENT(87)", "=GET.DOCUMENT(76)".

Adding up values on different worksheets

This formula returns the sum for the cell in column "A" for each worksheet from Sheet1 to the worksheet where the function resides "=EVALUATE("SUM(Sheet1:"&ShtName&"!A"&Row()&")")+NOW()*0 where ="INDEX(wsNames,ShtPos)" where wsNames is the array of worksheet names in the active workbook and ShtPos = "GET.DOCUMENT(87).

Returning the unique values in a range

This formula returns the number of unique items in a worksheet range "=SUM(1/COUNTIF(Rge,Rge)), although this will only work if the range does not contain blank cells. If it does you can use: "=SUM(COUNTIF(RgemRge)/IF(NOT(COUNTIF(rge,Rge)),1,COUNTIF(Rge,Rge))^2).

Adding comma-delimited numbers

If you had comma-delimited values in cell A1 then the following function will return the sum of these values. EVALUATE(SUBSTITUTE(A1,",","+")). It basically replaces the commas with plus signs and then evaluates.

Things to Remember

If you receive the error "Undefined Function" or "Function Not Available" you may have lost one of your library references or ones of your addins.

You can always write your own worksheet functions using VBA when you need a function with more flexibility.

Remember to include a speech mark character in a function you must use double speech marks.