Sum Every nth Data in a Range

My Struggle and at the same time great fun with Excel continues... Must say, there's something new that I learn almost everyday....

Hurray ! I got a solution to this one and this time was quick too... Bt ain't sure if this is the most efficient solution because I had to insert an additional column to get the desired result.

Scenario:
From the given range of data, user needs to sum only nth data. Say, sum every 3rd data or every 5th data, etc
Refer to the below snapshot:



From the Snapshot:
1. Sample Data
2. Cell where criteria to sum every nth data is given
3. Additional inserted column
4. Refers to cell where result is displayed

Hereby is the list of steps:
1. In the inserted additional column A, use the below formula in Cell A2:
=IF(MOD(ROW(B2)-1,$E$1)=0,1,0)



2. Copy the formula to the cell which has data in adjacent column B

Remarks: You will get 1 in every cell which matches the criteria given in cell E1.

3. For Result in Cell E2, used the formula: =SUMIF(A2:B11,1,B2:B11)



4. Verify the result by changing the data in Cell E1.

Mission Accomplished !!

Chart as per the Worksheet Name

Issues or rather room for improvement seems never ending with growing use of excel !!

Over the weekend, my client asked for a solution on excel which at the start looked very easy. But when I sat down to think about the possible solution, I was running short of ways... Thankfully, sense prevailed and I could come out with a rather easy to implement solution. It took me about 45 minutes but would definitely like to better the time taken.

Reqmt:
To get the chart in the Summary worksheet as per the worksheet name entered in cell B2. [Example used here is with dummy data to replicate the scenario]

Challenge was to ensure that the requirement had to be solved without coding. It meant that I had to solve with just functions, cell referencing and suitable data arrangement.



Approach:
I thought of using Consolidation, 3D Referencing, data indexing, etc but to no use. Eureka !! thought of using Indirect( )

In Summary WS:
1 Worksheet name is entered in Cell B1.

2 Create Custom Type - Column with depth chart for data range from C2:D8.

3 In the cell address c2, to get the Col A data from the worksheet as specified in cell B1, type the following function
=INDIRECT($B$1&"!"&ADDRESS(ROW(A2),COLUMN(A2)))

Drag the formula to the required number of rows from the worksheet name entered in B1




4 In the cell address d2, to get the Col B data from the worksheet as specified in cell B1, type the following function
=INDIRECT($B$1&"!"&ADDRESS(ROW(B2),COLUMN(B2)))

Drag the formula to the required number of rows from the worksheet name entered in B1



5 Verify, by giving a different worksheet name in cell B1. Data from C2: D8 should change and chart should reflect view as per the new data.

Friends, I have few consultancy work this week on EXCEL and thus, I am sure by the weekend I will have several new topics to post...

Enjoy !!

Filter Data with Cell Fill Color

This is an interesting requirement that I came across yesterday for someone who I have given a nickname Fresher. Don't ponder for the reason, Even I don't understand why do I give such names... Anyways, let's come straight to the point.
Scenario: Filter the data from Col A as per the cell fill color
Reference: Below Snapshot



Solution:
Step-wise approach:
1. ADD a new column B [Here, it's label is 'Color' at B1]
2. Create a user-defined function cellcolor() [Refer below for it's code]
3. Press Alt+F11 -> Insert -> Module -> Paste the function code here
4. Save -> Alt + Q
6. Go to cell B2, type =cellcolor(A2,true) and hit Enter
7. Drag the formula to get result for other cells i.e. applied fill color
8. Now, you can use filter drop down and select the cell color by which you want to filter data



Enjoy !! - Dedicated to Fresher...

Function Code:
Function CellColor(rCell As Range, Optional ColorName As Boolean)
Dim strColor As String, iIndexNum As Integer

Select Case rCell.Interior.ColorIndex
Case 1
strColor = "Black"
iIndexNum = 1
Case 53
strColor = "Brown"
iIndexNum = 53
Case 52
strColor = "Olive Green"
iIndexNum = 52
Case 51
strColor = "Dark Green"
iIndexNum = 51
Case 49
strColor = "Dark Teal"
iIndexNum = 49
Case 11
strColor = "Dark Blue"
iIndexNum = 11
Case 55
strColor = "Indigo"
iIndexNum = 55
Case 56
strColor = "Gray-80%"
iIndexNum = 56
Case 9
strColor = "Dark Red"
iIndexNum = 9
Case 46
strColor = "Orange"
iIndexNum = 46
Case 12
strColor = "Dark Yellow"
iIndexNum = 12
Case 10
strColor = "Green"
iIndexNum = 10
Case 14
strColor = "Teal"
iIndexNum = 14
Case 5
strColor = "Blue"
iIndexNum = 5
Case 47
strColor = "Blue-Gray"
iIndexNum = 47
Case 16
strColor = "Gray-50%"
iIndexNum = 16
Case 3
strColor = "Red"
iIndexNum = 3
Case 45
strColor = "Light Orange"
iIndexNum = 45
Case 43
strColor = "Lime"
iIndexNum = 43
Case 50
strColor = "Sea Green"
iIndexNum = 50
Case 42
strColor = "Aqua"
iIndexNum = 42
Case 41
strColor = "Light Blue"
iIndexNum = 41
Case 13
strColor = "Violet"
iIndexNum = 13
Case 48
strColor = "Gray-40%"
iIndexNum = 48
Case 7
strColor = "Pink"
iIndexNum = 7
Case 44
strColor = "Gold"
iIndexNum = 44
Case 6
strColor = "Yellow"
iIndexNum = 6
Case 4
strColor = "Bright Green"
iIndexNum = 4
Case 8
strColor = "Turqoise"
iIndexNum = 8
Case 33
strColor = "Sky Blue"
iIndexNum = 33
Case 54
strColor = "Plum"
iIndexNum = 54
Case 15
strColor = "Gray-25%"
iIndexNum = 15
Case 38
strColor = "Rose"
iIndexNum = 38
Case 40
strColor = "Tan"
iIndexNum = 40
Case 36
strColor = "Light Yellow"
iIndexNum = 36
Case 35
strColor = "Light Green"
iIndexNum = 35
Case 34
strColor = "Light Turqoise"
iIndexNum = 34
Case 37
strColor = "Pale Blue"
iIndexNum = 37
Case 39
strColor = "Lavendar"
iIndexNum = 39
Case 2
strColor = "White"
iIndexNum = 2
Case Else
strColor = "No Fill "
End Select

If ColorName = True Or strColor = "Custom color or no fill" Then
CellColor = strColor
Else
CellColor = iIndexNum
End If

End Function

Introduction

The reason I felt the need of creating a blog is to share my knowledge with you on Microsoft Application EXCEL. Being in the corporate world for over 6 years has been great and much to my delight. I always got to learn something new. Also, got certified with Microsoft on EXCEL 2007 (MCAS) which was definitely fun. 3 years back, I knew not much about Excel except for the very basics that it's used to store data in tabular format, etc. I used to wonder if there were ways to store data in a systematic and useful manner on Excel. But, one of the requirements of my client changed my ways of thinking. One of the highlight of my career is that in short span of 6 years in IT, I got a chance to interact with over 60 Companies, 10K Professionals. Thanks to my approach of consulting.

To begin with, I would post blog on topics from EXCEL to cover the Basic, Intermediate and Advance concepts. Going forward, would stress upon Data Handling tips and tricks on Excel.

This blog is dedicated to all of you who wants to learn more about Excel and unravel the ways to efficiently use its features.

Incase of any suggestions, please feel free to post.

-Thank you (धन्यवाद)