lists

[Jeff Spiccoli]

Hi,

I have a spreadsheet I use to calculate costs for use of a facility. The
two main variables are number of nights and number of people. There are many
factors that go into this calculation such as number of staff required, food
costs, transportation etc. (The costs for these have several different
thresholds that are built in, they're not simply straightline.) The
spreadsheet works well, but now I want to take it a step further.

I want to make a list of the costs for all combinations, within a reasonable
range. I'll have three columns with 2, 3, and 4 night stays. Then I'll have
about 120 rows with 20, 21, 22, ... 130 guests. I want Excel to build a
table for me. (Of course I could enter in each one separately, but this
would be cumbersome.)

Can anyone suggest a simple way to do this?

Thanks!

 

[Max]

Think a 2 variable data table might do it for you

Here's a simple illustration ..

Assume you have in A1:C2

#ofNights_#ofPeople_Costs
2_________25______1000

where the formula in C2 is : =(B2*20)*A2

C2's simple formula calculates the output of interest
i.e. Cost, based on numbers input in A2 (Night stays)
and B2 (# of Guests) - i.e. 2 precedencies
which would be the 2 variables used to compute Cost

[your actual formula for Cost would invariably be more
complex, but as long as it's dependent on
*both* Night stays and # of Guests,
the principle's the same]

Now to set up the data table
---------------------------------
Put in A4: =C2
(this establishes a link to the Cost cell)

Enter in B44 the numbers: 2, 3, 4 (night stays)
Enter in A5:A10 the numbers: 20, 21, 22... 25 (guests)

Select A410

Click Data > Table

In the Table dialog, enter for:
----------------------------------
Row input cell: A2
Column input cell: B2

Click OK

In B510 will be computed the Cost amounts
corresponding to the number of nights (2,3,4)
and the number of people (20,21 ... 25)

If desired, just mask A4 by formatting the font
as "white" to blend-in with the fill colour
and format B510 as currency

Adapt / extend the above to suit

Note:
-------
All cells within B510 will have the same formula:
{=TABLE(A2,B2)}
but note that this formula *cannot* be keyed-in
and array-entered like a "normal" array formula,
it has to be set-up via the Data > Table menu
as outlined above
--

This link has a pdf file which covers the data-table topic
in some detail (watch the line wrap):

http://it.fuqua.duke.edu/public/2001XLDataTablesMonochrome.pdf

Try also : http://tinyurl.com/5b7uf
for a simple example set-up for
a 1-variable and a 2-variable data table

 

[Jeff Spicolli]

Max,

THANK YOU!!! It works great!

This is very helpful to me.

Jeff

Think a 2 variable data table might do it for you

Here's a simple illustration ..

Assume you have in A1:C2

#ofNights_#ofPeople_Costs
2_________25______1000

where the formula in C2 is : =(B2*20)*A2

C2's simple formula calculates the output of interest
i.e. Cost, based on numbers input in A2 (Night stays)
and B2 (# of Guests) - i.e. 2 precedencies
which would be the 2 variables used to compute Cost

[your actual formula for Cost would invariably be more
complex, but as long as it's dependent on
*both* Night stays and # of Guests,
the principle's the same]

Now to set up the data table
---------------------------------
Put in A4: =C2
(this establishes a link to the Cost cell)

Enter in B44 the numbers: 2, 3, 4 (night stays)
Enter in A5:A10 the numbers: 20, 21, 22... 25 (guests)

Select A410

Click Data > Table

In the Table dialog, enter for:
----------------------------------
Row input cell: A2
Column input cell: B2

Click OK

In B510 will be computed the Cost amounts
corresponding to the number of nights (2,3,4)
and the number of people (20,21 ... 25)

If desired, just mask A4 by formatting the font
as "white" to blend-in with the fill colour
and format B510 as currency

Adapt / extend the above to suit

Note:
-------
All cells within B510 will have the same formula:
{=TABLE(A2,B2)}
but note that this formula *cannot* be keyed-in
and array-entered like a "normal" array formula,
it has to be set-up via the Data > Table menu
as outlined above
--

This link has a pdf file which covers the data-table topic
in some detail (watch the line wrap):

http://it.fuqua.duke.edu/public/2001XLDataTablesMonochrome.pdf

Try also : http://tinyurl.com/5b7uf
for a simple example set-up for
a 1-variable and a 2-variable data table
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi,

I have a spreadsheet I use to calculate costs for use of a facility. The
two main variables are number of nights and number of people. There are many
factors that go into this calculation such as number of staff required, food
costs, transportation etc. (The costs for these have several different
thresholds that are built in, they're not simply straightline.) The
spreadsheet works well, but now I want to take it a step further.

I want to make a list of the costs for all combinations, within a reasonable
range. I'll have three columns with 2, 3, and 4 night stays. Then I'll have
about 120 rows with 20, 21, 22, ... 130 guests. I want Excel to build a
table for me. (Of course I could enter in each one separately, but this
would be cumbersome.)

Can anyone suggest a simple way to do this?

Thanks!

 

[Max]

Glad to hear that, Jeff !
Thanks for the feedback