# Solved: Excel 2003 Formula

Discussion in 'Business Applications' started by computerman29642, Apr 16, 2008.

Not open for further replies.

Joined:
Dec 4, 2007
Messages:
2,895
I have lower and upper limits. How would I create a formula in excel to say if the number in Column F is not between the lower limit in column m and the upper limit in column n, then column p equals "No". If the number does fall within the limits, then place "Yes" in column P?

For Example:

F Column
5
12
1
0
10

M Column
1 (Lower Limit)

N Column
10 (Upper Limit)

P Column
Yes
No
Yes
No
Yes

2. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255

I have kept the info in column A & B

1 & 10 are in A1 & B1
The data is in A3 to A7
The results/formulas are in B3 to B7

The formula in B3 is =IF(AND(A3>=\$A\$1,A3<=\$B\$1),"Yes","No")
This can be copied down column B

Hope that helps

#### Attached Files:

• ###### Yes No.xls
File size:
14 KB
Views:
98

Joined:
Dec 4, 2007
Messages:
2,895
Thanks Villian. I will give this a try.

4. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
No Problem. Let us know if it works OK

Joined:
Dec 4, 2007
Messages:
2,895
Is there a way to create a macro to perform a conditional format? For example, if the FormulaIs equal to Yes then the cell background will be red, and the value will be bolded.

If anyone knows a better way, I would really like to hear any suggestions.

6. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
You can do that through using the menu Format, Conditional format

When you creatae the formula, don't copy it till you have done the Conditional format.

Select the cell with the formula in
Then Format, conditional format and in the three text boxes
Select cell Value is
Select Equal to
Type in Yes

Then click on Format...

Select the Font Tab and click on Bold

Select the Patterns Tab and click on the colour Red

Click on OK

Click on OK again

The formula cell if Yes should have the background of the cell Red and Yes should be in bold.
If No, it should have a white background and is normal.

If the formulas are already in place, then select all the cells with the formulas in and carry out the procedures listed above.

Joined:
Dec 4, 2007
Messages:
2,895
Villian, I got the conditional format working. Thank you very much for all of your help.

8. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
You are very welcome.
It doesn't always require macro's. Excel provides a wealth of menu options and formulas that will do very nicely for most people.
Anyway, if you are happy with the result, could you please mark the thread as Solved. Not sure if you know how to do that, but if you dont', then scroll to the top of this thread and you should see an option called Thread Tools. Click on that and select Solved.

As Seen On