Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

For example, if one value is greater than 101 and less than 225, the value
is $31.04.

Any ideas?

--
JerryS

One way:

=VLOOKUP(J1, A2:C6, 3, TRUE)

where J1 is your value, and the table you show is in A1:D6.

In article ,
JerryS wrote:

Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

For example, if one value is greater than 101 and less than 225, the value
is $31.04.

Any ideas?

=VLOOKUP(101,A2:D6,3,1)
or
=SUMPRODUCT(--(A2:A6=101),--(B2:B6<=225),C2:C6)


"JerryS" wrote:

Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

For example, if one value is greater than 101 and less than 225, the value
is $31.04.

Any ideas?

--
JerryS

Another :

=Lookup(J1,A1:A6,C1:C6)

HTH
--
Santipong

JerryS เขียน:
Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

For example, if one value is greater than 101 and less than 225, the value
is $31.04.

Any ideas?

--
JerryS

Thanks. I've used the SUMPRODUCT formula before so I want to stay with that.
I want to expand theformula to include the ranges from each row. For example,
if value is between 0 and 30, then C1, if between 31 and 100, then C2, and so
on. How would you suggest I write that formula?
--
JerryS


"Teethless mama" wrote:

=VLOOKUP(101,A2:D6,3,1)
or
=SUMPRODUCT(--(A2:A6=101),--(B2:B6<=225),C2:C6)


"JerryS" wrote:

Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

For example, if one value is greater than 101 and less than 225, the value
is $31.04.

Any ideas?

--
JerryS

=SUMPRODUCT(--(A2:A6<=F2),--(B2:B6=F2),C2:C6)
Where your sample table is in A1:D6 and the value you are looking up is in
F2. Adjust as necessary.
If your table is on a different sheet from your value include sheet names:
=SUMPRODUCT(--(Sheet1!A2:A6<=C2),--(Sheet1!B2:B6=C2),Sheet1!C2:C6)
Where your table is in A1:D6 on Sheet1 and the value is in C2 on a different
sheet.

"JerryS" wrote:

Thanks. I've used the SUMPRODUCT formula before so I want to stay with that.
I want to expand theformula to include the ranges from each row. For example,
if value is between 0 and 30, then C1, if between 31 and 100, then C2, and so
on. How would you suggest I write that formula?
--
JerryS


"Teethless mama" wrote:

=VLOOKUP(101,A2:D6,3,1)
or
=SUMPRODUCT(--(A2:A6=101),--(B2:B6<=225),C2:C6)


"JerryS" wrote:

Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

For example, if one value is greater than 101 and less than 225, the value
is $31.04.

Any ideas?

--
JerryS

Tue, 8 May 2007 08:34:02 -0700 from JerryS
:
Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

For example, if one value is greater than 101 and less than 225, the value
is $31.04.

Use VLOOKUP with the fourth argument set to TRUE.

You don't need your first column at all, assuming it's impossible for
your input value to be less than 0.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/