"if" formula

  • Thread starter Thread starter Neuther
  • Start date Start date
N

Neuther

If i am using a if formula i know i can go

"=IF(AN3<=86,"30","20")"

I get how that work's but what if i want to add a 3rd value.

i want something like this

=IF(AN3<=86,"30",<=173,"20",<=206,"10")

except that doesn't work, i want it to say 30 if it's 1-86, 20 if it'
87-173, and 10 if it's 174-206.

Thanx,

Neuthe
 
Hi Neuther,

you have to nest your IF statements:
=IF(AN3<="86","30",IF(AN3>=174,"10","20"))

Frank
 
Neuther,

A few things,

Numbers don't need to be in quotes unless you want them
returned as text values and not numbers.

You have to nest your if's
=IF(AN3<=86, 30, IF(AN3<=173, 20, IF(AN3<=207, 10, ">206")))

You can only have 7 nested if's. If you need more you might have
to use a lookup table.

Dan E
 
The following formula will return "Invalid" for numbers outside the
range of 1-206:


=IF(OR(AN3<1,AN3>206),"Invalid",IF(AN3<=86,30,IF(AN3<=173,20,IF(AN3<=206,10))))

Also, numbers shouldn't be enclosed in quotation marks, or they'll be
returned as text, instead of numbers. That might cause other
calculations to return unexpected results.
 
thanx so much, i was messing w/ that forever and i couldn't get it.
Thanx for getting rid of my headac
 
Can I use this formula to transfer a number to another spreadsheet?

say if I am on another spreadsheet use
=Title2!if(an3>173,206,if(an3>86,173,if(an3>1,20,"")))

Do you think this would work.. I need to transfer over the last value
entered in a column of a previous page.

Thanks

Tony

Don Guillett said:
try
=if(an3>173,206,if(an3>86,173,if(an3>1,20,"")))
Click to expand...
 
Don't see how it relates to the question at hand but try this. Change sheet
& column to suit.

=INDEX(Sheet10!A:A,MATCH(999999999,Sheet10!A:A))

--
Don Guillett
SalesAid Software
(e-mail address removed)
1992 Toyota Xcab said:
Can I use this formula to transfer a number to another spreadsheet?

say if I am on another spreadsheet use
=Title2!if(an3>173,206,if(an3>86,173,if(an3>1,20,"")))

Do you think this would work.. I need to transfer over the last value
entered in a column of a previous page.

Thanks

Tony
Click to expand...
 
Hi Neuther

ie with the number 87

=IF(OR(AN3<1,AN3>206),"Invalid",IF(AN3<=86,30,IF(AN3<=173,20,IF(AN3<=206,10)
)))
answer #¿NOUM?

=IF(AN3<="86","30",IF(AN3>=174,"10","20"))
answer 30

=if(an3>173,206,if(an3>86,173,if(an3>1,20,"")))
answer 173


try this other
=IF(AND(AN3>=1;AN3<=86);"30";IF(AND(AN3>=87;AN3<=173);"20";IF(AND(AN3>=174;A
N3<=206);"10";"out of range")))

Regards
 
Thanks for the answer... To be a little more clear.. How with the formula
you provided giving me an answer greater than zero, how could or would you
copy that cell to another? Does it need a statement "If True then
{Column:number}?


Tony

blanco said:
Hi Neuther

ie with the number 87
Click to expand...
 
Hi Tony

I understand if this is what you find

=IF(AND(Title2!AN3>=1;Title2!AN3<=86);"30";IF(AND(Title2!AN3>=87;Title2!AN3<
=173);"20";IF(AND(Title2!AN3>=174;Title2!AN3<=206);"10";"out of range")))

if not, send me a copy of your sheet with the problem to blanco_esARROBA...
(see my head)

Regards
 
Blanco,

I can send you a page on Tuesday... But until then.. I need to find a
number in a column starting from the bottom greater than zero than copy that
particular cell's number. The cell is a formula from the row so I can't
just copy down.

Basically what I am looking for is this

12
13
14
15
15
0
0
0

if that was a column to find the first number above 0 going up then transfer
that number to another page and cell.

Hope I didn't confuse you more than I confused myself.. Thanks..


Tony
 
I realized that I didn't give you all of the answer.
match found the cell and index told you what is in it.

=INDEX(E:E,MATCH(0,E:E,-1)-1,1)
 
Back
Top