Input: Sample file with base data.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from the sample file and applies a filter to get all the loans with interest rate greater than 8.

Input: Sample file with base data.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from the sample file. Report a custom column having one forth of amount for interest greater than 8.

Hint:

1.Select Range and apply condition where interest is greater than 8.

2.Select custom column, mapping should be done to first cell of amount column and divide it by 4 (if $A$2 is starting entry cell of amount column then write $A$2/4)

3.Report the column name with 'Updated Amount'.

Input: Sample file with base data.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from the sample file and apply a filter to get all the Product having substring DEMAND only. (should not contain word similar to DEMANDED,DEMANDING).

Hint:

Use the filter condition 'contains substring' and 'Text ends with' on product column to get desired output.

for solution click here.

Input: Sample file with base data.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from the sample file and apply a filter to get latest 3 distinct date for which data is available in sample file.

Hint:

1.Use distinct option for date column in filter operation

2.Apply filter again, select top 3 rows and sort date in descending order.

Input: Sample file with base data.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from the sample file and apply a filter to get all the entries which does not belong to the company name "LLOYD ELECTRIC & ENGINEERING" with interest rate greater than 7 or date less than 01-01-2020.

Input: Sample file with base data.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from the sample file and apply a filter to get all the #NA in currency column and replace it with INR.

Input: Sample file with base data.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from the sample file and apply a filter that matches a regex with dd-mmm-yyyy format in date column.

hint: Use the function 'text matches regex' in condition section. (regex string- ^\d{1,2}-[a-zA-Z]{3}-\d{4}$).