18.6 Mu Sigma

M38. Write a formula to mark students Pass or Fail based on marks and attendance.

Reported for: Mu Sigma [S7] · also MathCo [S15]

=IF(AND(B2>=40,C2>=75%),"Pass","Fail") where B = Marks and C = Attendance %. With OR for "either" conditions: =IF(OR(B2<40,C2<75%),"Fail","Pass"). Keep thresholds in input cells for flexibility.

M39. How would you calculate a running total?

Reported for: Mu Sigma [S7] · also Amazon [S8]

=SUM($G$2:G2) copied down (expanding range). In a Table: =SUM(INDEX([Amount],1):[@Amount]). In a pivot: Show Values As › Running Total In › Order Date. Plot it as a line chart to show cumulative sales through the month.

M40. Explain advanced filters and sorting for segmentation.

Reported for: Mu Sigma [S7]

Multi-level sort (Data › Sort › Add Level: City then Amount descending) and custom-list sorts. Advanced Filter (Data › Sort & Filter › Advanced) uses a criteria range: conditions on the same row are AND, on different rows are OR – e.g. Pune & Delivered or Nagpur & Delivered – and can copy results to another location or extract unique records.

M41. Which advanced PivotTable features have you used?

Reported for: Mu Sigma [S7]

Show Values As (% of total, % difference, running total, rank), date and number grouping, calculated fields, Top-N value filters, slicers and timelines with report connections, Distinct Count through the Data Model, GETPIVOTDATA for dashboards, Show Report Filter Pages, and PivotCharts.

M42. Highlight regions where sales dropped by more than 10%.

Reported for: Mu Sigma [S7]

With last month in C and this month in D: select the rows › Conditional Formatting › New Rule › Use a formula › =$D2<$C2*0.9 › red fill. If a growth column exists, =$E2<-10% does the same.

M43. What are wildcards and how are they used?

Reported for: Mu Sigma [S7]

* matches any number of characters, ? one character, and ~ escapes them. Examples: =COUNTIF(A:A,"BLK-*") counts Blinkit orders; =SUMIFS(G:G,E:E,"*Milk*") sums all milk products; ? in "BLK-26031?" matches one digit. Wildcards also work in Find & Replace, filters, XLOOKUP (match mode 2) and MATCH.

M44. How do you check for inconsistencies between two columns?

Reported for: Mu Sigma [S7]

Row-wise: =A2=B2 or =EXACT(A2,B2) (case-sensitive), or Home › Find & Select › Go To Special › Row differences. Across lists: =COUNTIF(B:B,A2)=0 for missing items, conditional formatting of unique values, or a Power Query anti-join. Clean with TRIM first to avoid false mismatches.

M45. How do you protect a sheet or workbook to prevent accidental changes?

Reported for: Mu Sigma [S7]

Unlock input cells (Ctrl + 1 › Protection › untick Locked), then Review › Protect › Protect Sheet and choose allowed actions. Protect Workbook locks the structure (sheets can't be added, deleted or unhidden). For confidentiality use File › Info › Protect Workbook › Encrypt with Password. (Module 14.)

M46. How do you split text into columns, and what are Text to Columns and Flash Fill used for?

Reported for: Mu Sigma [S7] · also MathCo [S15]

Data › Data Tools › Text to Columns splits by a delimiter or fixed width and can also convert text dates (choose DMY). Flash Fill (Ctrl + E) fills a column from examples, e.g. first names from full names. Formulas (LEFT/MID/FIND, or TEXTSPLIT in Microsoft 365) keep the result live when the source changes.

M47. How would you transpose data from rows to columns?

Reported for: Mu Sigma [S7]

Copy › right-click › Paste Special › Transpose (static), =TRANSPOSE(A1:F2) (spills in Microsoft 365; live), or Power Query › Transform › Transpose. To turn month columns into rows for analysis, use Unpivot in Power Query instead.

M48. Show month-over-month growth for each region.

Reported for: Mu Sigma [S7] · also MathCo [S15]

Pivot: Region in Rows, Order Date grouped by Month in Columns, Sales in Values shown as % Difference From › Base field Month › (previous). With formulas: a Region × Month SUMIFS grid and =IFERROR(C2/B2-1,"") for each month.

M49. How do you create a dynamic chart that updates when data changes?

Reported for: Mu Sigma [S7]

Base the chart on a Table (new rows appear automatically), on a PivotTable with slicers, on dynamic named ranges (INDEX/OFFSET), or on a helper range driven by a drop-down selection with XLOOKUP. (Modules 8.13–8.15.)