Tags: MAX

Compute MODE of all numbers split across multiple worksheets

{2 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.

Derive end date and time from start date and time, office working hours and lunch breaks

{6 Comments}

Given the following inputs/restrictions, one may want to compute the end date and time of a project:

1. Start date and time of the project; and
2. Official working hours; and
3. Lunch breaks hours

Furthermore, to add to the complication, one may have different National holidays and weekend days i.e. while for some, the weekend days may be Saturday and Sunday, for others it may be Friday and Saturday.

You may refer to both my solutions here

1. Given National holidays and where Saturday and Sunday are the weekend days - Download workbook.  This solution will work in all Excel versions

2. Given National holidays and where Friday and Saturday are the weekend days - Download workbook.  This solution will work only in Excel 2010 and higher versions.

Determine the maximum number of consecutive 1′s appearing in a range

{19 Comments}

Assume a database where customers are listed from cell A6 down.  From cell B5 to the right months are entered from April to March (B5:M5).  In B6:M6 (Customer 1), a user enters 1's and 0's.  A value of 1 respresents "Cheque bounced" and 0 represents "Cheque honoured".  Similar data is entered for other customers in B7:M500.

One may want to know the maximum number of consecutive "Cheque bounce events" for all customers listed in column A without using spare rows and columns.

In cell N6, enter the following array formula (Ctrl+Shift+Enter)

=IF(MAX(FREQUENCY(IF(B6:M6=1,COLUMN(B6:M6)),IF(B6:M6=0,COLUMN(B6:M6))))=1,0,MAX(FREQUENCY(IF(B6:M6=1,COLUMN(B6:M6)),IF(B6:M6=0,COLUMN(B6:M6)))))

Calculate turn around time excluding Sundays and public holidays

{46 Comments}

Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell).  Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays.

You may refer to my solution in this workbook.

Extract farthest/latest date based on multiple conditions

{0 Comments}

Assume a three column database showing Site ID, Customer, Status and Requested Date.  On the same site ID, the same customer may have different status on different dates.   In such a scenario, one may want to know the farthest/latest requested date and its corresponding status for all unique combinations of Site ID and Customer.

I initially attempted to solve this problem by using a pivot table but the pivot output was incorrect.  The pivot was returning the farthest/latest date for all status' of a particular Site ID and Customer.  Ideally, it should show only the farthest/latest date and its corresponding status for a particular Site ID and Customer.  Therefore, for a particular Site ID and Customer combination, only one row should show up in the final output.  Therefore, the Pivot Table solution did not work.

You may download this workbook for a better description of the problem and my workaround.

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)

Solution for all versions of MS Excel

You may refer my Advanced Filter (along with formula) solution in the workbook.

Extract number from an alphanumeric string

{18 Comments}

Given an alphanumeric string, one may want to perform the following

Extract phone number from the string

Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself.  These numbers may be mobile numbers and/or mobile numbers.  Furthermore, PIN codes may also be mentioned in the address string.

One may want to extract only the phone numbers to another column.

You may refer to the my solution in this workbook.

Extract one specific 20 digit number from the string

Assume cell descriptions which contains two 20 digit numbers occurring anywhere in the string.  Once may want to extract only that 20 digit number which has the word New before it.

You may refer to my solution in this workbook.