How do I tell Calc (or Excell) to display one figure in one case, or another figure if another case?

Question

I need a formula that says "Look at cell B12.  If it's less than 6, then display 1.  If it's between 7 and 20, then display 1.5.  If it's between 20 and 30 then display 2." 

Is there a way to do this that I'm missing?

garbo7441 · Accepted Answer

Edit: I don't use Calc, so I am not familiar with its syntax. However, it does not appear the 'IF', 'And', or 'Between' are included in its built in functions.

Please see this site:

http://isthe.com/chongo/tech/comp/calc/calc-builtin.html

The formula is:

=IF(B12="","",IF(B12<6,1,
IF(AND(B12>6,B12<21),1.5,
IF(AND(B12>19,B12<31),2))))

If cell B12 is blank, the formula result is also blank, else it returns the value resulting from the formula.

Note: This formula should be entered all in one line.

expletive_xom · Answer

Garbo's formula logic is correct and will work.
its default OoCalc, so you need to replace all the commas with semi-colons when you write your formulas.
i know it wierd, but you get used to it.

edit:
or copy&paste this

=IF(I4="";""; IF(I4<=6;1;IF(I4<=20;1.5;2)))

and it should work.

GodAlliz · Answer

I haven't used that in forever, but I wanted to give you props for using OpenOffice.org

How do I tell Calc (or Excell) to display one figure in one case, or another figure if another case?

Related Questions

Trending Questions