software

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: www.excelforum.com/member.php...oamp;userid=32469
View this thread: www.excelforum.com/showthread...hreadid=524494=IF(OR(MOD(ABS(A1-B1)/2,1)lt;gt;0,ABS(A1-B1)gt;22),0,12-ABS(A1-B1)/2)

--
Kind regards,

Niek Otten

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

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

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

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

gt; =IF(OR(MOD(ABS(A1-B1)/2,1)lt;gt;0,ABS(A1-B1)gt;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

Try

=IF(A1lt;gt;quot;quot;,MAX(0,12-CEILING(ABS(A1-B1)/2,1)),quot;quot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=524494You are right, thanks.
I think the OP's question has been answered already.

--
Kind regards,

Niek Otten

quot;David Biddulphquot; gt; wrote in message ...
gt; quot;Niek Ottenquot; gt; wrote in message ...
gt;
gt;gt; quot;recklawquot; gt; wrote in message
gt;gt; ...
gt;gt;gt;
gt;gt;gt; Hi everyone.
gt;gt;gt;
gt;gt;gt; I'm looking to create a formula which to me is quite complicated.
gt;gt;gt; In essence I need to carry out the following: -
gt;gt;gt;
gt;gt;gt; If X = Y then result = 12
gt;gt;gt; If X = Y plus or minus 2 then result = 11
gt;gt;gt; If X = Y plus or minus 4 then result = 10
gt;gt;gt; If X = Y plus or minus 6 then result = 9
gt;gt;gt; If X = Y plus or minus 8 then result = 8
gt;gt;gt; If X = Y plus or minus 10 then result = 7
gt;gt;gt; If X = Y plus or minus 12 then result = 6
gt;gt;gt; If X = Y plus or minus 14 then result = 5
gt;gt;gt; If X = Y plus or minus 16 then result = 4
gt;gt;gt; If X = Y plus or minus 18 then result = 3
gt;gt;gt; If X = Y plus or minus 20 then result = 2
gt;gt;gt; If X = Y plus or minus 22 then result = 1
gt;gt;gt; else result = 0
gt;gt;gt;
gt;gt;gt; I have a 'working out' spreadsheet if this helps
gt;gt;gt; I figured I could have 12 different If cells, but can I use an if
gt;gt;gt; formula with 4 Or formulas? i.e. If (X=(Y-1) or (Y-2) or (Y 1) or (Y 2)
gt;gt;gt; then result = 11
gt;gt;gt;
gt;gt;gt; Any other ideas is also greatly appreciated
gt;gt;gt; Thanks in advance
gt;
gt;gt; =IF(OR(MOD(ABS(A1-B1)/2,1)lt;gt;0,ABS(A1-B1)gt;22),0,12-ABS(A1-B1)/2)
gt;
gt; 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
gt; number. The OP wanted an answer of 11 if the difference is /-2 *or* /- 1.
gt; --
gt; David Biddulph
gt;
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)lt;24)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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

Regards,
B. R. Ramachandran

quot;recklawquot; wrote:

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

=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.

quot;Ardus Petusquot; wrote:

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