Inter-related cells

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

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

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

Originally Posted by Richard Buttrey

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

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

Works perfectly! Thanks mate - this should save me a nice bit of time.

Do you by any chance know how I could use this on my Palm? It's a Z22 and it came with a database software called MobileDB. I've just imported the Excel file (converted to a .csv file) into it, but it doesn't seem to have the functions to be able to make the calculations, so I guess I would need another program to do it.

Thanks again

Hi,

Thanks for the feedback. I don't know the software you mention but would be surprised if it did have the equivalent of the XL Solver functionality. If it supports some sort of programming, i.e. the equivalent of Visual Basic with Excel, then it might be possible to write some dedicated code.

That was the approach I first thought of using before I realised that Solver seems to offer exactly what you want.

Please mark the thread 'Solved' by going to your original, and if appropriate rate the responses by clicking on the 'scales' icon on the post.

Rgds

I'll look for some Palm software or maybe even another device with the capabilites, as I often make the calculations manually when not at the computer.

Just one more question on the spreadsheet you sent me; can you explain the process of adding another food item to the database (I'm not sure how you did it)?

Thanks

Hi,

Yes, all you need to do is keep adding new food item descriptions in column A, with their details in B:E.

If you look in the formulae in B2:E2 you'll see that these just address A8:E16 at the moment, so if you expand your database past row 16 you'll need to modify the range covered in the formula. You could create a dynamic range name which automatically adjusts as necessary, but if you've got a table which is unlikely to change, it's probably best just to change the formula range to say A8:E100, and leave it - unless of course your food items go past row 100.

Rgds

Ok, after removing the '$' characters from the formulas (what are they for?) I can now see how it works so I can replicate it for further items. But I'm unsure why the formulas need to define a table range (e.g. A8:E16).

B2 for example (=F2*VLOOKUP(A2,A8:E16,2)/100): 'F2' is to reference the gram value, 'A8' and '2' are to reference the total calories value; I'm unsure why 'A2' and 'E16' are needed in the formula.

I should have no problems creating the database now anyway, but it might be useful to know a little more about how it works.

Cheers again

Originally Posted by sk1

Ok, after removing the '$' characters from the formulas (what are they for?) I can now see how it works so I can replicate it for further items. But I'm unsure why the formulas need to define a table range (e.g. A8:E16).

B2 for example (=F2*VLOOKUP(A2,A8:E16,2)/100): 'F2' is to reference the gram value, 'A8' and '2' are to reference the total calories value; I'm unsure why 'A2' and 'E16' are needed in the formula.

I should have no problems creating the database now anyway, but it might be useful to know a little more about how it works.

Cheers again

Hi The $ characters are there to avoid problems if you were to copy the formula elsewhere. Without them, as you now have it, and if you were to copy the formula to say row 3 you'd find that they would no longer address the same table. As it happens and provided you never copy it elsewhere you'll be OK. The $ signs are there to make cell references Absolute references rather than Relative references. It's worth examining the difference between the two a little more since they will catch you out in other circumstances.

I guess you've moved the word 'Oats' to A2. No problem with that because the formula has been changed to use A2. The VLOOKUP() function uses whatever is in A2 to identify the correct row to use in the Table of Calorie values.

Actually I realise there's a potential problem since the VLOOKUP() element in the formulae don't contain the 4th element "False". Please substitute the following

Please Login or Register  to view this content.

HTH

Originally Posted by Richard Buttrey

Hi The $ characters are there to avoid problems if you were to copy the formula elsewhere. Without them, as you now have it, and if you were to copy the formula to say row 3 you'd find that they would no longer address the same table. As it happens and provided you never copy it elsewhere you'll be OK. The $ signs are there to make cell references Absolute references rather than Relative references. It's worth examining the difference between the two a little more since they will catch you out in other circumstances.

I guess you've moved the word 'Oats' to A2. No problem with that because the formula has been changed to use A2. The VLOOKUP() function uses whatever is in A2 to identify the correct row to use in the Table of Calorie values.

Ok, thanks - it's useful to know.

Originally Posted by Richard Buttrey

Actually I realise there's a potential problem since the VLOOKUP() element in the formulae don't contain the 4th element "False". Please substitute the following

Please Login or Register  to view this content.

What is the purpose of the element 'FALSE'? I've made the substitutions but D2 is now labled 'FASLE'.

Hi

The 'False' element - or sometimes the zero character (same thing), is used to make sure the VLOOKUP uses an exact match. Without it a VLOOKUP will find the nearest value to the value being looked up. It's complicated by the fact that with an unsorted list and without a 'False' you usually get a value you don't expect. Unless you have good reasons not to include False I suggest you treat that as a default setting.

Put the False in D2 as well.

HTH

Originally Posted by Richard Buttrey

Hi

The 'False' element - or sometimes the zero character (same thing), is used to make sure the VLOOKUP uses an exact match. Without it a VLOOKUP will find the nearest value to the value being looked up. It's complicated by the fact that with an unsorted list and without a 'False' you usually get a value you don't expect. Unless you have good reasons not to include False I suggest you treat that as a default setting.

Got that, thanks.

Originally Posted by Richard Buttrey

Put the False in D2 as well.

I've copied and pasted the formulaes and they all work except for D2, which just says FALSE now; I'm not sure what the problem is. I've attached the file if you want to check it out.

Hi,

You seem to have doubled/trebled up on the formulae functions. Just substitute the following.

Please Login or Register  to view this content.

Rgds

Originally Posted by Richard Buttrey

Hi,

You seem to have doubled/trebled up on the formulae functions.

Sorry mate, you've lost me there. When I copy and paste the formulae for B2-E2, D2 becomes FALSE. Now I have copied and pasted the formula you just gave me into D2, and it now says #N/A .

Hi,

You seem to have simply added the code I gave you last time to what is already in the cell - as you presumably did on the previous occasions - hence the formula growing longer and longer.

Just delete completely whatever is in D2 and re-enter the small formula I gave you.

Rgds

Originally Posted by Richard Buttrey

Hi,

You seem to have simply added the code I gave you last time to what is already in the cell - as you presumably did on the previous occasions - hence the formula growing longer and longer.

Ok, I see what's happened. I was highlighting the formula and then pasting the new one to replace it, but unlike a text document, it adds it instead of replacing it. But it looks as though it's replacing it - only after I widened the 'Formual Bar' was I able to see the older formulae.

The #N/A problem was because I moved 'Oats' from A1 to A2, so I updated the formula for D2 and it's working now.

Thanks again Richard - you've been a great help and it's much appreciated. I've bookmarked the site and will be back whenever I need any Excel help.