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 –…
Solution – Challenge 13 – Generating Digit Product Sequences
A proposed solution is listed below for the challenge – Challenge 13 – Generating Digit Product Sequences Put a starting seed in A1. Put following formula in A2 and drag down – =A1+PRODUCT(INDEX(–MID(SUBSTITUTE(A1,0,1),ROW(INDIRECT("1:"&LEN(A1))),1),,)) For a seed value of 1 in A1, it will generate following sequence – 1, 2, 4, 8, 16, 22, 26, 38,…
Solution – Challenge 12 – Student Scoring Maximum Total Score
Below is a proposed solution for the challenge Challenge 12 – Student Scoring Maximum Total Score Put following Array formula in N2 =INDEX(A2:A20,MATCH(MAX(MMULT(B2:K20,TRANSPOSE(COLUMN(B2:K20)^0))), MMULT(B2:K20,TRANSPOSE(COLUMN(B2:K20)^0)),0)) Put following Array formula in N3 =MAX(MMULT(B2:K20,TRANSPOSE(COLUMN(B2:K20)^0))) The solution sheet for this can be downloaded from Solution – Challenge 12 – Student Scoring Maximum Total Score
Solution – Challenge 11 – Generate a Repeating Number Sequence – II
Below is a proposed solution for challenge – Challenge 11 – Generate a Repeating Number Sequence – II Put following formula and drag down – =ROUNDUP(ROWS($1:1)/3,0) This will repeat the sequence 111222333444555………..
Solution – Challenge 10 – Generate a Repeating Number Sequence – I
Below is the proposed solution for challenge – Challenge 10 – Generate a Repeating Number Sequence – I Put following in a cell and drag down – =MOD(ROWS($1:1)-1,5)+1 This will generate a sequence 1, 2, 3, 4, 5 and will repeat this.