IF Statement problem

[Guest]

Hi
I have got a problem with an If Statement that I am trying to create.
Cell A1 is a variable number (but I will use -24, for the example)

A3 = -9
A4 = -15
A5 = -3
A6 = -11
A7 = -7
A8 = -1
A9 = -13
A10 = -5
A11 = -17
A12 = -4
A13 = -6
A14 = -14
A15 = -12
A16 = -10
A17 = -2
A18 = -16
A19 = -8
A20 = -18

The formula that I need to create is basically the following that I need to
nest together.
If Cell A1 is higher than -18 (this is lower in negative form, -19 etc)
=IF(A1<-18,IF(SUM(A3-18)=>A1,2,IF(SUM(A3-18)<A1,0))
If Cell A1 is Lower than -18 (this is higher in negative form, -17 etc)
=IF(A1>-18,=IF(A3=<A1,1,IF(A3>A1,0))

The final pieces of the Jigsaw are:
If Cell A1 is equals to 1.
=IF(And(A1=1,A3=-18,-1,0)
If Cell A1 is equals to 2.
=IF(And(A1=2,A3=-18 or -17,-1,0)
I hope that this makes sense

Thanks

 

[Guest]

First, don't multi-post!

Can't your first two IFs be reduced to:

=IF(A1<-18,IF(A3-18>=A1,2,0),IF(A3<=A1,1,0))

??

and are your second pair of tests in different cell to the above?

Confused!

 

[Guest]

Toppers

I didn't Multi-Post!! I clicked on Post and I got an error, so I went back
and tried to post it again (3 times), I have deleted the other posts.

The first two parts have different criteria. The first is if it is greater
than -18 and the second if it is less than -18. The formulas are also
different. If A1 is greater than -18 the formula needs to subract 18 from
Cell A3, If A1 is less than -18 then the formula doesn't need to do this.

Basically I need to put these formulas together so that if A1 is greater
than -18 it follows the first formula and if A1 is less or equals to -18 then
it follows the second part.

=IF(A1<"-18",IF(SUM(A10-18)>=A1,2,IF(SUM(A10-18)<A1,0))
=IF(A1>="-18",IF(A3<=A1,1,IF(A3>A1,0))

I then need (somehow) to tag the following formulas onto the end of this so
that the output score is in the same cell.

=IF(A1=1and A3=-18,-1,0)
=IF(A1=2 and A3=-18 or -17,-1,0)

I am sorry if this doesn't make sense, I have been on this all afternoon!!

Thanks

 

[Guest]

First, check out the formula I gave you which does combine your first two.

In which column is this formula to be placed?

I am confused by the second set of formulae because if A1 (do you mean A1?)
is 1 or 2, then it will satisfy this formula:

=IF(A1>-18,=IF(A3=<A1,1,IF(A3>A1,0))

So hopefully you can see WHY I am confused!