Hey everyone, I’m working on a Google Sheets assignment and need some help. The task is to figure out what portion of our products are no longer being made. I have to use a formula in Google Sheets to show this.
I started by counting the discontinued items using =COUNTIF(K3:K79, "1") in column K. But now I’m stuck on how to turn this into a proportion.
Is my first step right? And what’s the best way to show this as a ratio of all products? I’m pretty new to formulas, so any tips would be great!
I’ve dealt with similar inventory tracking issues before, and your approach is solid. To expand on the solution, you might want to consider creating a named range for your product list. This makes formulas more readable and easier to update if your product range changes.
Where ‘ProductRange’ is your named range. This gives you a decimal ratio. For clearer reporting, you could format the cell as a percentage or add a text description:
This approach provides a clear, labeled result that’s easy for stakeholders to understand. Remember to double-check your data consistency, especially the criteria for marking items as discontinued.
Your approach is correct for counting discontinued items. To calculate the ratio, you’ll need to divide that count by the total number of products. Here’s a formula that does both in one step:
=COUNTIF(K3:K79, “1”) / COUNTA(K3:K79)
This will give you a decimal result. For a percentage, you can format the cell or multiply by 100 in the formula.
A tip: ensure your data is clean. Check for any blank cells or inconsistent entries in column K that might skew your results. Also, consider adding a label to your result cell for clarity, like “Discontinued Product Ratio:” next to the formula cell.