Where is my error?

[Harvey Waxman]

=IF(OR(B48=1,8,23),1,IF(OR(B48=21,22,24,25,26,33,98,AND(B48>1,B48<16)),2,IF(B48=
16,3,IF(OR(B48=17,18,19,AND(B48>40,B48<98)),4,"x"))))

Can anyone see where my mistake is? This returns a "1" no matter what is in
B48.


Thanks

--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[Bernard Rey]

Harvey Waxman wrote :

=IF(OR(B48=1,8,23),1,IF(OR(B48=21,22,24,25,26,33,98,AND(B48>1,B48<16)),2,
IF(B48=16,3,IF(OR(B48=17,18,19,AND(B48>40,B48<98)),4,"x"))))

Not too clear what things should be. But If you take the very first test
(the one that returns "1", whatever you do) separately, you'll see it always
returns 1. That's not surprising, since "B48=1" will return true when the
value in B48 is 1, but "8" by itself is no test, so it will always return
true, it should read "B48=8" to be a test...

You could write it "=OR(B48=1,B48=8,B48=23)" instead if you really mean that
B48 can take one of the three values.

"=IF(OR(B48=1,B48=8,B48=23),1,IF(OR(B48=21,B48=22,B48=24,B48=25,B48=26,B48=3
3,B48=98,AND(B48>1,B48<16)),2,IF(B48=16,3,IF(OR(B48=17,B48=18,B48=19,AND(B48

40,B48<98)),4,"x"))))" should be closer to the formula you're looking for.

 

[Harvey Waxman]

=IF(OR(B48=1,B48=8,B48=23),1,IF(OR(B48=21,B48=22,B48=24,B48=25,B48=26,B48=33,
B48=98,AND(B48>1,B48<16)),2,IF(B48=16,3,IF(OR(B48=17,B48=18,B48=19,AND(B48>
40,B48<98)),4,"x")))) should be closer to the formula you're looking for.

I wasn't sure about having to repeat the cell references for every test.

I copied the above but received an error message. It sure looks ok to me.



--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[Bernard Rey]

Harvey Waxman wrote :

I wasn't sure about having to repeat the cell references for every test.

Yes, you have to.

I copied the above but received an error message. It sure looks ok to me.

Copying and pasting from a NG message is not always the thing to do, as
there may be some carriage returns in the middle of it. Maybe you can paste
it to TextEdit first, so you can see end delete these unwanted guests.

Or you can simply modify your original formula, adding "B48=" everywhere
it's needed (copy/paste works for that)...

 

[JE McGimpsey]

=IF(OR(B48=1,8,23),1,IF(OR(B48=21,22,24,25,26,33,98,AND(B48>1,B48<16))
,2,IF(B48= 16,3,IF(OR(B48=17,18,19,AND(B48>40,B48<98)),4,"x"))))

Can anyone see where my mistake is? This returns a "1" no matter
what is in B48.

One problem is that you're comparing an cell to an array of values
without creating an array, e.g.:

OR(B48={1,8,23}...

so your formula becomes:

=IF(OR(B48={1,8,23}),1,IF(OR(B48={21,22,24,25,26,33,98},AND(B48>1,B48<16)
),2,IF(B48=16,3,IF(OR(B48={17,18,19},AND(B48>40,B48<98)),4,"x"))))

I don't know what the formula is for, but if there's the least chance
that the values will change, I would recommend putting the values in a
list, e.g., say, on Sheet2:

A
1
2
2
2
2
2
2
1
2
2
2
2
2
2
2
3
4
4
4
x
...

Then use this formula:

=INDEX(Sheet2!A:A,MIN(B48,99))

 

[Harvey Waxman]

I copied the above but received an error message. It sure looks ok to me.

Copying and pasting from a NG message is not always the thing to do, as
there may be some carriage returns in the middle of it. Maybe you can paste
it to TextEdit first, so you can see end delete these unwanted guests.[/QUOTE]

That was apparently the problem although I didn't see any extras. Pasting
first to Text Edit then from there to Excel solved the problem..


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[Harvey Waxman]

One problem is that you're comparing an cell to an array of values
without creating an array, e.g.:

OR(B48={1,8,23}...

so your formula becomes:

=IF(OR(B48={1,8,23}),1,IF(OR(B48={21,22,24,25,26,33,98},AND(B48>1,B48<16)
),2,IF(B48=16,3,IF(OR(B48={17,18,19},AND(B48>40,B48<98)),4,"x"))))

Very good solution. This too solved the problem. I don't know where the use
of braces is explained but I think I can retain that bit iof information for at
least a little while.

Thanks once again for the great help from the group


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

I don't know where the use
of braces is explained but I think I can retain that bit iof information for
at
least a little while

Check out "About array formulas and array constants" in XL Help.