Greater than equal to formula

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.

Thanks
Book8.xlsx
wcodyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

frankhelkCommented:
in cell B2:
   =IF(A2>12;1;IF(A2>9;0,75;IF(A2>3;0,5;0)))

example will follow, based on your excel sheet.
0
frankhelkCommented:
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.
example.xls
0
Steven HarrisPresidentCommented:
If you receive an error with the semicolons, use:

=IF(A2>12,1,IF(A2>9,0.75,IF(A2>3,0.5,0)))

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

=IF(A2>=12,1,IF(A2>=9,0.75,IF(A2>=3,0.5,0)))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

frankhelkCommented:
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.
0
Steven HarrisPresidentCommented:
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.
0
Rob HensonFinance AnalystCommented:
You could also do it with a small lookup table.

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

=vlookup(a1,$e$1:$f$4,2)

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

Thanks
Rob H
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.