1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 Formula

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

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    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

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    See attached spreadsheet

    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:

  3. computerman29642

    computerman29642 Thread Starter

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

    The Villan

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

    computerman29642 Thread Starter

    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

    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.
     
  7. computerman29642

    computerman29642 Thread Starter

    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

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    You are very welcome. (y):)
    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.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704395

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice