Function for detecting is a parameter is in range

[rjwalker2514]

[TABLE="width: 500"]
<tbody>[TR]
[TD]HEIGHT[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]WIDTH[/TD]
[TD]2100[/TD]
[/TR]
[TR]
[TD]TOTAL SIZE[/TD]
[TD]4100[/TD]
[/TR]
[TR]
[TD]DESIGN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Out of Range[/TD]
[/TR]
</tbody>[/TABLE]

Hi I would like a function that will detect if a value is outside the design parameters.

Example:

DESIGN 1 is only compatible if the total size is between 1000 & 4000 inclusive. How can I write a formula that detects that this example if Out of Range and shows that in a nearby cell?

If the DESIGN number was to change to 2 the compatible size would now be between 1000 & 2600. Can I add to the formula to show this also?

 

[JoeMo]

This formula is for the specific case you cite in your post, where Designs are numbered 1, 2, ... and can be expanded to include higher-number designs.

Note that the labels you posted are used to create named ranges by selecting A1:B4, then Formula>Defined Names>Create from Selection and make sure the Names to the left check box is ticked. The formula is in D1.

Excel Workbook
	A	B	C	D
1	HEIGHT	2000		Out of Range
2	WIDTH	2100
3	TOTAL SIZE	4100
4	DESIGN	1
Sheet3