Describe how to apply conditional formatting in a spreadsheet to highlight sales figures that are both above the monthly average AND represent a new personal best for an individual salesperson.

To apply conditional formatting in a spreadsheet to highlight sales figures that are both above the monthly average and represent a new personal best for an individual salesperson, follow these precise steps. This method uses custom formulas within the conditional formatting rules.

First, understand the purpose of conditional formatting: it automatically applies formatting (like colors or fonts) to cells based on specified conditions or rules. When a cell's value meets the rule, the formatting is applied; otherwise, it is not. This process is dynamic, meaning if the data changes, the formatting updates automatically.

Assume your sales data is organized in a spreadsheet with columns for 'Salesperson' (e.g., Column A), 'Date of Sale' (e.g., Column B), and 'Sales Amount' (e.g., Column C). We will apply the conditional formatting to the 'Sales Amount' column (Column C).

Step 1: Define the target range for conditional formatting. Select all the cells in the 'Sales Amount' column that contain sales figures, for example, C2 to C100. The formulas will be written from the perspective of the first cell in your selected range, which is C2 in this example.

Step 2: Construct the formula for the first condition: 'above the monthly average'. The monthly average is the arithmetic mean of all sales figures within a specific month or overall dataset, depending on what constitutes 'monthly' for your data. To calculate the average of all sales amounts in Column C (e.g., from C2 to C100), you use the AVERAGE function. The formula will be `C2 > AVERAGE($C$2:$C$100)`. Here, `C2` refers to the current cell being evaluated by the conditional formatting rule, and `$C$2:$C$100` is an absolute reference to the entire range of sales amounts. Absolute references (indicated by dollar signs, `$`) ensure that the range for the average calculation remains fixed no matter which cell in your selected range the conditional formatting rule is evaluating. Without the dollar signs, the range would shift, leading to incorrect average calculations for subsequent cells.

Step 3: Construct the formula for the second condition: 'new personal best for an individual salesperson'. A new personal best means the current sales figure for a specific salesperson is strictly greater than all previous sales figures achieved by that same salesperson up to the current date. To achieve this, we need to compare the current sales amount with the maximum sales amount for that salesperson from all prior sales dates. The formula for cell C2 would be `C2 > MAX(IF(($A$2:$A$100=$A2)*($B$2:$B$100<$B2), $C$2:$C$100))`. Let's break this down:
`$A$2:$A$100` is the absolute reference to the full range of salesperson names.
`$A2` is the relative reference to the salesperson name in the current row being evaluated by the rule. This relative reference ensures the formula correctly identifies the salesperson for each row.
`$B$2:$B$100` is the absolute reference to the full range of sale dates.
`$B2` is the relative reference to the date in the current row.
`$C$2:$C$100` is the absolute reference to the full range of sales amounts.
`($A$2:$A$100=$A2)` creates an array of TRUE/FALSE values, indicating which rows belong to the current salesperson.
`($B$2:$B$100<$B2)` creates another array of TRUE/FALSE values, indicating which rows have a date earlier than the current row's date.
Multiplying these two arrays `(...) (...)` acts as an AND condition; it results in an array of 1s (TRUE) or 0s (FALSE) where both conditions are met (same salesperson AND earlier date). This is a common method for handling multiple criteria in array-like formulas within conditional formatting.
`IF(..., $C$2:$C$100)` uses these 1s and 0s to select the corresponding sales amounts from `$C$2:$C$100` for sales made by the same person on earlier dates. Sales not matching the criteria will result in FALSE, which the MAX function typically ignores or treats as zero, depending on the spreadsheet software's implementation, allowing it to correctly find the maximum of only the relevant prior sales.
`MAX(...)` then calculates the highest sales figure among all previous sales for that specific salesperson.
`C2 > MAX(...)` checks if the current sales amount (`C2`) is strictly greater than that highest previous sales figure, thereby qualifying it as a new personal best.

Step 4: Combine both conditions using the AND logical function. The AND function returns TRUE only if all its arguments are TRUE. The combined formula will be `AND(C2 > AVERAGE($C$2:$C$100), C2 > MAX(IF(($A$2:$A$100=$A2)*($B$2:$B$100<$B2), $C$2:$C$100)))`. This formula is what you will input into the conditional formatting rule.

Step 5: Apply the conditional formatting rule in your spreadsheet software (e.g., Microsoft Excel, Google Sheets, LibreOffice Calc). With your target range (e.g., C2:C100) still selected, navigate to the 'Conditional Formatting' option (usually found in the Home tab). Select 'New Rule', then 'Use a formula to determine which cells to format'. Paste the complete combined formula into the formula box. Finally, click the 'Format' button to choose the desired highlighting (e.g., a specific fill color or font style) that will be applied to cells meeting both criteria. Confirm by clicking 'OK' or 'Apply'.