how to calculate a value for 49 possible combinations

Though my problem is a bit more complicated than this, I think that if I
solve this, I can apply the rule to my real problem.

In A1, I've defined a data-validation (list/range) of which the values can
be SUN, MON, TUE, WED, THU, FRI, and SAT. (arrival day)
In B1, I've defined a data-validation (list/range) of which the values can
be SUN, MON, TUE, WED, THU, FRI, and SAT. (departure day)

I need to determine a total dollar-amount depending upon the arrival day and
the departure day (i.e., how long the technician is on-site). I suppose that
I could "hard-code" the values for 49 different combinations. But is there a
formula that can determine the value by looking at the "intersection" of a 7
x 7 table (A1:H8)? I can populate the values relatively easily. But can a
formula (in cell C1, for example) "look" at the corresponding intersection of
a column and row (e.g, arriving MON and departing FRI) and get the value
from that intersected cell? I don't want to do this in a macro. I want to
do it in a formula.

Thanks for any suggestions.

use vlookup to assign numbers to days SUN=1 MON=2, etc then use ABS(arrival
day - departure day) +1

Sure it's possible
to find a cell's value inside a table
Look the help of INDEX function

and also MATCH

so now, if you make a table, with day-names for labels (rows and
collumns) you may use MATCH (twice) to know the position of a known day
inside a serie, (twice: one for each date) and then choose the
intersection in the table by a
INDEX (table; row position;column position)
function

Say your data list was on Sheet2, from A1:H8.
Sheet1 - A1 = drop down list
Sheet1 - B1 = second drop down list
Sheet1 - C1 = this formula:

=INDEX(Sheet2!A1:H8,MATCH(A1,Sheet2!A1:A8,0),MATCH(B1,Sheet2!A1:H1,0))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"crimsonkng" <[email protected]> wrote in message
news:[email protected]...
> Though my problem is a bit more complicated than this, I think that if I
> solve this, I can apply the rule to my real problem.
>
> In A1, I've defined a data-validation (list/range) of which the values can
> be SUN, MON, TUE, WED, THU, FRI, and SAT. (arrival day)
> In B1, I've defined a data-validation (list/range) of which the values can
> be SUN, MON, TUE, WED, THU, FRI, and SAT. (departure day)
>
> I need to determine a total dollar-amount depending upon the arrival day
and
> the departure day (i.e., how long the technician is on-site). I suppose
that
> I could "hard-code" the values for 49 different combinations. But is
there a
> formula that can determine the value by looking at the "intersection" of a
7
> x 7 table (A1:H8)? I can populate the values relatively easily. But can
a
> formula (in cell C1, for example) "look" at the corresponding intersection
of
> a column and row (e.g, arriving MON and departing FRI) and get the value
> from that intersected cell? I don't want to do this in a macro. I want
to
> do it in a formula.
>
> Thanks for any suggestions.

you all are great. thanks so much.
Dan

"crimsonkng" wrote:

> Though my problem is a bit more complicated than this, I think that if I
> solve this, I can apply the rule to my real problem.
>
> In A1, I've defined a data-validation (list/range) of which the values can
> be SUN, MON, TUE, WED, THU, FRI, and SAT. (arrival day)
> In B1, I've defined a data-validation (list/range) of which the values can
> be SUN, MON, TUE, WED, THU, FRI, and SAT. (departure day)
>
> I need to determine a total dollar-amount depending upon the arrival day and
> the departure day (i.e., how long the technician is on-site). I suppose that
> I could "hard-code" the values for 49 different combinations. But is there a
> formula that can determine the value by looking at the "intersection" of a 7
> x 7 table (A1:H8)? I can populate the values relatively easily. But can a
> formula (in cell C1, for example) "look" at the corresponding intersection of
> a column and row (e.g, arriving MON and departing FRI) and get the value
> from that intersected cell? I don't want to do this in a macro. I want to
> do it in a formula.
>
> Thanks for any suggestions.