Help on randomly selecting cells to SUM

[kayb701]

Hello,
I have a decent grasp on the workings of Excel, but I am not extremely versed in macros. I am working on costing a buffet table spread to determine the average cost per pound of food a customer buys. I have 59 unique cells (which refer to the 59 unique prices by oz that the buffet offers). Is there a method to randomly select..let's say.. 8 of these dishes and calculate the sum of their prices? How can I include a rule that ensures that the weight of these 8 dishes sum up to 16 oz? I would like to utilize the cell randomization, so that the overall price changes and presents different scenarios.

I have written RAND rules to establish sensible maximum and minimums...(=RAND()*(5-0.1)+0.1 ) for salad dressing, because I do not believe someone would purchase 16 oz worth of dressing, etc...

Does anyone have advice / input on where I should start / functions I should look into?

Thanks for your help,
Kay.

 

[taxasker]

I am working on costing a buffet table spread to
determine the average cost per pound of food a
customer buys. I have 59 unique cells (which refer
to the 59 unique prices by oz that the buffet offers).
Is there a method to randomly select..let's say..
8 of these dishes and calculate the sum of their
prices? How can I include a rule that ensures that
the weight of these 8 dishes sum up to 16 oz?

I suspect the complete solution will require some back-and-forth
refinements. The following paradigm might be a good start.

Suppose you want 0.1 to 5 oz of any one item, with 0.1 oz resolution.

Suppose B1 contains the amount for the first item:
=RANDBETWEEN(1,50)/10

B2 contains the amount of the next item:
=RANDBETWEEN(ROUND(10*MAX(0.1,16-SUM($B$1:B1)-5*(8-COUNT($B$1:B1)-1)),0),
ROUND(10*MIN(5,16-SUM($B$1:B1)-0.1*(8-COUNT($B$1:B1)-1)),0))/10

Copy B2 into B3:B7. B8 could be the same formula. But it is simpler to
write:
=16-SUM(B1:B7)

See below regarding RANDBETWEEN.

FYI, the use of ROUND above is needed to circumvent anomalies that arise
with Excel arithmetic and the behavior of RANDBETWEEN with non-integer
parameters(!). I can explain in more detail, if you really want to know.
But it is very technical and somewhat overwhelming for most people.

Note: It will probably be preferrable to bury RANDBETWEEN in a VBA function
or otherwise avoid the incessant recalculation of those formulas and any
dependent formulas when any cell in any worksheet in the workbook is
modified. Do you need help with that?

I have written RAND rules to establish sensible maximum
and minimums...(=RAND()*(5-0.1)+0.1 ) for salad dressing,
because I do not believe someone would purchase 16 oz
worth of dressing, etc...
Does anyone have advice / input on where I should start
/ functions I should look into?

Nothing wrong with using a RAND expression like that, especially for
non-integers.

But for random integers between 1 and n, consider using RANDBETWEEN(1,n).

For random non-integers, you might want to ensure that the result is rounded
to tenths, for example. So your example above might become ROUND(0.1 +
4.9*RAND(),1).

But we could also write RANDBETWEEN(1,50)/10. That can be much simpler to
read and maintain in some cases. Like the second formula above.

To select 8 of 59 dishes, presumably you do not want duplication. There are
several ways to do that, depending on the kind of design you want. While
there are some turnkey VBA functions, it is just as easy to write your own.
But there are also simple ways to avoid VBA, which may or may not be one of
your objectives.

For example, simply create a column with the numbers 1 to 59, put =RAND()
into 59 adjacent cells, manually Sort the two columns based on the RAND
column, then use the first 8 numbers in the integer column with INDEX and/or
OFFSET.

That only touches on the highlights. There are probably other constraints
to consider. The devil is in the details.

You can provide some details here: layout of data (actual cell references),
constraints, etc. Or better: you can up an example Excel file; see
instructions below. Or I can work with you one-on-one.

I will send email to your gmail.com account. If you do not receive it, feel
free to write to me at joeu2004 "at" hotmail.com.

 

[joeu2004]

[reposted with correct user id. sorry.]

I am working on costing a buffet table spread to
determine the average cost per pound of food a
customer buys. I have 59 unique cells (which refer
to the 59 unique prices by oz that the buffet offers).
Is there a method to randomly select..let's say..
8 of these dishes and calculate the sum of their
prices? How can I include a rule that ensures that
the weight of these 8 dishes sum up to 16 oz?

I suspect the complete solution will require some back-and-forth
refinements. The following paradigm might be a good start.

Suppose you want 0.1 to 5 oz of any one item, with 0.1 oz resolution.

Suppose B1 contains the amount for the first item:
=RANDBETWEEN(1,50)/10

B2 contains the amount of the next item:
=RANDBETWEEN(ROUND(10*MAX(0.1,16-SUM($B$1:B1)-5*(8-COUNT($B$1:B1)-1)),0),
ROUND(10*MIN(5,16-SUM($B$1:B1)-0.1*(8-COUNT($B$1:B1)-1)),0))/10

Copy B2 into B3:B7. B8 could be the same formula. But it is simpler to
write:
=16-SUM(B1:B7)

See below regarding RANDBETWEEN.

FYI, the use of ROUND above is needed to circumvent anomalies that arise
with Excel arithmetic and the behavior of RANDBETWEEN with non-integer
parameters(!). I can explain in more detail, if you really want to know.
But it is very technical and somewhat overwhelming for most people.

Note: It will probably be preferrable to bury RANDBETWEEN in a VBA function
or otherwise avoid the incessant recalculation of those formulas and any
dependent formulas when any cell in any worksheet in the workbook is
modified. Do you need help with that?

I have written RAND rules to establish sensible maximum
and minimums...(=RAND()*(5-0.1)+0.1 ) for salad dressing,
because I do not believe someone would purchase 16 oz
worth of dressing, etc...
Does anyone have advice / input on where I should start
/ functions I should look into?

Nothing wrong with using a RAND expression like that, especially for
non-integers.

But for random integers between 1 and n, consider using RANDBETWEEN(1,n).

For random non-integers, you might want to ensure that the result is rounded
to tenths, for example. So your example above might become ROUND(0.1 +
4.9*RAND(),1).

But we could also write RANDBETWEEN(1,50)/10. That can be much simpler to
read and maintain in some cases. Like the second formula above.

To select 8 of 59 dishes, presumably you do not want duplication. There are
several ways to do that, depending on the kind of design you want. While
there are some turnkey VBA functions, it is just as easy to write your own.
But there are also simple ways to avoid VBA, which may or may not be one of
your objectives.

For example, simply create a column with the numbers 1 to 59, put =RAND()
into 59 adjacent cells, manually Sort the two columns based on the RAND
column, then use the first 8 numbers in the integer column with INDEX and/or
OFFSET.

That only touches on the highlights. There are probably other constraints
to consider. The devil is in the details.

You can provide some details here: layout of data (actual cell references),
constraints, etc. Or better: you can up an example Excel file; see
instructions below. Or I can work with you one-on-one.

I will send email to your gmail.com account. If you do not receive it, feel
free to write to me at joeu2004 "at" hotmail.com.