Excel Formulas

[Brett Walters]

I am trying to work out a formula where I can refer to a
number range in a cell and be given a value for it.

eg: if cell A1 is >=840 or <450 display 1, if A1 is >=720
and <=839 display 2, if A1 is >=575 and <=719 display 3,
if A1 is <=505 and <=574 display 4 and if A1 is >=450 and
<=504 display 5.

From what I can tell, not the normal type of formula that
people use. I have tried using IF but I get more than 1
value returned in the cell. I have used the & separator on
10 different IF formulas so the formula is valid but get
the same issue.

If I had 200 in A1 it meets all the conditions so I get
12345, rather than just getting a 1, which is what I am
looking for.

If anyone has any ideas how I can return just the 1 value
in a cell if it meets one of the conditions

 

[JE McGimpsey]

One way:

=IF(OR(A1>=840,A1<450),1,LOOKUP(A1,{450,5;505,4;575,3;720,2}))

 

[Brett Walters]

Thanks alot, kind of worked it out from another post I
saw. End up formula I used was

=IF(AND(F17>=720,F17<=839),2,IF(AND(F17>=575,F17<=719),3,IF
(AND(F17>=505,F17<=574),4,IF(AND(F17>=450,F17<=504),5,IF
(AND(F17<450),1,IF(AND(F17>=840),1,))))))

Works ok.

 

[SlimPickins]

I think I could use something similar to this. But I would
be workink with years.
If A1 through A700 is between 4.5 to 5 years past copy
to "sheet 4".

Posted this question as "Date Question" on 05-03-04
@2.27pm.
There are more details in that post as to what I need.
Had a reply to it, but the client does not want to have to
remember to run the macro. Basicly he wants this to happen
automaticly.

Slim