asked on

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

Frank Helk

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.

Frank Helk

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

ASKER CERTIFIED SOLUTION

Steven Harris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Helk

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.

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.

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

=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