Tue 28 Jul 2015

Solution - Challenge 19 – Make Comparative Horizontal Bar Graph

By |Tuesday, July 28th, 2015|Categories: Solutions|Tags: , , , , , |1 Comment

Below is a proposed solution to the challenge - Challenge 19 – Make Comparative Horizontal Bar Graph

1. Download the Excel sheet from the challenge.

2. Put "F" without quotes in D1 and put following formula in D2 and drag down

=-C2

3. Now select A1:B9, hold CTRL and select D1:D9.

4. Insert the bar chart from Insert Menu.

1

5. Change the title to "Literacy in US States" without quotes and format it in bold and increase the size appropriately.

6. Remove the unnecessary vertical lines.

7. Select the x-axis values, right click > Format Axis > Select Label position as High under labels. This will send x axis to top. Under Number > Format Code, put "0%;0%" without quotes and press Add. This will convert -ve %ages into +ve.

8. Click the chart. Now, design will be enabled in menu bar. Add Chart Element + > Data Labels > Outside End.

9. Right click on any -ve % value for F bars and Format Data Labels > Under Number > Format Code, put "0%;0%" without quotes and press Add. This will convert -ve %ages into +ve.

10. Right click on Orange bars i.e. bars corresponding to F > Format Data Series > Put 100% in Series Overlap to align right and left bars and put 50% in gap width to increase the width of bars.

11. Select vertical axes value and right click > Format the font to White and Bold.

12. Right click on legend and format the font appropriately by increasing the size.

Finally, the chart would like below -

1

The solution can be downloaded from following link  Literacy Rates Graph with Answer

Sat 25 Jul 2015

Excel Quiz 17 - Crossword - IV

By |Saturday, July 25th, 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 4

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

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

(more…)

Sat 25 Jul 2015

Tips & Tricks 101 - Get Column Name for a Column Number

By |Saturday, July 25th, 2015|Categories: Tips and Tricks|Tags: , , , , |1 Comment

Let's suppose, you have a number in A1 and you want to get the column Name for that.

Hence, if A1=1, you want "A"
Hence, if A1 =26, you want "Z"
Hence, if A1=27, you want "AA" and so on.

The formula to derive the column name would be be -

=SUBSTITUTE(ADDRESS(1,A1,4),1,"")

Sat 25 Jul 2015

Tips & Tricks 100 - Get Sheet (tab) Name, Workbook Name and File Name through a formula

By |Saturday, July 25th, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

There are many situation while working in Excel that you need to get the name of the sheet. (Note - For formulas to work, the workbook must be saved at least once)

The formula to retrieve file name would be -

=CELL("filename",$A$1)

The formula to retrieve the sheet name would be -

=REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),"")

Note - CELL is a volatile function, hence this will calculated for every change in the sheet.

The formula to retrieve workbook name would be -

=REPLACE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))-1),1,FIND("[",CELL("filename",$A$1)),"")

Note - CELL is a volatile function. Hence, the formulas would recalculate every time, the worksheet changes.

 

Sun 19 Jul 2015

Article 18 - LTRIM and RTRIM through Excel Formulas

By |Sunday, July 19th, 2015|Categories: Articles|Tags: , , , , , , , , |0 Comments

So, we have TRIM function in Excel. Almost all programming languages provide LTRIM and RTRIM functions also but Excel doesn't provide LTRIM and RTRIM. The same is provided in VBA but most of the Excel users are not using VBA. They are simple folks who want to accomplish their day to day job through Excel functions only.

Before I delve into LTRIM and RTRIM, let's recap TRIM.

TRIM, basically, removes all spaces from your string and if there are more than one space between your words, it will convert them into only one space. So, it removes all leading spaces, all trailing spaces and all spaces between words except one. If there is only one space between words, it will leave that space untouched.

(more…)

Sat 18 Jul 2015

Challenge 21 - Male Female Pie Chart with Pictures

By |Saturday, July 18th, 2015|Categories: Challenges|Tags: , , , , , , |1 Comment

This time, you have a challenge to create a Male Female Percentage chart with pictures of Male and Female to denote the labeling of pie slices. There are 2 pie charts for your challenge.

The files related to this can be downloaded from following links -

Male_Female_Pie_Chart Excel File  Male Picture -1st Pie  |  Female Picture - 1st Pie

Male Parliamentarian - 2nd Pie  |  Female Parliamentarian - 2nd Pie  |  Parliament - 2nd Pie

Male_Female_Pie_Chart

Male_Female_Pie_Chart_2

Note - You can post you answer in comments.

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

Fri 17 Jul 2015

Solution - Challenge 18 – Find the Longest Word in a List

By |Friday, July 17th, 2015|Categories: Solutions|Tags: , , , , |1 Comment

Below is a proposed solution for the challenge - Challenge 18 – Find the Longest Word in a List

Use following formula -

=INDEX(A1:A10,AGGREGATE(15,6,ROW(A1:A10)/
((MAX(INDEX(LEN(A1:A10),,))=LEN(A1:A10))),1))

The solution file can be downloaded from following - Solution - Challenge 18 - Longest Word

 

Fri 17 Jul 2015

Solution - Challenge 17 – Sum if Y Appears More than One Time Consecutively

By |Friday, July 17th, 2015|Categories: Solutions|Tags: , , , , , , , |0 Comments

Below is a proposed solution for challenge Solution - Challenge 17 – Sum if Y Appears More than One Time Consecutively Maximum

1. Introduce a helper column in column D and put following formula in D2 and drag down -

=IF(B2="N",0,IF(AND(A2=$F$2,A1=A2),1+D1,1))

2. Put following formula in F3

=IF(ISNUMBER(MATCH(F2,A:A,0)),SUM(OFFSET(C1,MATCH(MAX(D2:D22),D2:D22,0),
0,-MAX(D2:D22),1)),"")

Tue 14 Jul 2015

Solution - Challenge 16 – Team Pairing

By |Tuesday, July 14th, 2015|Categories: Solutions|Tags: , , , , , , , , |0 Comments

Below is a proposed solution for the challenge Challenge 16 – Team Pairing

I am going to discuss 2 solutions -

1. One where number of teams is fixed and we are looking at a simple solution.

2. A generic solution which will work for any number of teams. You will need to just do a find and replace on $21 and replace this by required number.

(more…)

Tue 14 Jul 2015

Solution - Challenge 15 – Floyd’s Triangle

By |Tuesday, July 14th, 2015|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a proposed solution for the challenge Challenge 15 – Floyd’s Triangle

Put this formula anywhere in your sheet and drag right and down

=IF(COLUMNS($A:A)>ROWS($1:1),"",IF(ROWS($1:1)=1,1,ROWS($1:1)-2+
COLUMNS($A:A)+INDIRECT(ADDRESS(ROW()-1,COLUMN()-COLUMNS($A:A)+1,1))))

The solution workbook is located at Solution - Challenge 15 – Floyd’s Triangle