• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

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
0
wcody
Asked:
wcody
  • 3
  • 2
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 HensonIT & Database AssistantCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now