Help with "IF" Formula

[Guest]

I need help in writing a If formula (at least I think that is what I need) in
excel. This is what I need:
If (a1)cell=6, then (g1)cell-1, IF(a1)cell=5.5, then cell-2, and so on down
to if cell=3, then cell-7, if false for all then enter (g1)cell. I need to
do this also from cell=7 then cell+1 up to cell=8.5, then +4. Can I have
that many if statements? I can get it to work with one no more. Example
=IF(F3=6,(J3-1),J3), this works. this doesn't
=IF(F3=6,(J3-1),J3)=IF(F3=5.5,(J3-2),J3) Thanks

 

[Ian]

You can do this without a bunch of IFs as this is a logical progression. If
I understand correctly, you want to offset the value in J3 by 1 for each 0.5
variation in F3 from 6.5. You can do this with J3+((F3-6.5)*2). You appear
to want a false statement returned if F3 is not between 3 & 8.5 and you can
test for this with AND(F3>=3,F3<=8.5).

Putting this into a formula we get

=IF(AND(F3>=3,F3<=8.5),J3+((F3-6.5)*2),"false statement")

You wouldn't have been able to do it the way you were trying because you can
only have 7 nested IFs.

 

[Guest]

Thanks Ian, but I am not quite sure if this is exactly what I am wanting. I
will try to explain better. You are correct that I want to add 1 per.5 but
that is if the numbers are between 2 and 6. If the number is between 6.5 and
9 I need to subtract 1 for every .5. Does this make sense. If it is 6.5
then it stays the value. I understand what I need to do now better so I will
work on it but if you can help I will greatly appreciate it.

 

[Ian]

Hi PH

Sorry for the delay in replying but I've been on holiday.

I got your offset the wrong way round. My original formula added for higher
numbers.

If I understand correctly, F3 at 7 should give J3-1 and at 6 should be J3+1.
You also need the operating limits to be 2 and 9

Try this:

=IF(AND(F3>=2,F3<=9),J3+((6.5-F3)*2),"false statement")

All I have done is altered the limits in the first part of the formula (the
AND argument) and reversed the 6.5 and F3 the the next part.