Akari uses a worksheet to track inventory. The worksheet requires Akari to input the quantity of each item in stock and the total number sold. The worksheet is set up with a formula to determine the quantity of items left in stock. The spreadsheet might look something like this table:

Question

Akari’s Inventory
Item Total in Stock Total Sold Total Left
Item 1
Item 2
Item 3
Akari wants to be notified when any item has fewer than 50 units remaining in the Total Left column. Describe how he can use conditional operations to meet his goal.

Answer 1

To set up a notification system for when any item has fewer than 50 units remaining in the "Total Left" column using conditional operations in a spreadsheet, Akari can follow these steps:

1. Setting Up the Spreadsheet: Ensure that the worksheet is properly set up with the following columns:

   • Column A: Item
   • Column B: Total in Stock
   • Column C: Total Sold
   • Column D: Total Left (with a formula to calculate it)

   The formula for calculating "Total Left" in Column D (for example, in cell D2) would be:

   =B2 - C2
   

   This formula subtracts the "Total Sold" from the "Total in Stock" to give the current quantity left for each item.

2. Using Conditional Formatting: Akari can use conditional formatting to highlight any items that fall below the threshold of 50 units left in stock. Here’s how to set it up:

   • Select the Range for Conditional Formatting: Highlight the cells in the "Total Left" column (for example, D2:D4 for Item 1 to Item 3).

   • Access Conditional Formatting: On the toolbar, go to the "Format" menu and select "Conditional formatting."

   • Set the Conditional Format Rule: In the conditional formatting pane, create a new rule:

     • Choose "Custom formula is" or an equivalent option depending on the software (like Google Sheets or Excel).
     • Enter the formula to check for values less than 50:

       =D2 < 50
       

       Make sure to adjust the row number accordingly if the first item is in a row other than 2.

   • Choose the Formatting Style: Select a formatting style (like a fill color, text color, or bold text) that will alert Akari visually when the condition is met.

   • Apply the Rule: Click "Done" or "Apply" to save the conditional formatting rule.

3. Notification System: In addition to conditional formatting, some spreadsheet software offers notification features that send alerts or emails when specific criteria are met. If Akari is using Google Sheets, for example:

   • He can set up an alert for changes:
     • Go to the "Tools" menu and select "Notification rules."
     • Set rules to get notified when an edit is made (e.g., when the quantity left drops below 50).

By following these steps, Akari will have a system in place to visually and potentially be alerted when any item in stock falls below 50 units. This allows him to take timely actions for restocking or managing inventory.