Tips & Tricks 101 – Get Column Name for a Column Number
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,"")
Tips & Tricks 100 – Get Sheet (tab) Name, Workbook Name and File Name through a formula
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 –…
Article 18 – LTRIM and RTRIM through Excel Formulas
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…
Challenge 21 – Male Female Pie Chart with Pictures
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 | …
Solution – Challenge 18 – Find the Longest Word in a List
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
Solution – Challenge 17 – Sum if Y Appears More than One Time Consecutively
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)),"")
Solution – Challenge 16 – Team Pairing
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…
Solution – Challenge 15 – Floyd’s Triangle
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
Solution – Challenge 14 – Student Scoring Highest Marks in a Subject
A proposed solution is listed below for the challenge – Challenge 14 – Student Scoring Highest Marks in a Subject Put following Array formula in N2 =INDEX(A2:A20,MATCH(1,–(MMULT(–(MAX(B2:K20)=B2:K20),TRANSPOSE((COLUMN(B2:K20)^0)))>0),0)) Put following Array formula in N3 =INDEX(B1:K1,MATCH(MAX(B2:K10),OFFSET(A1,MATCH(1,–(MMULT(–(MAX(B2:K20)=B2:K20), TRANSPOSE((COLUMN(B2:K20)^0)))>0),0),1,1,COLUMNS(B2:K10)),0)) Put following (non Array) formula in N4 =MAX(B2:K20) The solution workbook can be downloaded from Solution – Challenge 14 –…