Tags: MIN

Story telling with Excel Power BI

{6 Comments}

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View).  Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)

1. Power Query; and
2. Power Map

I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:

1. An overview of the BRIC Economies
2. Sales data analysis

Just in case you do not have Microsoft Office Professional Plus 2013, you can see the features of Power BI in this 10 minutes video of the first workbook - "An overview of the BRIC Economies".

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.

Minimise the total number of inverters to be used for different electricity load factors

{0 Comments}

Assume there are two types of inverters - Inverter1 with a capacity of 15kW and Inverter2 with a capacity of 20kW.  For an electricity load factor of, say 60kW, the objective is to determine the minimum number of inverters to be used.  Therefore the number of inverters which can be used for a load factor of 60kW are:

Option1: All 4 of 15kW; or
Option2: All 3 of 20kW

Since the objective is to mimimise the number of inverts to be used, the result should be all 3 of 20kW.  To take another example, for an electricity load of, say 100kW, the number of inverters which can be used are:

Option1: All 5 of 20kW; or
Option2: 2 of 20kW and 4 of 15kW

The result should be Option1 because the number of total inverters used is minimised there i.e. 5 instead of 6 in Option2.

To acheive this via Excel formulas, try this

1. Enter 15 in cell C2 and 20 in cell C3
2. In range A8:A18, enter different load factors such as 45,50,60,100,75,220,240,90,120,160,275
3. Enter 15 in cell B7 and 20 in cell C7
4. In cell B8, enter the following array formula (Ctrl+Shift+Enter) and copy down till cell B18

=SUMPRODUCT(((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))=MIN(IF((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))>0,(INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,)))))*({0,1,2,3,4,5,6,7,8,9,10}))

5. In cell C8, enter the following array formula (Ctrl+Shift+Enter) and copy down till cell C18

=SUMPRODUCT(((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))=MIN(IF((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))>0,(INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,)))))*({0;1;2;3;4;5;6;7;8;9;10}))

For each load factor, the combination of inverters of each capacity will be the minimum number of inverters (of all possible combinations) that can be used.

The assumption in both formulas above is that no more than 10 inverters of each inverter type will ever be used i.e. no more than 10 inverters of 15kW and no more than 10 inverters of 20kW will be used.  This assumption can obviously be relaxed by making minor changes to both formulas.

Calculate turn around time excluding Sundays and public holidays

{55 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 numeric data and dates from string

{12 Comments}

In column A, there are some alphanumeric entries.  Somewhere in the alphanumeric entries (beginning, middle or end) there are the following:

1. Dates
2. Numbers (without decimals)
3. Numbers (with decimals)

Furthermore, there may or may not be spaces between numbers/dates and text entries.  In Excel 2010 and prior versions, the only way to solve this problem would be write a formula or a VBA code.  In Excel 2013, one can solve this problem by using the "Flash Fill" feature.  Flash Fill is identifies patterns in your sample output data which you type (for a few entries) and suggests the output for the remaining cells of the range.

You may refer to my solution in this workbook.

Return closest numeric match

{2 Comments}

In range A2:A6, the numbers are 7.5, 2, -12, 11.2 and 8.  In cell B8, a user enters 8.  One may want to answer the following questions:

1. Return the closest number which is less than or equal to the number in cell B8
2. Return the closest number which is greater than or equal to the number in cell B8
3. Return the closest match

You may refer to my solution in this workbook.