"IF" function probelms

[Jinsem]

Ok, so I thought I had this down, and it appears you can't lear
everything in 1 week

Here is the forumla in question


=IF($O3>1,$C$17,IF($O3>1.3,$C$18,IF($O3>2.1,$C$19,IF($O3>3.1,$C$20,$C$21))))

Now what it's doing is looking at another cell in "Number" format wit
2 decimals (the decimals are for me too see, I'm hoping this isn'
causing a problem. It then takes that number and then returns 1 of
numbers. It seems to only want to return the lowest for some reason.

Here's the data set:

C17 - .06007
C18 - .06007
C19 - .06932
C20 - .07839
C21 - .09422

So let's say my number (in O3) is 1.6 I need it to return ".06932".
If the number is .23 I need it to return ".06007". I'm still new t
all these functions and what I've done is taken a percentage and made
hidden column with the number changed into a 2digit decimal using th
"number" format. If there's a way to not do that, I'd love to know.

Any help will be appreciated. Can't wait till this is done

Jinse

 

[filky]

Just a thought. Try replacing > with <
It's satisfying the first criteria and answering as you asked it to

 

[Randall Roberts]

The reason it gives you the wrong answer is that any number in the
cell is >1

The if statements checs for a number finds three which is over 1 and
uses the first if for your answer

changed your formula so the first if is the highest value you want ti
check for then if the number IS NOT above that value it will do the
next test until it finds the correct value

Randall

 

[Beverley]

What your formula currently says is:

if the value of o3 is greater than 1, get the value from c17.
if it isn't greater than 1, then go on to the next part which now checks to
see if o3 is greater than 1.3 (which it won't be because only numbers
smaller than 1 got here in the first place)

So it will never get past that, because the first part is true.

If you want to keep it in this format, put the biggest number first. Or you
might want to check out the HLOOKUP and VLOOKUP functions which are a bit
tricky at first, but cool once you get the hang of them.

Beverley

 

[Jinsem]

It irks me to no end when something is as simple as this...

I took all the advice and used it, and it works perfectly. I eve
double checked it before I posed

This is what I used.

=IF($Q3>3,$C$21,IF($Q3>2,$C$20,IF($Q3>1.3,$C$19,IF($Q3>1,$C$18,$C$17))))

I'm unsure why y pacman theory doesn't work, but as long as it works
guess it's fine. Although it's nice to know why LOL.

Thanks to you all. Until my next question, take care

Jinse

 

[Ron Rosenfeld]

Ok, so I thought I had this down, and it appears you can't learn
everything in 1 week

Here is the forumla in question


=IF($O3>1,$C$17,IF($O3>1.3,$C$18,IF($O3>2.1,$C$19,IF($O3>3.1,$C$20,$C$21))))

Now what it's doing is looking at another cell in "Number" format with
2 decimals (the decimals are for me too see, I'm hoping this isn't
causing a problem. It then takes that number and then returns 1 of 5
numbers. It seems to only want to return the lowest for some reason.

Here's the data set:

C17 - .06007
C18 - .06007
C19 - .06932
C20 - .07839
C21 - .09422

So let's say my number (in O3) is 1.6 I need it to return ".06932".
If the number is .23 I need it to return ".06007". I'm still new to
all these functions and what I've done is taken a percentage and made a
hidden column with the number changed into a 2digit decimal using the
"number" format. If there's a way to not do that, I'd love to know.

Any help will be appreciated. Can't wait till this is done

Jinsem

Perhaps something like:

=OFFSET($C$16,MATCH($O3,{0,1,1.3,2.1,3.1}),0)


--ron