Need an expression that's larger than my skill

[Denise]

St M(Lean)/M(Med.)/M(High) Veg Frt Mlk Fat

GOALS CALS PERCENT
CAR: 1,000 50.0%
PRO: 400 20.0%
FAT: 600 30.0%
Total: 2,000 100%

1 exchange 80 45 75 100 25 60 100 45
Calories

Carbohydrates combine Starch [65 of the 80 calories], Veg [20 of the 25
calories], Fruit [all 60 calories], Milk [38 of the 100 calories], (B,F,G,H)

Protein combines Starch [15 of the 80 calories], Meat [40% of the total
meat calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk [27 of
the 100 calories] & Fruit (C-E & H)

Fat combines Meat [60% of the total meat calories are Fat (C10-E10)] & Milk
[35 of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

The original version of this worksheet is laid out so that a client
or our staff Dietician can choose the number exchanges in day (Starch-Fat)
and the sheet will calculate the calories and the percentage for
Carbs./Protein & Fat.

Our dietician has asked me to make it work inversely so that she can insert
total calories for the client - she wants the sheet to then calculate the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

I'm not skilled enough to express these formulas or clear as to whether
there are too many variables for it to work.

Thank you for your help!

 

[JoeU2004]

Please forgive my naivete, but either I am having a hard time understanding
the information in your posting, or what you want cannot be done.

First, bear in mind that your references to columns (cells, in some cases)
are unclear because of the mangled format of posted data (not your fault).
I suggest that you present the data another way. The following is my
interpretation.

As I understand your data, 1 exchange is 530 cal broken down as follows:

Starch: 80 cal; Carb=65, Prot=15, Fat=0
Meat:
Lean: 45 cal; Carb=0, Prot=18, Fat=27 [*]
Med: 75 cal; Carb=0, Prot=30, Fat=45 [*]
High: 100 cal; Carb=0, Prot=40, Fat=60 [*]
Veg: 25 cal; Carb=20, Prot=5, Fat=0
Fruit: 60 cal; Carb=60, Prot=0, Fat=0
Milk: 100 cal; Carb=38, Prot=27, Fat=35
Fat: 45 cal; Carb=0, Prot=0, Fat=45

If I understand that correctly, then for 1 exchange: Carb=183, Prot=135,
Fat=212 cal.

By definition, Carb is 34.5% (183/530), Prot is 25.5% (35/530), and Fat is
40% (212/530).

[*] Since the meat categories (lean, med, high) usually refer to fat
content, it seems incongruous to assume that the fat content is 60% of each
category. You did say 60% of __total__ meat calories. But you offered no
data as how that is distributed over lean, med and high fat content. That
does not affect the summary calculations above. However, it might affect
solution to the speculated problem below ("is the dietician asking").
Please fill in this detail (fat content of __each__ meat category) when you
post a follow-up.

Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then calculate the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

It cannot be broken by 50-20-30%. It is 34.5-25.5-40% by defintion (?).

Is the dietician asking: find some combination of starch, meats, veg,
fruit, milk and fat that sums to 530 cal, but with a breakdown of 50-20-30%?

First, I don't know if both constraints can be met, based on the data above.
But if it can be, it probably cannot be determined by a few formulas. You
might be able to use Solver; but I doubt it. I would write a macro.

But I suspect there is a problem with my interpretation. You can see how I
interpreted it. Please correct my misunderstanding, if any.


----- original message -----

St M(Lean)/M(Med.)/M(High) Veg Frt Mlk Fat

GOALS CALS PERCENT
CAR: 1,000 50.0%
PRO: 400 20.0%
FAT: 600 30.0%
Total: 2,000 100%

1 exchange 80 45 75 100 25 60 100 45
Calories

Carbohydrates combine Starch [65 of the 80 calories], Veg [20 of the 25
calories], Fruit [all 60 calories], Milk [38 of the 100 calories],
(B,F,G,H)

Protein combines Starch [15 of the 80 calories], Meat [40% of the total
meat calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk [27
of
the 100 calories] & Fruit (C-E & H)

Fat combines Meat [60% of the total meat calories are Fat (C10-E10)] &
Milk
[35 of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

The original version of this worksheet is laid out so that a
client
or our staff Dietician can choose the number exchanges in day (Starch-Fat)
and the sheet will calculate the calories and the percentage for
Carbs./Protein & Fat.

Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then calculate the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

I'm not skilled enough to express these formulas or clear as to whether
there are too many variables for it to work.

Thank you for your help!

 

[Denise]

Thanks for taking the time.

1 exchange of Starch = 80 calories
1 exchange of Meat- Lean = 45 calories
1 exchange of Meat - Med. = 75 calories
1 exchange of Meat - High = 100 calories
1 exchange of Veg. = 25 calories
1 exchange of Fruit = 60 calories
1 exchange of Milk = 100 calories
1 exchange of Fat = 45 calories

Carbohydrates - 50% of calories
combines Starch [65 of the 80 calories], Veg [20 of the 25 calories], Fruit
[all 60 calories], Milk [38 of the 100 calories], (B,F,G,H)

Protein - 20% of calories
combines Starch [15 of the 80 calories], Meat [40% of the total meat
calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk [27 of the
100 calories] (C-E & H)

Fat - 30% of calories
combines Meat [60% of the total meat calories are Fat (C10-E10)] & Milk [35
of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

So....if, in the current worksheet, I entered 7 starches, 4 fruits, 3 milks,
4 vegetables, 5 fats, 1 high fat meat, 1 med. fat meat and 5 lean meats it
would calculate 1825 calories and the percentages of Carbs/Protein and Fats.

What our RD wants is to anchor the percentages (always 50/20/30) and by
entering the total calories she wants the client to have - for the
spreadsheet to calculate the exchanges.

Does that make sense?

Please forgive my naivete, but either I am having a hard time understanding
the information in your posting, or what you want cannot be done.

First, bear in mind that your references to columns (cells, in some cases)
are unclear because of the mangled format of posted data (not your fault).
I suggest that you present the data another way. The following is my
interpretation.

As I understand your data, 1 exchange is 530 cal broken down as follows:

Starch: 80 cal; Carb=65, Prot=15, Fat=0
Meat:
Lean: 45 cal; Carb=0, Prot=18, Fat=27 [*]
Med: 75 cal; Carb=0, Prot=30, Fat=45 [*]
High: 100 cal; Carb=0, Prot=40, Fat=60 [*]
Veg: 25 cal; Carb=20, Prot=5, Fat=0
Fruit: 60 cal; Carb=60, Prot=0, Fat=0
Milk: 100 cal; Carb=38, Prot=27, Fat=35
Fat: 45 cal; Carb=0, Prot=0, Fat=45

If I understand that correctly, then for 1 exchange: Carb=183, Prot=135,
Fat=212 cal.

By definition, Carb is 34.5% (183/530), Prot is 25.5% (35/530), and Fat is
40% (212/530).

[*] Since the meat categories (lean, med, high) usually refer to fat
content, it seems incongruous to assume that the fat content is 60% of each
category. You did say 60% of __total__ meat calories. But you offered no
data as how that is distributed over lean, med and high fat content. That
does not affect the summary calculations above. However, it might affect
solution to the speculated problem below ("is the dietician asking").
Please fill in this detail (fat content of __each__ meat category) when you
post a follow-up.

Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then calculate the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

It cannot be broken by 50-20-30%. It is 34.5-25.5-40% by defintion (?).

Is the dietician asking: find some combination of starch, meats, veg,
fruit, milk and fat that sums to 530 cal, but with a breakdown of 50-20-30%?

First, I don't know if both constraints can be met, based on the data above.
But if it can be, it probably cannot be determined by a few formulas. You
might be able to use Solver; but I doubt it. I would write a macro.

But I suspect there is a problem with my interpretation. You can see how I
interpreted it. Please correct my misunderstanding, if any.


----- original message -----

St M(Lean)/M(Med.)/M(High) Veg Frt Mlk Fat

GOALS CALS PERCENT
CAR: 1,000 50.0%
PRO: 400 20.0%
FAT: 600 30.0%
Total: 2,000 100%

1 exchange 80 45 75 100 25 60 100 45
Calories

Carbohydrates combine Starch [65 of the 80 calories], Veg [20 of the 25
calories], Fruit [all 60 calories], Milk [38 of the 100 calories],
(B,F,G,H)

Protein combines Starch [15 of the 80 calories], Meat [40% of the total
meat calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk [27
of
the 100 calories] & Fruit (C-E & H)

Fat combines Meat [60% of the total meat calories are Fat (C10-E10)] &
Milk
[35 of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

The original version of this worksheet is laid out so that a
client
or our staff Dietician can choose the number exchanges in day (Starch-Fat)
and the sheet will calculate the calories and the percentage for
Carbs./Protein & Fat.

Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then calculate the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

I'm not skilled enough to express these formulas or clear as to whether
there are too many variables for it to work.

Thank you for your help!

 

[JoeU2004]

Does that make sense?

No, because the sum of the Carb calories is 183 (65 starch, 20 veg, 60
fruit, 38 milk), Prot is 135, and Fat is 212, for a total of 530. So Carb
is 34.5% (183/530), not 50%.

However, I do have another spin on all this that does make the calculations
doable. I cannot get into it now because I need to go some place.

In the meantime, you might cogitate on the disparity I mention above, and
perhaps you can post a further clarification, if needed.

PS: I don't feel that any clarification of the numbers is needed. In a
nutshell, I think they are intended to demonstrate how carbs, for example,
are distributed across the exchange groups (starch, veg, etc).


----- original message -----

Thanks for taking the time.

1 exchange of Starch = 80 calories
1 exchange of Meat- Lean = 45 calories
1 exchange of Meat - Med. = 75 calories
1 exchange of Meat - High = 100 calories
1 exchange of Veg. = 25 calories
1 exchange of Fruit = 60 calories
1 exchange of Milk = 100 calories
1 exchange of Fat = 45 calories

Carbohydrates - 50% of calories
combines Starch [65 of the 80 calories], Veg [20 of the 25 calories],
Fruit
[all 60 calories], Milk [38 of the 100 calories], (B,F,G,H)

Protein - 20% of calories
combines Starch [15 of the 80 calories], Meat [40% of the total meat
calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk [27 of
the
100 calories] (C-E & H)

Fat - 30% of calories
combines Meat [60% of the total meat calories are Fat (C10-E10)] & Milk
[35
of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

So....if, in the current worksheet, I entered 7 starches, 4 fruits, 3
milks,
4 vegetables, 5 fats, 1 high fat meat, 1 med. fat meat and 5 lean meats it
would calculate 1825 calories and the percentages of Carbs/Protein and
Fats.

What our RD wants is to anchor the percentages (always 50/20/30) and by
entering the total calories she wants the client to have - for the
spreadsheet to calculate the exchanges.

Does that make sense?

Please forgive my naivete, but either I am having a hard time
understanding
the information in your posting, or what you want cannot be done.

First, bear in mind that your references to columns (cells, in some
cases)
are unclear because of the mangled format of posted data (not your
fault).
I suggest that you present the data another way. The following is my
interpretation.

As I understand your data, 1 exchange is 530 cal broken down as follows:

Starch: 80 cal; Carb=65, Prot=15, Fat=0
Meat:
Lean: 45 cal; Carb=0, Prot=18, Fat=27 [*]
Med: 75 cal; Carb=0, Prot=30, Fat=45 [*]
High: 100 cal; Carb=0, Prot=40, Fat=60 [*]
Veg: 25 cal; Carb=20, Prot=5, Fat=0
Fruit: 60 cal; Carb=60, Prot=0, Fat=0
Milk: 100 cal; Carb=38, Prot=27, Fat=35
Fat: 45 cal; Carb=0, Prot=0, Fat=45

If I understand that correctly, then for 1 exchange: Carb=183, Prot=135,
Fat=212 cal.

By definition, Carb is 34.5% (183/530), Prot is 25.5% (35/530), and Fat
is
40% (212/530).

[*] Since the meat categories (lean, med, high) usually refer to fat
content, it seems incongruous to assume that the fat content is 60% of
each
category. You did say 60% of __total__ meat calories. But you offered
no
data as how that is distributed over lean, med and high fat content.
That
does not affect the summary calculations above. However, it might affect
solution to the speculated problem below ("is the dietician asking").
Please fill in this detail (fat content of __each__ meat category) when
you
post a follow-up.

Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then calculate
the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

It cannot be broken by 50-20-30%. It is 34.5-25.5-40% by defintion (?).

Is the dietician asking: find some combination of starch, meats, veg,
fruit, milk and fat that sums to 530 cal, but with a breakdown of
50-20-30%?

First, I don't know if both constraints can be met, based on the data
above.
But if it can be, it probably cannot be determined by a few formulas.
You
might be able to use Solver; but I doubt it. I would write a macro.

But I suspect there is a problem with my interpretation. You can see how
I
interpreted it. Please correct my misunderstanding, if any.


----- original message -----

St M(Lean)/M(Med.)/M(High) Veg Frt Mlk Fat

GOALS CALS PERCENT
CAR: 1,000 50.0%
PRO: 400 20.0%
FAT: 600 30.0%
Total: 2,000 100%

1 exchange 80 45 75 100 25 60 100
45
Calories

Carbohydrates combine Starch [65 of the 80 calories], Veg [20 of the 25
calories], Fruit [all 60 calories], Milk [38 of the 100 calories],
(B,F,G,H)

Protein combines Starch [15 of the 80 calories], Meat [40% of the total
meat calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk
[27
of
the 100 calories] & Fruit (C-E & H)

Fat combines Meat [60% of the total meat calories are Fat (C10-E10)] &
Milk
[35 of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

The original version of this worksheet is laid out so that a
client
or our staff Dietician can choose the number exchanges in day
(Starch-Fat)
and the sheet will calculate the calories and the percentage for
Carbs./Protein & Fat.

Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then calculate
the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

I'm not skilled enough to express these formulas or clear as to whether
there are too many variables for it to work.

Thank you for your help!

 

[Denise]

The percentage relationship is to the total calories not the breakdown of the
calories among the three.

If this can be expressed (and work) those caloric divisions among carbs or
protein, etc, will be the building blocks to reach the correct percentage for
the total number of calories entered.

Better?

Thank you again for being willing to help.

Does that make sense?

No, because the sum of the Carb calories is 183 (65 starch, 20 veg, 60
fruit, 38 milk), Prot is 135, and Fat is 212, for a total of 530. So Carb
is 34.5% (183/530), not 50%.

However, I do have another spin on all this that does make the calculations
doable. I cannot get into it now because I need to go some place.

In the meantime, you might cogitate on the disparity I mention above, and
perhaps you can post a further clarification, if needed.

PS: I don't feel that any clarification of the numbers is needed. In a
nutshell, I think they are intended to demonstrate how carbs, for example,
are distributed across the exchange groups (starch, veg, etc).


----- original message -----

Thanks for taking the time.

1 exchange of Starch = 80 calories
1 exchange of Meat- Lean = 45 calories
1 exchange of Meat - Med. = 75 calories
1 exchange of Meat - High = 100 calories
1 exchange of Veg. = 25 calories
1 exchange of Fruit = 60 calories
1 exchange of Milk = 100 calories
1 exchange of Fat = 45 calories

Carbohydrates - 50% of calories
combines Starch [65 of the 80 calories], Veg [20 of the 25 calories],
Fruit
[all 60 calories], Milk [38 of the 100 calories], (B,F,G,H)

Protein - 20% of calories
combines Starch [15 of the 80 calories], Meat [40% of the total meat
calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk [27 of
the
100 calories] (C-E & H)

Fat - 30% of calories
combines Meat [60% of the total meat calories are Fat (C10-E10)] & Milk
[35
of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

So....if, in the current worksheet, I entered 7 starches, 4 fruits, 3
milks,
4 vegetables, 5 fats, 1 high fat meat, 1 med. fat meat and 5 lean meats it
would calculate 1825 calories and the percentages of Carbs/Protein and
Fats.

What our RD wants is to anchor the percentages (always 50/20/30) and by
entering the total calories she wants the client to have - for the
spreadsheet to calculate the exchanges.

Does that make sense?

Please forgive my naivete, but either I am having a hard time
understanding
the information in your posting, or what you want cannot be done.

First, bear in mind that your references to columns (cells, in some
cases)
are unclear because of the mangled format of posted data (not your
fault).
I suggest that you present the data another way. The following is my
interpretation.

As I understand your data, 1 exchange is 530 cal broken down as follows:

Starch: 80 cal; Carb=65, Prot=15, Fat=0
Meat:
Lean: 45 cal; Carb=0, Prot=18, Fat=27 [*]
Med: 75 cal; Carb=0, Prot=30, Fat=45 [*]
High: 100 cal; Carb=0, Prot=40, Fat=60 [*]
Veg: 25 cal; Carb=20, Prot=5, Fat=0
Fruit: 60 cal; Carb=60, Prot=0, Fat=0
Milk: 100 cal; Carb=38, Prot=27, Fat=35
Fat: 45 cal; Carb=0, Prot=0, Fat=45

If I understand that correctly, then for 1 exchange: Carb=183, Prot=135,
Fat=212 cal.

By definition, Carb is 34.5% (183/530), Prot is 25.5% (35/530), and Fat
is
40% (212/530).

[*] Since the meat categories (lean, med, high) usually refer to fat
content, it seems incongruous to assume that the fat content is 60% of
each
category. You did say 60% of __total__ meat calories. But you offered
no
data as how that is distributed over lean, med and high fat content.
That
does not affect the summary calculations above. However, it might affect
solution to the speculated problem below ("is the dietician asking").
Please fill in this detail (fat content of __each__ meat category) when
you
post a follow-up.


Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then calculate
the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

It cannot be broken by 50-20-30%. It is 34.5-25.5-40% by defintion (?).

Is the dietician asking: find some combination of starch, meats, veg,
fruit, milk and fat that sums to 530 cal, but with a breakdown of
50-20-30%?

First, I don't know if both constraints can be met, based on the data
above.
But if it can be, it probably cannot be determined by a few formulas.
You
might be able to use Solver; but I doubt it. I would write a macro.

But I suspect there is a problem with my interpretation. You can see how
I
interpreted it. Please correct my misunderstanding, if any.


----- original message -----


St M(Lean)/M(Med.)/M(High) Veg Frt Mlk Fat

GOALS CALS PERCENT
CAR: 1,000 50.0%
PRO: 400 20.0%
FAT: 600 30.0%
Total: 2,000 100%

1 exchange 80 45 75 100 25 60 100
45
Calories

Carbohydrates combine Starch [65 of the 80 calories], Veg [20 of the 25
calories], Fruit [all 60 calories], Milk [38 of the 100 calories],
(B,F,G,H)

Protein combines Starch [15 of the 80 calories], Meat [40% of the total
meat calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk
[27
of
the 100 calories] & Fruit (C-E & H)

Fat combines Meat [60% of the total meat calories are Fat (C10-E10)] &
Milk
[35 of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

The original version of this worksheet is laid out so that a
client
or our staff Dietician can choose the number exchanges in day
(Starch-Fat)
and the sheet will calculate the calories and the percentage for
Carbs./Protein & Fat.

Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then calculate
the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30% Fat

I'm not skilled enough to express these formulas or clear as to whether
there are too many variables for it to work.

Thank you for your help!

 

[JoeU2004]

So....if, in the current worksheet, I entered 7 starches, 4 fruits, 3
milks,
4 vegetables, 5 fats, 1 high fat meat, 1 med. fat meat and 5 lean meats it
would calculate 1825 calories and the percentages of Carbs/Protein and
Fats.

I agree that that is __a__ solution for 1825 cal. What steps did you take
to arrive at those numbers? Trial and error?

Why did you choose __that__ solution? What's wrong, for example, with 0
starches, 20 lean meats, 0 med-fat meat, 0 high-fat meat, 1 veg, 15 fruit, 0
milk and 0 fat?

That also totals 1825 cal with roughly 50-20-30% carb/prot/fat, even closer
than your breakdown of 49-20-31%.

My question ("what's wrong") is rhetorical. Of course, the purpose is to
arrive at a diet that is reasonably balanced among the exchange categories.
The question is: what's "reasonably balanced"?

(See my strawman answer below.)

In any case, my point is....


I wrote previously:

Is the dietician asking: find some combination of starch, meats, veg,
fruit, milk and fat that sums to [a given number of] cal, but with a
breakdown
of 50-20-30%?

First, I don't know if both constraints can be met, based on the data
above. But [...] I would write a macro.

Apparently that is indeed what the dietician wants.

I wrote a macro to find all (!) solutions. I paused the macro after it had
found over 27,700 (!) solutions ... and it still had a very long way to go.

I am not suggesting such a macro as a solution. Perish the thought!

My point is: a deterministic solution -- one that could be designed with
formulas -- cannot be developed without some additional constraints.

Strawman....


I hinted at such a constraint when I wrote previously, referring to the
example given for 530 cal:

In a nutshell, I think they are intended to demonstrate how carbs, for
example, are distributed across the exchange groups (starch, veg, etc).

I can explain this better, and I can share a working spreadsheet that
prototypes the needed computations.

But I feel that this is going far beyond general interest in Excel usage.
It is specific to your problem.

So I suggest that we take this discussion offline. If you would like to
pursue it, send email to joeu2004 "at" hotmail.com with a recognizable
subject line (e.g. the same one that you used for your postings).

(If I do not respond with 24 hours, post here again, and we can try to
figure out what went wrong.)


----- original message -----

The percentage relationship is to the total calories not the breakdown of
the
calories among the three.

If this can be expressed (and work) those caloric divisions among carbs or
protein, etc, will be the building blocks to reach the correct percentage
for
the total number of calories entered.

Better?

Thank you again for being willing to help.

Does that make sense?

No, because the sum of the Carb calories is 183 (65 starch, 20 veg, 60
fruit, 38 milk), Prot is 135, and Fat is 212, for a total of 530. So
Carb
is 34.5% (183/530), not 50%.

However, I do have another spin on all this that does make the
calculations
doable. I cannot get into it now because I need to go some place.

In the meantime, you might cogitate on the disparity I mention above, and
perhaps you can post a further clarification, if needed.

PS: I don't feel that any clarification of the numbers is needed. In a
nutshell, I think they are intended to demonstrate how carbs, for
example,
are distributed across the exchange groups (starch, veg, etc).


----- original message -----

Thanks for taking the time.

1 exchange of Starch = 80 calories
1 exchange of Meat- Lean = 45 calories
1 exchange of Meat - Med. = 75 calories
1 exchange of Meat - High = 100 calories
1 exchange of Veg. = 25 calories
1 exchange of Fruit = 60 calories
1 exchange of Milk = 100 calories
1 exchange of Fat = 45 calories

Carbohydrates - 50% of calories
combines Starch [65 of the 80 calories], Veg [20 of the 25 calories],
Fruit
[all 60 calories], Milk [38 of the 100 calories], (B,F,G,H)

Protein - 20% of calories
combines Starch [15 of the 80 calories], Meat [40% of the total meat
calories are Protein (C10-E10)], Veg [5 of the 25 calories], Milk [27
of
the
100 calories] (C-E & H)

Fat - 30% of calories
combines Meat [60% of the total meat calories are Fat (C10-E10)] & Milk
[35
of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

So....if, in the current worksheet, I entered 7 starches, 4 fruits, 3
milks,
4 vegetables, 5 fats, 1 high fat meat, 1 med. fat meat and 5 lean meats
it
would calculate 1825 calories and the percentages of Carbs/Protein and
Fats.

What our RD wants is to anchor the percentages (always 50/20/30) and by
entering the total calories she wants the client to have - for the
spreadsheet to calculate the exchanges.

Does that make sense?

:

Please forgive my naivete, but either I am having a hard time
understanding
the information in your posting, or what you want cannot be done.

First, bear in mind that your references to columns (cells, in some
cases)
are unclear because of the mangled format of posted data (not your
fault).
I suggest that you present the data another way. The following is my
interpretation.

As I understand your data, 1 exchange is 530 cal broken down as
follows:

Starch: 80 cal; Carb=65, Prot=15, Fat=0
Meat:
Lean: 45 cal; Carb=0, Prot=18, Fat=27 [*]
Med: 75 cal; Carb=0, Prot=30, Fat=45 [*]
High: 100 cal; Carb=0, Prot=40, Fat=60 [*]
Veg: 25 cal; Carb=20, Prot=5, Fat=0
Fruit: 60 cal; Carb=60, Prot=0, Fat=0
Milk: 100 cal; Carb=38, Prot=27, Fat=35
Fat: 45 cal; Carb=0, Prot=0, Fat=45

If I understand that correctly, then for 1 exchange: Carb=183,
Prot=135,
Fat=212 cal.

By definition, Carb is 34.5% (183/530), Prot is 25.5% (35/530), and
Fat
is
40% (212/530).

[*] Since the meat categories (lean, med, high) usually refer to fat
content, it seems incongruous to assume that the fat content is 60% of
each
category. You did say 60% of __total__ meat calories. But you
offered
no
data as how that is distributed over lean, med and high fat content.
That
does not affect the summary calculations above. However, it might
affect
solution to the speculated problem below ("is the dietician asking").
Please fill in this detail (fat content of __each__ meat category)
when
you
post a follow-up.


Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then
calculate
the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30%
Fat

It cannot be broken by 50-20-30%. It is 34.5-25.5-40% by defintion
(?).

Is the dietician asking: find some combination of starch, meats, veg,
fruit, milk and fat that sums to 530 cal, but with a breakdown of
50-20-30%?

First, I don't know if both constraints can be met, based on the data
above.
But if it can be, it probably cannot be determined by a few formulas.
You
might be able to use Solver; but I doubt it. I would write a macro.

But I suspect there is a problem with my interpretation. You can see
how
I
interpreted it. Please correct my misunderstanding, if any.


----- original message -----


St M(Lean)/M(Med.)/M(High) Veg Frt Mlk
Fat

GOALS CALS PERCENT
CAR: 1,000 50.0%
PRO: 400 20.0%
FAT: 600 30.0%
Total: 2,000 100%

1 exchange 80 45 75 100 25 60 100
45
Calories

Carbohydrates combine Starch [65 of the 80 calories], Veg [20 of the
25
calories], Fruit [all 60 calories], Milk [38 of the 100 calories],
(B,F,G,H)

Protein combines Starch [15 of the 80 calories], Meat [40% of the
total
meat calories are Protein (C10-E10)], Veg [5 of the 25 calories],
Milk
[27
of
the 100 calories] & Fruit (C-E & H)

Fat combines Meat [60% of the total meat calories are Fat (C10-E10)]
&
Milk
[35 of the 100 calories] & Fat [all 45 calories] (C-E, H & I)

The original version of this worksheet is laid out so that a
client
or our staff Dietician can choose the number exchanges in day
(Starch-Fat)
and the sheet will calculate the calories and the percentage for
Carbs./Protein & Fat.

Our dietician has asked me to make it work inversely so that she can
insert
total calories for the client - she wants the sheet to then
calculate
the
number of exchanges based on a fixed 50% Carbs/20% Protein and 30%
Fat

I'm not skilled enough to express these formulas or clear as to
whether
there are too many variables for it to work.

Thank you for your help!