A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?

Could you restate the question? the beer in each glass AFTER you pour the
beer would always be 10, wouldn't it?

"vsoler" wrote:

A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?

Given the 20 cc of beer available, will you pour into each glass (starting
with more empty) until this glass is full ?

If above is true, the result will be that glasses B,D,C will be full and
galss A will miss 1 cc.

Am I wrong?

--
AP

"vsoler" a écrit dans le message de
oups.com...
A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?

Does it matter what order you pour?

If from left to right, here's a fairly generic solution. assume the
capacity of the glasses in E1 and the number of cc's to pour is in F1:

A3: =MIN($E$1,A1+F1)
B3: =MIN($E$1,SUM($A1:B1)+$F$1-SUM($A3:A3))

Copy B3 to the right to D3


In article .com,
"vsoler" wrote:

A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?

oops, missed your order statement...

In article ,
JE McGimpsey wrote:

Does it matter what order you pour?

Not really.

In the example given (20 cc to pour), the result would be

A B C D
1 7 3 5 4
2 4 1 3 2
3 9 10 10 10

Before pouring, I had 7+3+5+4 = 19 cubic centimeters (cc)
After pouring I can have, at most 19+20 = 39 cc
Because I start with the glass that is more empty, I can only pour 2 cc
in my first glass, giving a total of 7+2 = 9 cc

Another example, say I only have 10 cc to pour. Then,

A B C D
1 7 3 5 4
2 4 1 3 2
3 7 10 5 7

I start pouring in the glass with 3 cc, giving 3+7 = 10 cc. The other 3
go to the glass having 4 cc giving 4+3 = 7 cc. The other two glasses
are unchanged.

I hope it is clearer.

Ardus Petus,

Your right, you understood the question!!!

Now, I cannot still figure out what the formula would look like.

Hope you can help

Here's one way...........

=IF(A1=SMALL($A$1:$D$1,1),MAX(10,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(A1=SMALL($A$1:$D$1,2),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(A1=SMALL($A$1:$D$1,3),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),A1+20-(10-SMALL($A$1:$D$1,1))-(10-SMALL($A$1:$D$1,2))-(10-SMALL($A$1:$D$1,3)))))

Vaya con Dios,
Chuck, CABGx3



"vsoler" wrote:

A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?

Actually, that one only works with a starting value less than an amount
needed to fill all glasses.......this one seems better as it prevents
overfilling the last glass......

=IF(B1=SMALL($A$1:$D$1,1),MAX(10,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(B1=SMALL($A$1:$D$1,2),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(B1=SMALL($A$1:$D$1,3),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),MIN(10,B1+20-(10-SMALL($A$1:$D$1,1))-(10-SMALL($A$1:$D$1,2))-(10-SMALL($A$1:$D$1,3))))))

Vaya con Dios,
Chuck, CABGx3





"CLR" wrote:

Here's one way...........

=IF(A1=SMALL($A$1:$D$1,1),MAX(10,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(A1=SMALL($A$1:$D$1,2),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(A1=SMALL($A$1:$D$1,3),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),A1+20-(10-SMALL($A$1:$D$1,1))-(10-SMALL($A$1:$D$1,2))-(10-SMALL($A$1:$D$1,3)))))

Vaya con Dios,
Chuck, CABGx3



"vsoler" wrote:

A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?

Thank you, CLR,

Needless to say that my actual problem consists of more that 4 glasses,
around 10 to 15, and that your proposal becomes not practical. Thank
you anyway.

Should anyone else have any other suggestions, please let me know.

Regards

CLR wrote:
Actually, that one only works with a starting value less than an amount
needed to fill all glasses.......this one seems better as it prevents
overfilling the last glass......

=IF(B1=SMALL($A$1:$D$1,1),MAX(10,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(B1=SMALL($A$1:$D$1,2),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(B1=SMALL($A$1:$D$1,3),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),MIN(10,B1+20-(10-SMALL($A$1:$D$1,1))-(10-SMALL($A$1:$D$1,2))-(10-SMALL($A$1:$D$1,3))))))

Vaya con Dios,
Chuck, CABGx3





"CLR" wrote:

Here's one way...........

=IF(A1=SMALL($A$1:$D$1,1),MAX(10,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(A1=SMALL($A$1:$D$1,2),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(A1=SMALL($A$1:$D$1,3),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),A1+20-(10-SMALL($A$1:$D$1,1))-(10-SMALL($A$1:$D$1,2))-(10-SMALL($A$1:$D$1,3)))))

Vaya con Dios,
Chuck, CABGx3



"vsoler" wrote:

A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?

You're welcome, "anyway".........

The less accurate one describes their problem, the less likely they will be
to obtain a directly usable solution........perhaps next time...


Vaya con Dios,
Chuck, CABGx3


"vsoler" wrote:

Thank you, CLR,

Needless to say that my actual problem consists of more that 4 glasses,
around 10 to 15, and that your proposal becomes not practical. Thank
you anyway.

Should anyone else have any other suggestions, please let me know.

Regards

CLR wrote:
Actually, that one only works with a starting value less than an amount
needed to fill all glasses.......this one seems better as it prevents
overfilling the last glass......

=IF(B1=SMALL($A$1:$D$1,1),MAX(10,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(B1=SMALL($A$1:$D$1,2),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(B1=SMALL($A$1:$D$1,3),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),MIN(10,B1+20-(10-SMALL($A$1:$D$1,1))-(10-SMALL($A$1:$D$1,2))-(10-SMALL($A$1:$D$1,3))))))

Vaya con Dios,
Chuck, CABGx3





"CLR" wrote:

Here's one way...........

=IF(A1=SMALL($A$1:$D$1,1),MAX(10,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(A1=SMALL($A$1:$D$1,2),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),IF(A1=SMALL($A$1:$D$1,3),MAX(1 0,20-SMALL($A$1:$D$1,1)-SMALL($A$1:$D$1,2)-SMALL($A$1:$D$1,3)-SMALL($A$1:$D$1,4)),A1+20-(10-SMALL($A$1:$D$1,1))-(10-SMALL($A$1:$D$1,2))-(10-SMALL($A$1:$D$1,3)))))

Vaya con Dios,
Chuck, CABGx3



"vsoler" wrote:

A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?

This is why you should post your real question - I imagine you just
wasted more than a little of Chuck's time...

It's hard to justify investing time helping when you play "bring me a
rock".


In article . com,
"vsoler" wrote:

Needless to say that my actual problem consists of more that 4 glasses,
around 10 to 15, and that your proposal becomes not practical. Thank
you anyway.

Should anyone else have any other suggestions, please let me know.

vsoler Wrote:
A difficult one for me!!!

A B C D
1 7 3 5 4
2 4 1 3 2
3 ? ? ? ?

I have 4 glasses of beer filled with, say 7, 3, 5 and 4 cubic
centimeters (row 1)
I RANK them from more empty to less empty (row 2, not my question)

Now, I have 20 cubic centimeters of beer to fill my glasses, starting
from the more empty and finishing in the less empty.

If the capacity of a glass is 10 cubic centimeters, what's the formula
for row 3 which will give me the beer in each glass after I pour the
beer?

Please, I'm looking for a formula that does not use any intermediate
calculations in other cells.

Want to share the beer with me?



See attachment, the catch is you CANNOT have the same rank more than
once. ie. If you have data 7, 3, 3, 4....you need to rank them 4, 1,
2, 3 instead of 3, 1, 1, 2. Press F9 to recalculate the page.


Good luck


+-------------------------------------------------------------------+
|Filename: Beer.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4617 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=531880

Morrigan,

Your answer is excellent, super !!! It not only works, but also I was
able to understand how it works!

Thank you very much indeed.

Comments:

1/ For some unknown reason, the function RANDBETWEEN does not work in
my system. Any ideas why?

2/ When 2 glasses are filled with exactly the same quantity of beer, it
fills them from left to right, which is perfectly acceptable for me;
therefore, there is no catch at all

3/ Actually, the problem that I am trying to solve is, still, a little
bit more difficult, because my glasses do not all of them hold the same
kind of beer, and I am trying to pour only one kind; I cannot pour in
any glass having a different beer. Do you recommend me to ask my
question in this post, or should I open a new one?

Thank you very much.

PS: your link to excelforum did not work in my system, I had to go to
the site instead.

vsoler Wrote:
Morrigan,

Your answer is excellent, super !!! It not only works, but also I was
able to understand how it works!

Thank you very much indeed.

Comments:

1/ For some unknown reason, the function RANDBETWEEN does not work in
my system. Any ideas why?

2/ When 2 glasses are filled with exactly the same quantity of beer,
it
fills them from left to right, which is perfectly acceptable for me;
therefore, there is no catch at all

3/ Actually, the problem that I am trying to solve is, still, a little
bit more difficult, because my glasses do not all of them hold the
same
kind of beer, and I am trying to pour only one kind; I cannot pour in
any glass having a different beer. Do you recommend me to ask my
question in this post, or should I open a new one?

Thank you very much.

PS: your link to excelforum did not work in my system, I had to go to
the site instead.


In order to use RANDBETWEEN() function, you need to have Analysis
Toolpak Add-In installed. It is up to you to open a new thread or ask
in this post. I am certain people are glad to help either way as long
as you provide a clear and descriptive statement to your problem.

Quite a few people told me they had problems downloading the files I
uploaded. I have no problem downloading them myself, not sure why some
people have issues.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=531880