Complicated formula help required

Hi everyone.

I'm looking to create a formula which to me is quite complicated.
In essence I need to carry out the following: -

If X = Y then result = 12
If X = Y plus or minus 2 then result = 11
If X = Y plus or minus 4 then result = 10
If X = Y plus or minus 6 then result = 9
If X = Y plus or minus 8 then result = 8
If X = Y plus or minus 10 then result = 7
If X = Y plus or minus 12 then result = 6
If X = Y plus or minus 14 then result = 5
If X = Y plus or minus 16 then result = 4
If X = Y plus or minus 18 then result = 3
If X = Y plus or minus 20 then result = 2
If X = Y plus or minus 22 then result = 1
else result = 0

I have a 'working out' spreadsheet if this helps
I figured I could have 12 different If cells, but can I use an if formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2) then result = 11

Any other ideas is also greatly appreciated
Thanks in advance
Recklaw

=IF(OR(MOD(ABS(A1-B1)/2,1)<>0,ABS(A1-B1)>22),0,12-ABS(A1-B1)/2)

--
Kind regards,

Niek Otten

"recklaw" <[email protected]> wrote in message
news:[email protected]...
>
> Hi everyone.
>
> I'm looking to create a formula which to me is quite complicated.
> In essence I need to carry out the following: -
>
> If X = Y then result = 12
> If X = Y plus or minus 2 then result = 11
> If X = Y plus or minus 4 then result = 10
> If X = Y plus or minus 6 then result = 9
> If X = Y plus or minus 8 then result = 8
> If X = Y plus or minus 10 then result = 7
> If X = Y plus or minus 12 then result = 6
> If X = Y plus or minus 14 then result = 5
> If X = Y plus or minus 16 then result = 4
> If X = Y plus or minus 18 then result = 3
> If X = Y plus or minus 20 then result = 2
> If X = Y plus or minus 22 then result = 1
> else result = 0
>
> I have a 'working out' spreadsheet if this helps
> I figured I could have 12 different If cells, but can I use an if
> formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2)
> then result = 11
>
> Any other ideas is also greatly appreciated
> Thanks in advance
> Recklaw
>
>
> --
> recklaw
> ------------------------------------------------------------------------
> recklaw's Profile: http://www.excelforum.com/member.php...o&userid=32469
> View this thread: http://www.excelforum.com/showthread...hreadid=524494
>

"recklaw" <[email protected]> wrote in
message news:[email protected]...
>
> Hi everyone.
>
> I'm looking to create a formula which to me is quite complicated.
> In essence I need to carry out the following: -
>
> If X = Y then result = 12
> If X = Y plus or minus 2 then result = 11
> If X = Y plus or minus 4 then result = 10
> If X = Y plus or minus 6 then result = 9
> If X = Y plus or minus 8 then result = 8
> If X = Y plus or minus 10 then result = 7
> If X = Y plus or minus 12 then result = 6
> If X = Y plus or minus 14 then result = 5
> If X = Y plus or minus 16 then result = 4
> If X = Y plus or minus 18 then result = 3
> If X = Y plus or minus 20 then result = 2
> If X = Y plus or minus 22 then result = 1
> else result = 0
>
> I have a 'working out' spreadsheet if this helps
> I figured I could have 12 different If cells, but can I use an if
> formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2)
> then result = 11
>
> Any other ideas is also greatly appreciated
> Thanks in advance

=MAX(12-(INT((ABS(X1-Y1)+1)/2)),0) ought to work if the inputs are all
integer.
If you want to test for non-integer values of (X-Y), then that could be
added if required.
--
David Biddulph

Assuming X is in A1 and Y in A2:

=MAX(12-(INT(ABS(B2-B1)/2)),0)

HTH
--
AP


"recklaw" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> Hi everyone.
>
> I'm looking to create a formula which to me is quite complicated.
> In essence I need to carry out the following: -
>
> If X = Y then result = 12
> If X = Y plus or minus 2 then result = 11
> If X = Y plus or minus 4 then result = 10
> If X = Y plus or minus 6 then result = 9
> If X = Y plus or minus 8 then result = 8
> If X = Y plus or minus 10 then result = 7
> If X = Y plus or minus 12 then result = 6
> If X = Y plus or minus 14 then result = 5
> If X = Y plus or minus 16 then result = 4
> If X = Y plus or minus 18 then result = 3
> If X = Y plus or minus 20 then result = 2
> If X = Y plus or minus 22 then result = 1
> else result = 0
>
> I have a 'working out' spreadsheet if this helps
> I figured I could have 12 different If cells, but can I use an if
> formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2)
> then result = 11
>
> Any other ideas is also greatly appreciated
> Thanks in advance
> Recklaw
>
>
> --
> recklaw
> ------------------------------------------------------------------------
> recklaw's Profile:
http://www.excelforum.com/member.php...o&userid=32469
> View this thread: http://www.excelforum.com/showthread...hreadid=524494
>

Try

=IF(A1<>"",MAX(0,12-CEILING(ABS(A1-B1)/2,1)),"")

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...

> "recklaw" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi everyone.
>>
>> I'm looking to create a formula which to me is quite complicated.
>> In essence I need to carry out the following: -
>>
>> If X = Y then result = 12
>> If X = Y plus or minus 2 then result = 11
>> If X = Y plus or minus 4 then result = 10
>> If X = Y plus or minus 6 then result = 9
>> If X = Y plus or minus 8 then result = 8
>> If X = Y plus or minus 10 then result = 7
>> If X = Y plus or minus 12 then result = 6
>> If X = Y plus or minus 14 then result = 5
>> If X = Y plus or minus 16 then result = 4
>> If X = Y plus or minus 18 then result = 3
>> If X = Y plus or minus 20 then result = 2
>> If X = Y plus or minus 22 then result = 1
>> else result = 0
>>
>> I have a 'working out' spreadsheet if this helps
>> I figured I could have 12 different If cells, but can I use an if
>> formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2)
>> then result = 11
>>
>> Any other ideas is also greatly appreciated
>> Thanks in advance

> =IF(OR(MOD(ABS(A1-B1)/2,1)<>0,ABS(A1-B1)>22),0,12-ABS(A1-B1)/2)

You may have missed the later part of the question, Nick? I think your
formula gives a zero result if the difference is an odd number. The OP
wanted an answer of 11 if the difference is +/-2 *or* +/- 1.
--
David Biddulph

You are right, thanks.
I think the OP's question has been answered already.

--
Kind regards,

Niek Otten

"David Biddulph" <[email protected]> wrote in message news:[email protected]...
> "Niek Otten" <[email protected]> wrote in message news:[email protected]...
>
>> "recklaw" <[email protected]> wrote in message
>> news:[email protected]...
>>>
>>> Hi everyone.
>>>
>>> I'm looking to create a formula which to me is quite complicated.
>>> In essence I need to carry out the following: -
>>>
>>> If X = Y then result = 12
>>> If X = Y plus or minus 2 then result = 11
>>> If X = Y plus or minus 4 then result = 10
>>> If X = Y plus or minus 6 then result = 9
>>> If X = Y plus or minus 8 then result = 8
>>> If X = Y plus or minus 10 then result = 7
>>> If X = Y plus or minus 12 then result = 6
>>> If X = Y plus or minus 14 then result = 5
>>> If X = Y plus or minus 16 then result = 4
>>> If X = Y plus or minus 18 then result = 3
>>> If X = Y plus or minus 20 then result = 2
>>> If X = Y plus or minus 22 then result = 1
>>> else result = 0
>>>
>>> I have a 'working out' spreadsheet if this helps
>>> I figured I could have 12 different If cells, but can I use an if
>>> formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2)
>>> then result = 11
>>>
>>> Any other ideas is also greatly appreciated
>>> Thanks in advance
>
>> =IF(OR(MOD(ABS(A1-B1)/2,1)<>0,ABS(A1-B1)>22),0,12-ABS(A1-B1)/2)
>
> You may have missed the later part of the question, Nick? I think your formula gives a zero result if the difference is an odd
> number. The OP wanted an answer of 11 if the difference is +/-2 *or* +/- 1.
> --
> David Biddulph
>

I am reading the question differently from Niek
My take is:
x is within y +- 2 result is 11
x is within y +- 4 result is 10
This seems to work =INT(12-ABS(x-y)/2)*(ABS(x-y)<24)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"recklaw" <[email protected]> wrote in
message news:[email protected]...
>
> Hi everyone.
>
> I'm looking to create a formula which to me is quite complicated.
> In essence I need to carry out the following: -
>
> If X = Y then result = 12
> If X = Y plus or minus 2 then result = 11
> If X = Y plus or minus 4 then result = 10
> If X = Y plus or minus 6 then result = 9
> If X = Y plus or minus 8 then result = 8
> If X = Y plus or minus 10 then result = 7
> If X = Y plus or minus 12 then result = 6
> If X = Y plus or minus 14 then result = 5
> If X = Y plus or minus 16 then result = 4
> If X = Y plus or minus 18 then result = 3
> If X = Y plus or minus 20 then result = 2
> If X = Y plus or minus 22 then result = 1
> else result = 0
>
> I have a 'working out' spreadsheet if this helps
> I figured I could have 12 different If cells, but can I use an if
> formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2)
> then result = 11
>
> Any other ideas is also greatly appreciated
> Thanks in advance
> Recklaw
>
>
> --
> recklaw
> ------------------------------------------------------------------------
> recklaw's Profile:
> http://www.excelforum.com/member.php...o&userid=32469
> View this thread: http://www.excelforum.com/showthread...hreadid=524494
>

Hi,


Assuming that the the numbers are in A1 and B1 (and also that the
differences between them can only have integral values as implied in your
post) one of the following formulas should work.

If the result should be zero when X is equal to Y plus or minus an odd
number (as implied by the 13 possibilities you have listed in the top part of
your post), use the formula,

=IF(ISEVEN(A1-B1),12-ABS(A1-B1)/2,0)

However, the bottom section of your post, i.e., "I have a 'working out'
spreadsheet if this helps I figured I could have 12 different If cells, but
can I use an if formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or
(Y+1) or (Y+2) then result = 11" conflicts with the top section. If the
bottom section were correct, the top section should read,

If X = Y then result = 12
If X = Y plus or minus 1 or plus or minus 2 then result = 11,
..
..
..
If X = Y plus or minus 21 or plus or minus 22 then result = 1
else result = 0

If that is the case, use the formula,

=IF(ABS(A1-B1)<=22,12-ROUNDUP(ABS(A1-B1)/2,0),0)

Regards,
B. R. Ramachandran

"recklaw" wrote:

>
> Hi everyone.
>
> I'm looking to create a formula which to me is quite complicated.
> In essence I need to carry out the following: -
>
> If X = Y then result = 12
> If X = Y plus or minus 2 then result = 11
> If X = Y plus or minus 4 then result = 10
> If X = Y plus or minus 6 then result = 9
> If X = Y plus or minus 8 then result = 8
> If X = Y plus or minus 10 then result = 7
> If X = Y plus or minus 12 then result = 6
> If X = Y plus or minus 14 then result = 5
> If X = Y plus or minus 16 then result = 4
> If X = Y plus or minus 18 then result = 3
> If X = Y plus or minus 20 then result = 2
> If X = Y plus or minus 22 then result = 1
> else result = 0
>
> I have a 'working out' spreadsheet if this helps
> I figured I could have 12 different If cells, but can I use an if
> formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2)
> then result = 11
>
> Any other ideas is also greatly appreciated
> Thanks in advance
> Recklaw
>
>
> --
> recklaw
> ------------------------------------------------------------------------
> recklaw's Profile: http://www.excelforum.com/member.php...o&userid=32469
> View this thread: http://www.excelforum.com/showthread...hreadid=524494
>
>

=MAX(12-CEILING(ABS(B1-A1)/2,1),0)

you need the CEILING function instead of INT. Your function will return 12
for differences of 1 and below, and will return 11 for difference of 3 to 1,
10 for 5 to 3, etc.

"Ardus Petus" wrote:

> Assuming X is in A1 and Y in A2:
>
> =MAX(12-(INT(ABS(B2-B1)/2)),0)
>
> HTH
> --
> AP
>
>
> "recklaw" <[email protected]> a écrit
> dans le message de
> news:[email protected]...
> >
> > Hi everyone.
> >
> > I'm looking to create a formula which to me is quite complicated.
> > In essence I need to carry out the following: -
> >
> > If X = Y then result = 12
> > If X = Y plus or minus 2 then result = 11
> > If X = Y plus or minus 4 then result = 10
> > If X = Y plus or minus 6 then result = 9
> > If X = Y plus or minus 8 then result = 8
> > If X = Y plus or minus 10 then result = 7
> > If X = Y plus or minus 12 then result = 6
> > If X = Y plus or minus 14 then result = 5
> > If X = Y plus or minus 16 then result = 4
> > If X = Y plus or minus 18 then result = 3
> > If X = Y plus or minus 20 then result = 2
> > If X = Y plus or minus 22 then result = 1
> > else result = 0
> >
> > I have a 'working out' spreadsheet if this helps
> > I figured I could have 12 different If cells, but can I use an if
> > formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y+1) or (Y+2)
> > then result = 11
> >
> > Any other ideas is also greatly appreciated
> > Thanks in advance
> > Recklaw
> >
> >
> > --
> > recklaw
> > ------------------------------------------------------------------------
> > recklaw's Profile:
> http://www.excelforum.com/member.php...o&userid=32469
> > View this thread: http://www.excelforum.com/showthread...hreadid=524494
> >
>
>
>

Just wanted to thank everyone.

=INT(12-ABS(x-y)/2)*(ABS(x-y)<24) worked a treat

Just need to go and understand it now ;o)