Intermediate Level What is the XLOOKUP function, and how does it improve upon older lookup functions like VLOOKUP or HLOOKUP? The XLOOKUP function is an advanced lookup tool in Excel that searches for a value in a range or array and returns a corresponding result....

Intermediate Level Explain the working of SUMPRODUCT function and cases in which we can use this function? The SUMPRODUCT function multiplies corresponding values in two or more arrays and then sums up the results. To calculate a weighted average, you can use the following steps:...

Intermediate Level How would you use the INDEX and MATCH functions together to perform a two-way lookup in Excel? • Perform a lookup based on both row and column values, more versatile than VLOOKUP. • INDEX(range, row_num, column_num): Returns the value at the intersection of...

Intermediate Level Run down the process of creating a drop-down list in Excel? To create a drop-down list in Excel: 1. Enter the list of items you want in the drop-down list in a column (e.g., A1:A5). 2. Select the cell where you want the...

Intermediate Level How to merge two DataFrames on a common column? df1 = pd.DataFrame({"A":[1,2,3], "B":[11,12,13]}) df2 = pd.DataFrame({"A":[1,2,4], "B":[21,22,23]}) new_df = pd.merge(df1, df2, how='inner', on='A') new_df A B_x B_y 0 1 11 21 1 2 12 22 How to apply a function to each element...

Intermediate Level How to create a NumPy array of evenly spaced values between a given range? arr = np.arange(10,100,5) arr How to create a DataFrame from a list of lists or list of tuples? data = [ [1,'Alice',88], [2, 'Bob',90], [3, 'Charlie',92] ] df =...