Google Sheets Ingredient Cost Formulas
Budget: $30 – $250 AUD
I’m working in Google Sheets and already have a template with tabs: “INGREDIENTS” (master price list) and “COST BREAKDOWN” (recipe calculator). What I need is the set of formulas, links, and light conditional formatting that will turn this into a point-and-go costing tool:
• Every time I enter an ingredient name and a quantity in COST BREAKDOWN, the sheet should fetch the matching unit price from the INGREDIENTS tab, multiply it by the quantity, and drop the extended cost into the same row.
• The sheet then sums those row costs into a “Full Batch Cost” figure.
• Item Cost is simply Full Batch Cost divided by the batch weight I type in (e.g., 30 kg).
• A Profit % column shows Sell Price ÷ Item Cost − 1.
• Conditional formatting: Profit % >= 50 % shows green, anything lower shows red.
All calculations are exactly as described—no extras—so the deliverable is a clean, documented formula set (ARRAYFORMULA or VLOOKUP/INDEX-MATCH, whichever is most efficient), together with the conditional-formatting rule applied, leaving the sheet ready for immediate use.
• Every time I enter an ingredient name and a quantity in COST BREAKDOWN, the sheet should fetch the matching unit price from the INGREDIENTS tab, multiply it by the quantity, and drop the extended cost into the same row.
• The sheet then sums those row costs into a “Full Batch Cost” figure.
• Item Cost is simply Full Batch Cost divided by the batch weight I type in (e.g., 30 kg).
• A Profit % column shows Sell Price ÷ Item Cost − 1.
• Conditional formatting: Profit % >= 50 % shows green, anything lower shows red.
All calculations are exactly as described—no extras—so the deliverable is a clean, documented formula set (ARRAYFORMULA or VLOOKUP/INDEX-MATCH, whichever is most efficient), together with the conditional-formatting rule applied, leaving the sheet ready for immediate use.