IF function for a range of numbers within another range

I work in communications, and want to display a spreadsheet that shows whether a transmitter can be receiveed by particular receivers as follows:

Data Rows:

Transmitter....LOW FREQ...HI FREQ......Receiver......LOW FREQ....HIGH FREQ
XTR 1............123 Mhz......300 Mhz......RCVR1........ 50 Mhz.......1000 Mhz

Since the receiver's capabilities are 50 - 1000 Mhz, the transmitter falls into it's reception range. So I would use a function in a cell next to the Receiver name to result in a simple 1 or 0. 1 being capable or receiving that transmitter, 0 not capable. Then use Conditional Formatting to turn that cell Green or Red respectively based on the 1 or 0.

I have tried nested IF functions to no avail. If any part of the transmitter number range falls within the receiver's number range, I want to simply color a cell green or red.

Sorry for the verbose post, hope I got my point across, and appreciate any assistance.

Thank for the help, although it needed a little tweaking to function correctly for a transmitter less than the RCV LOW and greater than the RCV HIGH.

I take it the -- in front of the or function converts the True or False to a 1 or 0?

Here's the final formula I cam up with that worked:

Please Login or Register  to view this content.

And the conditional formatting on that cell is just based off the resulting 1 or 0.

Thank you!!

That's perfect, THANK YOU Mike!!