Inter-related cells

[sk1]

Hi, I'm just now trying to use excel for the first time to create a database and I haven't been able to find the information on how to get the cells to do what I need. Here's what I'm trying to do:

I enter a food item in A1 (e.g. Oats).
B1=F1*3.6 (calculates the total calories)
C1=F1*3.6*0.67 (calculates carb calories)
D1=F1*3.6*0.12 (calculates protein calories)
E1=F1*3.6*0.21 (calculates fat calories)
I enter the gram amount in F1

This works fine. But what I would like to also do is to be able to enter a calorie amount in B,C,D, or E and for the other cells to calculate the rest, including the gram amount. Would really appreciate any help on this.

Thanks in advance

[Richard Buttrey]

Hi,

Your request isn't clear - to me at least. You say that B1 calculates the total calories, but then say you want to enter a calorie amount. That's contradictory. Then you say you want to enter a calorie amount in either B,C,D or E. Is that in a particular row number, and what determines your choice?

It would be better if you could upload a copy of your workbook, showing your data, and in addition manually adding the results you expect to see and explaining in a note how you calculate those results. It's always easier to help if we can see your questions in context.

Rgds

[sk1]

Hi Richard and thanks for the reply. I'll try and do a better job of explaining it using a detailed example.

First, I should explain what I'm using it for. I allocate a number of carb, protein, and fat calories individually for each meal of the day, and then I create a meal within those amounts from various foods.

Example: I decide I need a meal of oats with 120 carb calories, 80 protein calories, and minimal fat calories. Oats are 360 calories per 100g, and I have
calculated that they are composed of 67% carbs, 12% protein, and 21% fat. So, here's how I calculate it:

Step 1: (120/67)*100 = 179
So, if I wanted a meal of oats with 120 carb calories, the total calories from that portion of oats would be 179 (the other 59 calories being the protein and fat content of the oats).

Step 2: Now I need to calculate how many grams of oats that would be:
179/3.6 = 50 (amount in grams)

Step 3: Next, I need to know the individual protein and carb calories included:
179*.12 = 21 (calories from protein)
179*.21 = 38 (calories from fat)
So, the 50g portion of oats would be 120-21-38 (carb-protein-fat).

Last Step: Since I need a total of 80 calories from protein, I then calculate and add 59 calories of protein powder to the oats, and now my meal is a total of 120-80-38.

I keep a record of many foods for which I have listed their calories p/100g, and their percentage of calories from carb-protein-fat (e.g. Oats 360 67-12-21). Using this information, I can work out how many grams I need of a certain food to fulfill my carb-protein-fat calories allocations for any given meal, or I can work out how many carb-protein-fat calories a specific gram amount will give me.

So using excel, 'A1' is labeled 'Oats', and cells B1-F1 are used to calculate 'Total Calories', 'Carb Calories', 'Protein Calories', 'Fat Calories', and 'Grams' respectively. Using the previous example, lets say I didn't have any protein powder and I needed to get the 80 protein calories from the oats. I can enter 80 into 'D1' , and 'F1' (the 'Grams' cell) would calculate 185, and the amounts for the other cells would also be calculated automatically.

So this is basically what I need; to be able to enter an amount into any one of those cells and have the others calculated automatically.

Let me know if anything is unclear and I'll try and clarify.

Cheers

[Richard Buttrey]

Hi,

You're attempting to use a cell for both a formula and a value. Once you enter a constant value you lose the formula and vice versa.

I think what you probably want to use is the Solver Tool. In the attached workbook I've included a picture of what this looks like.

From the menu choose Tools Solver. Now in this example suppose you want to set the D2 cell to a value of 80, enter d2 as the target cell, set the Value to 80 and enter F2 in the 'By Changing Cells' box, then click the Solve button and accept the default 'Keep Solver Solution'. Now you should have values of 666.7, 446.7, 80 & 140

Use this technique to solve for other required values by changing F2.

I've also included the start of a table and you can see the formulae in row 2 use the values from this table. For instance change A1 to "other' and see that it uses the values from row 9.

HTH

[sk1]

Now you should have values of 666.7, 446.7, 80 & 140

Just to clarify, those values are correct, and my aim is to have the choice to enter any given value in any of those cells and for the others to calculate accordingly each time.

'Oats' should be in A2 (my explanation on how the cells should be setup was off there), but that aside your spreadsheet is setup how I want it.

However, it says I need Quicktime and a decompressor to see the picture, and I am having trouble finding the Tools Solver (Excel 2007).

Cheers

[Richard Buttrey]

Ah, didn't realise this was 2007.

Forget about the picture unless you really have problems.

First check to see whether the Solver Add in is loaded. Select the Data menu tab from the ribbon and look under the Analysis section. If you don't see it you'll need to load it.

From the Office Button click the Excel options. Now select 'Add-Ins' and Go.
In the Add-ins available click Browse to locate the Solver Add-In. If you're told that it's not currently installed click Yes to install it.

Then you should find it under the Data Tab as explained above.

HTH

HTH