Challenge No. 86: K-nearest neighbors (KNN) is a simple technique for replacing missing values with the average of the nearest values.
Solved using:Excel, Power Query, and VBA.
LAMBDA
Creates custom reusable functions in Excel.
Repeated Letters in Cities
Challenge No. 30: Provide a formula to list the alphabets repeated in given cities.
Solved using: Power Query.
Validate ISBN-10 Numbers
Challenge No. 29: List all books from A2:A10 which have a valid ISBN-10 number.
Solved using: Python in Excel.
Filter Candidates by Skill Match
Challenge No. 27: A1:C9: List of candidates with their primary and secondary skills.
Solved using: Power Query (List.Intersect, Text.Split).
Apply Caesar Cipher with Shift
Challenge No. 26: Caesar’s Cipher – This is second challenge on Caesar’s cipher.
Solved using: Power Query.
Check 3×3 Magic Square
Challenge No. 25: Provide a formula to populate Yes or No against given 3×3 magic squares.
Solved using: Power Query (List.Transform).
List Prime Numbers Only
Challenge No. 24: List all prime numbers from A2:A10.
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.
Missing Triangle Side Integer
Challenge No. 21: A right angle triangle is that for whom following property is satisfied.
Solved using: Python in Excel.