Help With Multiple If/And Statements

[slparadise]

[TABLE="width: 500"]
<tbody>[TR]
[TD]TABLE 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widget[/TD]
[TD]--Length of Widget--[/TD]
[TD]--SF of Widget--[/TD]
[TD]$$COST$$[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]TABLE 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--Length of Widget--[/TD]
[TD]$$COST$$[/TD]
[/TR]
[TR]
[TD]0-5 Feet[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]6-10 Feet[/TD]
[TD]$15[/TD]
[/TR]
</tbody>[/TABLE]


I need to create a formula that will calculate the cost in TABLE 1 assuming a minimum for the --SF of Widget-- cell. I also need that formula to calculate based on the amounts in TABLE 2. I have created a drop down box in TABLE 1 for --Length of Widget-- that corresponds to TABLE 2. I am struggling with the minimum component. I need the formula to use 500 as a multiplier if the --SF of Widget-- is LESS THAN 500 and to use the number input in the --SF of Widget-- cell if that number is GREATER THAN 500.

Essentially if the --Length of Widget-- is 0-5 feet and the --SF of Widget-- is 300; the equation would be $10 * 500 ($10=0-5 Feet; 500=--SF of Widget--Minimum). If the the --Length of Widget-- is 0-5 feet and the --SF of Widget-- is 600; the equation would be $10 * 600 ($10=0-5 Feet; 500=--SF of Widget--Minimum).

Thanks for any and all help. I really do appreciate it.

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H
Widget	Length	SF of Widget	Cost $	Length
A	From	To	Cost
B
C
D
E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"] $ 10.00 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"] $ 15.00 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"] $ 20.00 [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]11250[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=VLOOKUP(B2,$F$3:$H$5,3)*MAX(500,C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]