Sat 27 May 2017

Excel Quiz 52

By |Saturday, May 27th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , |0 Comments

Excel Quiz 52 - A Quiz on Formulas Tab in Excel

This quiz checks your knowledge about Formulas tab.

Mon 23 May 2016

Solution - Challenge 41 – Sum the Maximum Number where duplicates Exist

By |Monday, May 23rd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 41 – Sum the Maximum Number where duplicates Exist

Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=SUM((MATCH(A2:A10,IF(COUNTIFS(A2:A10,A2:A10,B2:B10,">"&B2:B10)=0,
A2:A10),0)=ROW(A2:A10)-MIN(ROW(A2:A10))+1)*B2:B10)

The workbook illustrating the above solution can be downloaded from Solution - Challenge 41 - Sum the Maximum Number where duplicates Exist

Sat 23 Apr 2016

Challenge 41 - Sum the Maximum Number where duplicates Exist

By |Saturday, April 23rd, 2016|Categories: Challenges|Tags: , , , , , |1 Comment

Suppose, you have been given following and you will need to find duplicates in column A and sum the maximum values from column B. If duplicates don't exist, values will be taken as they are. The values which needs to be summed up are colored. The answer would be 123 in this.

1

The workbook related to this challenge can be downloaded from Challenge 41 - Sum the Maximum Number where duplicates Exist

The answer to this challenge would be published after a month i.e. on 23-May-16.

Sun 13 Mar 2016

Article 37 - VBA - Generating Unique (Non-repeating) Random Numbers Efficiently

By |Sunday, March 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100.

The algorithm to ensure uniqueness is following -

1. Given lower limit and upper limit, generate all numbers and populate an array. Now, this array contains all numbers between lower limit and upper limit sequentially. Hence, in case of 10 to 100, it will contain entries 10, 11, 12, .....99,100.
2. Next is to shuffle the array randomly using Fisher Yates algorithm so that the array contains the numbers 10, 11, 12...99,100 in a random order.
(more…)

Mon 10 Aug 2015

Solution - Challenge 20 – Find Number of Friday the 13th between Two Given Dates

By |Monday, August 10th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 20 – Find Number of Friday the 13th between Two Given Dates

The formula for finding this would be -

=SUMPRODUCT((TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Fri")*(TEXT(ROW(INDIRECT(A1&":"&A2)),"dd")="13"))

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

Mon 13 Jul 2015

Solution - Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String

Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=SUM(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)^2,0))

Mon 13 Jul 2015

Solution - Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution for the challenge - Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String

=IFERROR(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)^2),"")

Sat 11 Jul 2015

Challenge 20 - Find Number of Friday the 13th between Two Given Dates

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

I had read the example of this problem in Mike Girvin's book on Array Formulas. So, I am posing this challenge here for my readers.

Suppose, you have two given dates -

A1: 1/1/2013

A2: 12/31/2015

The challenge before you is to write a formula to find the the days which were Friday the 13th. For example, in 2015 following dates were Friday the 13th -

13-Feb-15
13-Mar-15
13-Nov-15

Hence, if date range is 1/1/2015 to 12/31/2015, the answer would be 3.

You may post your answer in Comments section.

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

Sat 27 Jun 2015

Challenge 19 - Make Comparative Horizontal Bar Graph

By |Saturday, June 27th, 2015|Categories: Challenges|Tags: , , , , , , , , , , |1 Comment

---- The worksheet for this challenge can be downloaded from Literacy Rates Graph ----

Suppose, you have been given data for two categories as given below. The challenge before you is to make a chart like below.

Literacy Graph Data

Literacy Graph Chart

You can choose to post your answer in comments section.

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