Challenge No. 85: In the Question Table, values are provided and we want to rank them in ascending order based on custom logic.
Solved using:Excel (FILTER, HSTACK, IF), Power Query (List.Sort, List.Transform), Python, and R.
LET
Assigns names to values or calculations to simplify formulas in Excel.
Apply Caesar Cipher with Shift
Challenge No. 26: Caesar’s Cipher – This is second challenge on Caesar’s cipher.
Solved using: Power Query.
List Numbers After Last Eight
Challenge No. 23: Provide a formula to list all numbers greater than 8 after last 8 in A2:A20.
Solved using: Python in Excel.
Normal Distribution!
Challenge No. 84: Normal distribution was a kind of nightmare in university for me, but now let’s solve it using Excel.
Solved using:Excel (HSTACK, IF, INDEX), Power Query (Table.AddColumn), Python, Python in Excel, R, and VBA.
List Valid IP Addresses
Challenge No. 22: Provide a formula to list all valid IP addresses from A2:A12.
Solved using: Power Query.
Clean Data and Restructure Table
Challenge No. 8: (Excel formulas also welcome)
Clean the data and generate the result table as shown.
Solved using: Power Query (Table.AddColumn, Table.TransformColumns).
Missing Triangle Side Integer
Challenge No. 21: A right angle triangle is that for whom following property is satisfied.
Solved using: Python in Excel.
Top Scorers by Subject
Challenge No. 20: List the distinct Subjects and Students who have scored the highest marks in those subjects along with Marks scored by them.
Solved using: Python in Excel.
List Longest Words in Sentence
Challenge No. 19: Provide a formula to list longest words in sentences given in A2:A10.
Solved using: Python in Excel.
Custom Splitter (Part 3)!
Challenge No. 83: In the Question table, sales information, including dates, product names, and quantities, is provided in a single cell.
Solved using:Excel (IFERROR, LET, ROWS), Power Query (List.Transform, Text.Split, Table.FromRows), Python, Python in Excel, R, and SQL.