Tags: IFERROR

Compute MODE of all numbers split across multiple worksheets

{10 Comments}

Assume numbers are typed in range A1:A2 of multiple worksheets in a workbook.  The task is the compute the MODE of these numbers.  Mode is defined as the value which appears most frequently in a range of cells.  So, if one types 1,3,4,3,5,6 in range A1:A6, then the mode will be 3 - 3 appears maximum number of times in the range.

In MS Excel, there is a built in way to compute the MODE.  The formula for the same is

=MODE(A1:A6)

Unfortunately, MODE() is not a 3D function and therefore, something like this return a #REF error

=MODE(sheet1:sheet3!A1:A6)

This behavior seems somewhat vague because other basic Mathematical and Statistical functions such as SUM(), COUNT(), AVERAGE(), MAX(), MIN(), VAR(), and STDEV() work just fine across multiple worksheets.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem.

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  In Power Query, there is a feature to append data from multiple worksheets into one running range.  Once this is done, the running range can be transferred to an MS Excel worksheet on which the MODE function can be used.

Solution for all versions of MS Excel

To compute MODE across multiple worksheets, you may refer to my solution in this workbook.

Determine cumulative expenses per employee when per diem rates vary by block of dates

{8 Comments}

Assume per diem travel rates vary by block of dates (from and to).  So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day.  Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day.  With different travel dates (from and to) specified per traveller, the task is to determine total travel expenses per individual.

You may refer to my solution in this workbook.

Show granular as well as total figures on the Summary sheet

{0 Comments}

Assume there is data for complaints received and complaints resolved for two years - 2009 and 2010 for some regions regions.  Each Region has its own worksheet.  There are three headings on each sheet - Particulars, 2009 and 2010.  While the Particulars column has the text "Complaints received" and "Complaints resolved", the year columns have number of complaints received and resolved.

The task is to create a Summary sheet which:

1. Show the consolidated figure of complaints received and complaints resolved for both years
2. Show the individual regions which make up the consolidated figure in 1 above.

In this workbook, you may see the data layout and four different ways of resolving the two tasks mentioned above.

You may also refer to the following related posts:

1. Summarise data from multiple worksheets with one condition
2. Summarise data from multiple worksheets with one condition - Part II
3. Summarise data from multiple worksheets
4. Summarise data from multiple worksheets with multiple conditions
5. Summarise data from multiple worksheets with multiple conditions - Part II
6. Summarise data from different cells of multiple worksheets

Generate a list of missing invoice numbers

{2 Comments}

In a range of cells, assume there is a list of invoices raised during a certain period.  Since adequate care was not taken to number the invoices sequentially, there are missing invoice numbers in the Excel range.  So, it is possible that after raising invoice number 501, 502 and 503 the next invoice raised was 507.

Given the following:

1. Starting and ending invoice number (two numbers) from the invoice book; and
2. List of actual invoice numbers raised during a certain period (as elaborated above)

one may want to generate a list of missing invoices.

You may refer to two solutions in this workbook.

LOOKUP unique data from multiple columns where search string appears multiple times

{2 Comments}

In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched.  Furthermore,  the VLOOKUP() function returns data from one column only.

To work with a list where there are multiple occurrences of a particular string and data has to be extracted from multiple columns, you may refer to my solution in this workbook.

To work with a list where there are multiple occurrences of a particular string and data has to be extracted from one column only, you may refer to my solution at this link.

Sum data from a particular cell of last n sheets only

{0 Comments}

A number is entered in cell A1 of every sheet in a multi sheet workbook.  The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets - x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed up.  So if the number entered in cell B2 of sheet is 3, then sum numbers from cell A1 of last three sheets.  Worksheets can get added, removed or renamed.

You may refer to my solution in this workbook.

Worksheet formulas in a newly copied worksheet should point to the previous worksheet

{15 Comments}

Assume a worksheet with formulas referring to the previous worksheet.  When this sheet is copied by right clicking the sheet and selecting Move or Copy > Copy, formulas in this newly created sheet, should change to refer to the previous sheet.

In other words, if sheet2 has formulas referring to sheet1, then when sheet3 is created (by Move or Copy > Copy of sheet2), the formulas in sheet3 should refer to sheet2.

You may refer to my solution and Instructions in this workbook.

Compare value of one cell with value of next visible cell of a filtered range

{0 Comments}

Assume data in range B4:B18 (heading is in cell B3).  To compare the value in the current row with the value in the row below, one may enter the following formula in cell C4 and copy down

=B4=B5

True's indicate matching values with the value in the next row.  However, the above formula would fail when an auto filter is applied to the range.

To compare the value in the current row with the value in the next visible row below, refer to my solution in this workbook.

Performing an iterative lookup to return closest match

{5 Comments}

Suppose a worksheet name is Code_details.  The following values lie in range A2:A14 of this sheet:

A12,AS478,QW447,EQ46,RYT9985,VCX147,BNM159,ASQ478,VC325,ASW675,A123,
ASDFG1234567890 and ASDFG123456789

Suppose there is another worksheet named Summary where the following values lie in range B5:B7:

ASQ47809876,ASW675458 and QWERT

Now one may want to lookup ASQ47809876 in A2:A14 of Code_details sheet.  If this exact value is not found there, then the lookup value should be trimmed by one digit/character from the extreme right i.e. the lookup value should now become ASQ4780987.  The search should carry on till an exact match is found.

You may refer to my solution in this workbook.

Apportion a number over empty cells

{10 Comments}

Assume a matrix like data layout where Site's are mentioned in range C2:C15 and first day of every month from January 2011 till April 2012 are mentioned in range D1:S1.  In range D2:S15, the numbers represent the value of the electricity bill received in that month.  Some cells can be blank representing bills not received in that month.

One may want to apportion the value of bills received equally over months for which bills were not received.  The assumption in doing so is that if a cell in empty i.e. bill not received in the month, then it will be included in the next bill.  The problem also is that there can be a variable number of empty cells between various bills received.

You may refer to the "Result" sheet in the this workbook.