[Solved] Multiple IF in one formula

[jureiga]

Hi Everyone,

I am a beginner with OpenOffice and I have difficulty adjusting especially with the formulas. I'm not an expert but would like to learn. I would like to create a formula where in if a value is >1 but <3 it would return to me the value 6 or if the value is >4 but <10 then it would return the value 8 and if the value is >11 but <25 return the value 25. I need all that in one formula. Can someone please help me.

[Zizi64]

I would like to create a formula where in if a value is >1 but <3 it would return to me the value 6 or if the value is >4 but <10 then it would return the value 8 and if the value is >11 but <25 return the value 25.

When the vaule is in A1 then the formula is:

=IF(AND(A1>1;A1<3);6;IF(AND(A1>4;A1<10);8;IF(AND(A1>11;A1<25);25;-100000)))

And what about that cases when A1=1 A1=3; A1=4, A1=10, A1=11 or A1>25 or A1<1?
I think you need use ">=" ond/or "<=" operators too.

[jureiga]

Thanks for the quick reply Zizi64 but I get a -100000 answer.

I just want to know though, why do we need to put the "-100000" at the end? What is the purpose?

And if I take out the "-100000" I get a FALSE value.

I'm sorry if I sound so dumb about this.

[Zizi64]

I ask again:
And what about that cases when A1=1 A1=3; A1=4, A1=10, A1=11 or A1>25 or A1<1?

In other words: What to be the result, when all of your conditions is not met?
That -100000 value is the result for that case.

[Zizi64]

See the working example:

Multi_if.ods

(7.96 KiB) Downloaded 160 times

[Villeroy]

Do not use IF for lookup in scales.
=LOOKUP(A1;{1;3;10;25};{6;8;25;0})
returns error #NA for all values in A1<1 and zero for all values >=25
It's easier to maintain and test with a helper range, say D1:E4:
1 6
3 8
10 25
25 0
and then:
=LOOKUP(A1;$D$1:$D$4;$E$1:$E$4)

[jureiga]

--->Zizi64: Sorry I didn't catch the last question thus I was not able to answer it, I was not able to look at it at that point of view so I did place an "=" sign like you advised/asked when you question me what will happen if it is equal to the amount. And thank you for that.

---> Villeroy: I am not that good with Open Office Cal and the terms you are using requires a lot of analizing in my end. But I will try to cope up with what you are saying. What do you mean for lookup in scales? Does it function like a VLOOKUP? or there's a difference? What are the differences? Does a VLOOKUP function differently in Open Office?

[Villeroy]

Column A contains arbitrary test values, high, low positive and negative integers and some fractions.
Columns B to H contain 8 different ways to do the same scale lookup. The results are all the same.
At column J you find the used lookup scales for the vertical and for the horizontal lookup variants.
Play with the lookup scales as well as with the test values. Your computer won't explode.

Theoretically, your lookup scales can have as many conditions as the sheet has rows. IF formulas are exhausted and unreadable with some dozends of conditions.

The VLOOKUP and HLOOKUP are the most straight-forward variants for this particular case.

=LOOKUP(A2;{1;3;10;25};{6;8;25;0}) in column H contains the lookup scales "hard coded" in the formula itself without using any of the helper scales. This is very bad style when you use such a formula in many cells because you need to take care that they all use the same values. You can't easily play with these formulas.

=VLOOKUP(search_value ; lookup_range ; column#)
As you can see in ANY of the 8 result columns, the result is the last value of column #2 where the search value is greater than or equal to the lookup scale in column #1. If search_value is already smaller than the first value in the lookup scale (<1 in this case), the formulas return error #NA (not availlable).
The lookup_range needs to be sorted ascending by its first column, otherwise you may get wrong results.
LOOKUP, MATCH, VLOOKUP and HLOOKUP are the same in Excel and many similar programs.

[jureiga]

Got it. And thanks a lot. I will however doodle more on Open Office to learn more about these things that all of you have taught me. Thank you very much. It's good to know that Open Office has helpful hands to help users right away.