Help with calculations using ifs and minimum

[slparadise]

I need help creating an ifs formula that will calculate based on a minimum number. Here is an example:

TABLE 1
Widget	--Widget Length--	--Widget SF--	$$$PRICE$$$

<tbody>
</tbody>

TABLE 2
--Widget Length--	--Widget Cost--
0-24'	5
25'-28'	10
29'-32'	15
33'-36'	20

<tbody>
</tbody>

The –Widget Length— in TABLE 1 is tied to the –Widget Length—in Table 2 in the form of a drop-down box. I need the $$$PRICE$$$ in TABLE 1 to calculate based on a 500 SF minimum in the –Widget SF— cell but need the formula to recognize that if the number in the –Widget SF—is greater than 500 to use the larger number.

Example:

--Widget Length--	--Widget SF--	Formula (Cost * SF)
0-24’	350	5 * 500
29’-32’	750	15 * 750

<tbody>
</tbody>

 

[jtakw]

Hi,

As you have it in your sample, assuming your Table 2 Column A is Exactly the same as in your Table 1 Column B:

Book1
	A	B	C	D
1	TABLE 1
2	Widget	--Widget Length--	--Widget SF--	$$$PRICE$$$
3		0-24'	350	2500
4		29'-32'	750	11250
5
6
7	TABLE 2
8	--Widget Length--	--Widget Cost--
9	0-24'	5
10	25'-28'	10
11	29'-32'	15
12	33'-36'	20
Sheet460

Cell Formulas
Range	Formula
D3	=VLOOKUP(B3,A$9:B$12,2,0)*MAX(C3,500)

 

[slparadise]

Thanks!!! Didn't think of vlookup... works great.

 

[jtakw]

You're welcome.