Hello Thewenanator,
Thanks for the clarification of the question.
The formula (two version) to paste into cell E1 is included at the end
of this answer. The first formula assumes that chart one (indexed by
"F") starts at cell A3 (or row 3, column 1) and chart two (indexed by
"T") starts at cell A17 (or row 17, column 1). I assume the values to
look up are in different locations. I'll explain the values to adjust
to correct the formula for your situation (or use the second version).
Let me explain the formula in detail as well as the methods used to
determine the correct operation. The functions I used are:
IF
INDIRECT
TEXT
if you type those phrases into the Microsoft Excel help, you can find
additional information related to those functions. I also used the
string operator & to concatenate two strings together.
First, based on your description, multiple values need to be looked up
for the multiplier. I used nested IF functions to do this as follows:
IF(D1="P",1.6,IF(D1=19,1.18,1))
This compares D1 to the string value "P" and if true, returns 1.6. If
D1 is not "P", the next check is for the number 19 (not the string 19)
and if true, returns 1.18. If both are false, the value 1 is returned.
If you want to explicitly check for blank, revise this to something
like:
IF(D1="P",1.6,IF(D1=19,1.18,IF(D1=" ",1,0)))
which adds the explicit check & returns zero if none are matched.
Alternatively, you could put 1/0 in place of 0 to generate an error
value (#div/0!) if desired.
Second, based on your description, we need to use another IF function
to determine which table (or chart) of data to use. The construct is
something like this:
IF(C1="F",[formula for chart F], [formula for chart T])
Again, if you need to explicitly check for "T" (and not any other
value), this can be rewritten like
IF(C1="F",[formula for chart F], IF(C1="T",[formula for chart T],0))
You can also generate an error like described previously if desired as well.
Third, based on your description, we need to look up a value based on
the data from the two cells (A1 and B1). To do this, I used the
"INDIRECT" function - one I find rarely used by Excel users. There are
several good online references for this function as well - search
online with a phrase like
Excel indirect
for those sites.
Since I indicated that F started at cell A3 (or row 3, column 1), the
formula to look up the value is:
INDIRECT(TEXT(2+A1,"R#")&TEXT(B1-1,"C#"),FALSE)
This is a little complicated, so let me explain the steps involved.
INDIRECT takes a cell reference in A1 format or R1C1 (row / column)
format. I am using the latter, so the second parameter must be False.
The first TEXT function call generates the row reference (R5) using
the two plus the value in A1 and the format R# (the letter R followed
by the numeric value). The second TEXT function call generates the
column reference (C9) using the value in B1 minus one and the format
C# (the letter C followed by the numeric value). The two strings are
concatenated to produce the string value R5C9 used to look up the
value 8.56 as described in your question.
In a similar manner, the values in "T" are referenced with the second
INDIRECT function call using row and column offsets of +16 and -1 to
compute the proper cell references.
Since I expect your values (for "F" and "T") to be in different
locations, adjust the values (+2, -1) and (+16, -1) to refer to the
proper row / column to look up the values.
As another alternative - if you have defined names for the two regions
of values ("F" and "T"), you can use a pair of functions ROW and
COLUMN to lookup the starting row & column of the defined names. I
have included another version of the formula at the end using this
method - assuming the defined names of "F" and "T" for the range of
look up values. This second version will adjust the row / column
references automatically if you add / remove rows or columns so that
"F" and "T" moves. The first version would have to be fixed manually
if you reorganized the spreadsheet in this way.
If you have any difficulty adapting either formula for your specific
situation, if any part of the answer is unclear, or you need
additional information on this topic, please make a clarification
request. I would be glad to help you further.
Good luck with your work.
--Maniac
The formula is split into several lines so to make it more readable -
combine into a single formula when you copy & paste into your cell at
E1.
=IF(D1="P",1.6,IF(D1=19,1.18,1))*
IF(C1="F",INDIRECT(TEXT(2+A1,"R#")&TEXT(B1-1,"C#"),FALSE),
INDIRECT(TEXT(16+A1,"R#")&TEXT(B1-1,"C#"),FALSE))
The second alternative, using names F and T to refer to the two ranges
of look up values. This also includes an example of an error generated
if the value isn't set properly in cell D1.
=IF(D1="P",1.6,IF(D1=19,1.18,IF(D1=" ",1,1/0)))*
IF(C1="F",
INDIRECT(TEXT(A1+ROW(F)-1,"R#")&TEXT(B1+COLUMN(F)-2,"C#"),FALSE),
INDIRECT(TEXT(A1+ROW(T)-1,"R#")&TEXT(B1+COLUMN(T)-2,"C#"),FALSE)) |