need to create a macro to replace manual formulas with errors

[mrmvr]

Hi hope somebody can help me with this project basically the excel sheet is needed to work out the preparation costs of various products within our kitchen, during preparation the meat will go through various stages from the original unpacked weight of the meat being delivered, losses on defrost, cutting, washing and cooking then gains on marination, what i have attempted to do as i cant write macros is to manually set up all the formulas on the worksheet starting with ingredient and then meat cost per kg then the losses but on this it goes wrong the formula in the end prepared weight (h) column is not required as this only adds the weight of the marinade to the finished prepared meat, but i have already incorporated this adjustment of weight into the figures given in the columns (f) + (g) basically (g) is the finished weight of the product i want to base the costing on then use the recipe amounts per the weight used columns (L) to (AL) taking from the per kg prices quoted in column (A) the problem im getting is that i need to take into account the loss or gain of weight from the original price of the meat per kg then together with the cost of ingredient work out a final cost per kg for finished product.
I know its confusing and i know i have made errors setting this up which after many hrs of trying to fix it im finally asking for help!!! http://www.excelforum.com/images/smilies/confused.gif

[6StringJazzer]

Welcome to the Forum!

Although I kind of understand what your spreadsheet does from looking at it, I do not understand the problem you are having that caused you to start this thread. The way your question is written doesn't help--it's one long sentence.

I don't think you need macros for anything here. A clearer explanation would help. My suggestion is to start with the result you want, then describe the method you would use to get that result if you did it on pencil and paper. Then we can talk about how to do it in Excel.

[mrmvr]

Hi Thanks for taking the time to look at it for me, let me try and break it down a little i have uploaded another version of the same info spreadsheet this time i have put in 4 new columns highlighted in yellow the 2 figures im interested to get are the ones in the yellow columns highlighted in red, however there seems to now be a problem with the formula in column (F), basically i need this figure to represent the total cost of ingredients used per prepared product then as you can see from the formulas this figure is then used to work out the other columns, the problem being that the figures it is giving are far from accurate and if for example i add up the cost manually for the first product sliced beef i get £8.787 but it gives me £8.85 and as for (f5) i got £9.24 it gives £84.50 cant figure out where im going wrong.

[event21]

Hi -

Look into your formula

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

Regards,
Event

[6StringJazzer]

You have one major problem in your design that causes your formulas to be overly complicated, as can be seen in the error pointed out by event21. Please see the attachment below for a redesign of your data layout.

Prices for individual ingredients were in column B. I have moved them to be in row 2, in parallel with the ingredient names. This makes it much easier if you have to change ingredients or prices. This allows your formula in column G to be much simpler, as you can see. I suggest you review Excel Help for the SUMPRODUCT function that I used to simplify this formula.

Your formula in column O used the same formula as column F. Instead of repeating the entire formula, it now simply refers to the value already calculated in column F.

It appears that column N (now L in my design) is entered manually to refer to the corresponding stock from which the prepared product is made. This is error prone. To make it easier for the user, I have added a column to select the stock from a dropdown, and the price for the stock is looked up automatically. This depends on two Excel techniques that you might not be familiar with:

1. Data validation. Excel allows you to specify rules for what data is allowed to be in a cell. One type of rule is called the Validation List. To use this, you specify a range of cells that contain that allowed values. There is a Dropdown option, which shows those allowed values in a dropdown list, easy for the user to select.

2. Dynamic named range. A named range in Excel is way of giving a name to a range of cells. This has several benefits that I won't get into right now, but an example of how this works is that if you have a formula like this

=SUM($A$1:$B$20)

you can assign a name, let's say MyData, to the range $A$1:$B$20. Then the formula can be written as

=SUM(MyData)

A more complex type of named range includes the count of how many cells are actually being used in the range. I used the following formula and named it ProductStockList

Please Login or Register  to view this content.

This looks scary but in English it says this:

"Start from cell A4. Count how many non-blank cells are in A4:A41, and go that many cells down from A4."

To get this to work properly I had to remove the x's in column C that didn't appear to have any meaning.

There are a few other things I would clean up but I didn't want to throw too much at you at once. And I would really suggest that you don't write your posts as one long sentence.