Greater than equal to formula

Posted on 2014-08-13
Last Modified: 2014-08-14
In column a I have credits listed, I would like to write a formula that if the number in column a is greater than 12, 1 will display in column b if the number is 9 or greater .75 will display  and less than 9 but greater than 3 .5 display less than 3 0 displays.

Question by:wcody
    LVL 13

    Expert Comment

    in cell B2:

    example will follow, based on your excel sheet.
    LVL 13

    Expert Comment

    and here the example.

    BTW there might be a more elegant way, possibly with a macro function, but the distribution of values is a little bit non-linear which would make it complex to fit into a math expression.

    A macro function would simply reflect the logic in the formula above, using some VBA construct like if-elseif-elseif-else.
    LVL 18

    Accepted Solution

    If you receive an error with the semicolons, use:


    With this example, 12 would display as ".075".  If this is supposed to be greater than or equal to, you would need to use:

    LVL 13

    Expert Comment

    About the semicolons (the're the usual separators in german language Excel): If you check the formula in the example, it will perfectly fit your national formula notation.
    LVL 18

    Expert Comment

    by:Steven Harris
    When submitted through a workbook, Excel should automatically adjust them.

    When typing in a formula, the semicolons will be rejected and error the formula.

    Just an FYI, nothing more.
    LVL 31

    Expert Comment

    by:Rob Henson
    You could also do it with a small lookup table.

    Col e     Col f
    0           0
    3           0.5
    9           0.75
    12         1


    I have deliberately left the fourth parameter blank so that it finds a close match without going greater than.

    Rob H

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now