Below is a possible solution to the Challenge 64 – Sum up the Range where a particular alphabet appears =SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))
Category: Challenges
Challenge 64 – Sum up the Range where a particular alphabet appears
Suppose, you have been given a range like this and you need to find the sum of column B where the alphabet "c" appears alone. To check your answer, the sum will be 27 for above. The file related to this challenge can be downloaded from Challenge 64 – Sum up the Range where a…
Solution – Challenge 63 – Convert to Date Format
Below is a possible solution to the Challenge 63 – Convert to Date Format Put following formula and drag down =IFERROR(–SUBSTITUTE(A1,",",""),–SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,",","")," ","*",2),"*",", "))
Solution 62 – Produce the Sum for Merged Cells Headers
Below is a possible solution for the challenge – Challenge 62 – Produce the Sum for Merged Cells Headers Put following formula B14 and drag right and down =SUM(OFFSET($A$1,ROWS($1:1),MATCH(B$13,$1:$1,0)-1,,IFERROR(MATCH(C$13,$1:$1,0),COUNTA($2:$2)+1)-MATCH(B$13,$1:$1,0)))
Challenge 63 – Convert to Date Format
This time, you have been given a file containing Text dates into various formats. The challenge before you is to write a single formula to convert them into an appropriate date format. The challenge file can be downloaded from Challenge 63 – Convert to Date Format The solution to the above challenge will be published…
Challenge 62 – Produce the Sum for Merged Cells Headers
Download the workbook related to this challenge – Sum for Merged Cells This time challenge is to produce the sum where header is merged cells. For the layout like below – You need to write a single formula which can be dragged right and down to generate the sum for below table The solution to…
Solution – Challenge 61 – Generate Multiplication Table
Below is a possible solution to the challenge – Challenge 61 – Generate Multiplication Table Put following formula and drag right and down – =ROWS($1:1)*COLUMNS($A:A)
Challenge 61 – Generate Multiplication Table
This time, I want to set a challenge which is not difficult and useful for your kids. The challenge is to write a formula which can be dragged right and down to generate a multiplication table. The solution to this challenge will be published after a month i.e. on 1-May-17.
Solution – Challenge 60 – Generate a list of 2nd and 4th Saturdays for a Year
Below is a possible solution to the challenge – Challenge 60 – Generate a list of 2nd and 4th Saturdays for a Year Drag the below formula down =IF(ROWS($1:1)<25,FLOOR(DATE($A$1,ROUNDUP(ROWS($1:1)/2,0), 2*(MOD(ROWS($1:1)-1,2)+1)*7),7),"")
Solution – Challenge 59 – Clean the Problem Workbook Data
Below is a possible solution to the problem Challenge 59 – Clean the Problem Workbook Data Formula to convert would be which you need to drag down would be =–SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8237),""),UNICHAR(8236),"") Data has UNICHAR(8237) and UNICHAR(8236) prefixed and suffixed which need to be replaced by above formula.