Tue 29 Sep 2015

Solution - Challenge 24 – Sum a Range Conditionally where Range Inputs are variables

By |Tuesday, September 29th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the Challenge 24 – Sum a Range Conditionally where Range Inputs are variables

Put following formula for the result -

=SUMPRODUCT((INDEX(INDIRECT(D1):INDIRECT(D2),,)>D3)*(INDEX(INDIRECT(D1):INDIRECT(D2),,)))

The workbook illustrating the solution can be downloaded from Solution - Challenge 24 – Sum a Range Conditionally where Range Inputs are variables

Sat 26 Sep 2015

Challenge 26 - Find Sum given multiple OR conditions across columns

By |Saturday, September 26th, 2015|Categories: Challenges|Tags: , , , , |1 Comment

-- The Excel file related to this challenge can be downloaded from Challenge - Multiple OR Conditions ---

Given your data below, you need to find the total sales for cities given in F1 to I1 and zones given in F2 to I2. Either city has to match in F1 to I1 or zone should match in F2 to I2. If both city and zone match, it should be considered once only not twice.

Seattle East 20
Seatltle West 10
Miami East 30
Denver South 50

The answer for above for Seattle and East should be 20+10+30 = 60 not 20+20+10+30 = 80

Multiple OR Conditions

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 25-Oct-15.

Sat 26 Sep 2015

Article 26 - Converting Amount into Words (Indian Currency - Rupees and Paise)

By |Saturday, September 26th, 2015|Categories: Articles, VBA|Tags: , , , , , , , |0 Comments

Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh.....unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System)

Below are numerical equivalent of Indian words

Lakh - 1,00,000
Crore - 1,00,00,000
Arab - 1,00,00,00,000 (more…)

Mon 21 Sep 2015

Excel Quiz 21 - Crossword - V

By |Monday, September 21st, 2015|Categories: Quizzes|Tags: , , |0 Comments

Time for Crossword Again. This crossword is interactive and you can use browser to play with this. Click in the Grid to start.

Click on Check Puzzle just above Questions to check your score.

If you want to print the Crossword on a paper to play with, download from here Excel Crossword 5

The answer key can be downloaded from here  Excel Crossword 5 Answers

If the below Crossword is not properly visible because of WordPress plugin limitation, you can play it properly here  Excel_CW_5

(more…)

Sat 19 Sep 2015

Tips & Tricks 110 - Financial Functions - Calculate CAGR and AAGR

By |Saturday, September 19th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Many times we get posed with the question of calculating CAGR (Compounded Annual Growth Rate) and AAGR (Average Annual Growth Rate). For layman's not knowing what are these, following are good resources to introduce people to CAGR and AAGR.

CAGR - http://www.investopedia.com/terms/c/cagr.asp
AAGR - http://www.investopedia.com/terms/a/aagr.asp

The formula to calculate CAGR is very simple. RATE function can be used to calculate CAGR. We need to know only PV, FV and NPER. Let's assume that you invested $10,000 (hence, this is PV ) and it has become $16,448 (hence, this is FV) after the end of 5 years. So, to calculate CAGR, we will use following formula -

=RATE(5,,-10000,16448)

=RATE(5,,10000,-16448)

Hence, in the above formulas, you need to put either PV as negative or FV as negative. Both can not be positive or negative at the same time.

If you don't have FV but a series of fixed payments (say investment of 3000 every year), you can use -

=RATE(5,-3000,10000)

=RATE(5,1000,-10000)

Now, let's come back to AAGR. There is no single formula for AAGR. If your data set is laid like below, you can use following formula to calculate AAGR -

=SUMPRODUCT((B3:B11-B2:B10)/(B2:B10))/(ROWS(B2:B10))

1

Sat 19 Sep 2015

Tips & Tricks 109 - Macro to Change between A1 and R1C1 Notations

By |Saturday, September 19th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns.

The option to do it very simple -

File > Excel Options > Formulas > Uncheck R1C1 Reference Style

1

But if that happens very often, you can use a macro which you can click and toggle between A1 and R1C1 style effortlessly. The macro code to do it one line only -

Sub ChangeReferenceStyle()
Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle
End Sub

Credit - Rick Rothstein (http://blog.contextures.com/archives/2009/12/04/excel-vba-switch-column-headings-to-numbers/)

The above link also talks about how to add the macro to QAT so that it is always available to you.

Mon 14 Sep 2015

Solution - Challenge 23 – Make Bar Chart to Show Performance

By |Monday, September 14th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

-- The solution workbook related to this challenge solution can be downloaded from Solution - Challenge 23 – Make Bar Chart to Show Performance --

Below is a possible solution to the challenge Challenge 23 – Make Bar Chart to Show Performance

1. Download the workbook from Salesman Performance Challenge
2. Select range A1:C7
3. Insert > Chart > Choose the appropriate bar chart. You will get the chart like below -

(more…)

Sat 12 Sep 2015

Challenge 25 - Prepare a Project Status Chart with Sliders

By |Saturday, September 12th, 2015|Categories: Challenges|Tags: , , , |1 Comment

This time challenge is to prepare following chart for Project Status. I am not giving any Excel sheet for this challenge. Just think and be creative.

Project Status with Sliders

You may post your solution in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 11-Oct-15.

Sat 12 Sep 2015

Article 25 - Reverse FIND / SEARCH & MID Function

By |Saturday, September 12th, 2015|Categories: Articles|Tags: , , , , , , |0 Comments

Excel doesn't offer any function for reverse FIND / SEARCH & MID function. VBA does offer a function INSTRREV but this article is for non-VBA folks or folks who do not want to use VBA in their spreadsheet.

Hence, the only option before us is to build them through formulas. Let's build a reverse FIND / SEARCH function.

Note - There are two differences between FIND and SEARCH which you must make a note of.

(more…)

Sun 06 Sep 2015

Solution - Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves

By |Sunday, September 06th, 2015|Categories: Solutions|Tags: , |0 Comments

Below is a possible solution to the challenge - Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves

If A1 and A2 contain the start and end dates, put following formula and drag down -

=IFERROR(AGGREGATE(15,6,(ROW(INDIRECT($A$1&":"&$A$2)))/((TEXT(ROW(INDIRECT($A$1&":"&$A$2)),
"ddd")="Fri")*(TEXT(ROW(INDIRECT($A$1&":"&$A$2)),"dd")="13")),ROWS($1:1)),"")

A workbook illustrating the same can be downloaded from Solution - Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves