Share via

Preparing a excel for costing of cakes

Anonymous
2020-08-19T18:01:52+00:00

Hi everyone... 

I am trying to make a excel documents for costing my products(baked Good). 

Where in some cases I have to convert the recipes from cups to grams. I have a separate sheet with table of different conversions of different ingredients. How ever i do not want to input that Values manually. I was hoping you all can help me find a way so that the excel gets the value from the table. 

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-08-21T15:58:55+00:00

    Dear,

    Always welcome.

    If you have any more questions, feel free to ask.

    Thanks

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2020-08-20T13:02:11+00:00

    https://1drv.ms/x/s!AlzK-7ohrp7CgX53mJl0mVv0TzZp?e=ZISnw5

    Thank you, that makes it a lot easier.

    The first step to automate all that is to create a list for all ingredients and there costs. I have copied your Purchase sheet, created an Ingredient column from Item and Type (and removed duplicates) to create a unique "Typed Ingredient" that you can choose later in your receipt. The important column in this sheet is "Price per Gram". Check the calculation of that column.

    The next step is a conversation table to convert Cup to Tsp to Oz to Gram to ... we need just this (Sheet Convert):

    Alright, in the receipt sheet you can pick an Ingredient in column A and get the "Cost per Gram" using a VLOOKUP formula.

    After that you have to specify the Amount of a Unit in two separate cells ("1 Cup" and similar contents in one cell is to difficult to parse). The columns Grams and Cost are automatically calculated.

    Here is your modified sample file:

    https://www.dropbox.com/s/uzgt28u9fqwfzyl/8252149c-8377-4406-9e81-c0b20da23eb3.xlsx?dl=1

    Any questions?

    EDIT: Maybe the conversation (Cup to Gram) is too coarse, I can imagine that we use a "kind" of each ingredient (solid liquid powdery diced choped) to have a slightly finer, adjusted conversion.

    Andreas.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2020-08-19T19:21:56+00:00

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-08-19T18:37:05+00:00

    Hi, I am Jonathan, Independent Advisor here to work with you on this issue.

    You may use different functions to do what you wanted but I advise you to use the vlookup function as this is the most common one.

    You may refer to below link on how to properly use the said function.

    https://support.microsoft.com/en-us/office/vloo...

    Hope this helps you.

    Jonathan

    Was this answer helpful?

    0 comments
  5. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2020-08-19T18:11:59+00:00

    We need to see your file. Please upload your file on OneDrive and post the download link here.

    Andreas.

    Was this answer helpful?

    0 comments