Having trouble with an IF function

Question

Level 1

0 points

Having trouble with an IF function

Good afternoon,

I'm trying to use an IF function to return different values of one parameter when given another input parameter. There are three possible output values which are dependant on the size of the original. Below is my attempt at making a working IF function to achieve this:

=IF(B2<8,0.95,IF(8<=B2<=14,1,IF(14<B2,1.05,)))

I am getting an error saying that "you cannot compare a Boolean with a number because their data types are different"

This is what has stumped me. It may well be due to my own lack of knowledge of Boolean algebra, but I would greatly appreciate any assistance you may be able to offer.

Kind regards

MacBook Pro 15", Mac OS X (10.5.1)

Posted on Mar 22, 2011 9:27 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,825 points

Mar 22, 2011 10:17 AM in response to cave.g

Do you know that reading *_iWork Formulas and Functions User Guide_* is useful

If there is a function named AND, it's because 8<=B2<=14 is not allowed.
I wish to add that checking that 8<=B2 when the value passed the first test B2<8 is a bit absurd.

Same comment for your third IF.

In fact your formula must be :

=IF(B2<8,0.95,IF(B2<=14,1,1.05))
or

=IF(B2<8,0.95,IF(B2>14,1.05,1))

Yvan KOENIG (VALLAURIS, France) mardi 22 mars 2011 18:16:57

Reply

Answer 2

cave.g Author

Level 1

0 points

Mar 22, 2011 10:43 AM in response to KOENIG Yvan

Many thanks Yvan, the introduction of an AND function has solved the problem. The duplication of the <8 was simply a copying error and not present in the actual formula.

I have tracked down and saved the user guide for future reference.

Thanks again,

George

Reply

Answer 3

Mar 22, 2011 11:01 AM in response to cave.g

George,

Do note that Yvan has given you 2 solutions that avoid the need for and AND function.

I had been doing the same exercise, but Yvan posted ahead of me, so I did't post mine, which was identical to Yvan's first expression.

Jerry

Reply

Answer 4

cave.g Author

Level 1

0 points

Mar 22, 2011 11:15 AM in response to Jerrold Green1

Thanks Jerry, I used the more complicated expression with the AND function just to test my understanding.

Thanks again to you both, this has been frustrating me for some time.

George

Reply

Answer 5

KOENIG Yvan

Level 8

41,825 points

Mar 22, 2011 11:36 AM in response to Jerrold Green1

Hi Jerrold

You know that I'm lazy so, my own choice is the second one because it requires less typing and as it use a simple test, I guess that it will run faster.

Yvan KOENIG (VALLAURIS, France) mardi 22 mars 2011 19:36:18

Reply

Answer 6

Mar 22, 2011 1:04 PM in response to KOENIG Yvan

Hi Yvan,

I hadn't thought about the execution efficiency. I was trying to stay as close to George's expression as I could and didn't think about the alternatives.

Jerry

Reply