Challenge No. 140: For each customer, extract the ten consecutive days with the highest purchases.
Solved using:Excel (FILTER, HSTACK, IFS), Google Sheets, Power Query (Table.AddColumn, Table.Group), Python in Excel, and R.
IFS
Returns a value corresponding to the first TRUE condition in Excel.
Author Sale Difference
Challenge No. 133: List the authors who have written more than one book and have the highest difference between Max of Sold and Min of Sold.
Solved using: Python in Excel.
Compare Rows! Part 2
Challenge No. 111: In the historical sales table, extract the dates where the total sales value is greater than the total on the previous date
for example, in 2/1/2024 total sales were 14 and is greater than total sales on 1/1/2024 which is 11.
Solved using:Excel (DROP, FILTER, IF), Google Sheets, Power Query (Table.Group, Table.SelectRows), Python, Python in Excel, and R.
Duplicate Values! Part 1
Challenge No. 100: Consider the IDs in the question table.
Solved using:Excel (CHAR, COUNTIF, COUNTIFS), Google Sheets, Power Query (List.Transform, Table.AddColumn, Table.Group), Python, Python in Excel, and R.
Flag Columns with Special Keys
Challenge No. 24: Generate the result table.
Solved using: Power Query (List.Distinct).
Missing Values! Part 3
Challenge No. 92: In the question table, some cells (highlighted) are missing, but a character determines how to fill them based on the following rule:
R = right cell
L = left cell
U = upper cell
D = down cell.
Solved using:Excel (IF, IFS, INDEX), Power Query (List.PositionOf, List.Transform), Python, Python in Excel, and R.
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.
Find Common Animals
Challenge No. 13: Provide a formula to generate the list of animals who are common in all 3 columns.
Solved using: Python in Excel.
List Numbers Appearing Repeatedly
Challenge No. 6: Provide a formula to list numbers which appear more than once in Range A2:A20 and have both B and C in B2:B20.
Solved using: Python in Excel.
Convert Number To Text!
Challenge No. 22: Convert the numbers in column B to the text like column H
.
Solved using:Excel, Power Query, and R.