Complicated formula help required

[recklaw]

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 i
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
Reckla

 

[Niek Otten]

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

 

[Ardus Petus]

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

 

[David Biddulph]

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]

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

 

[daddylonglegs]

Try

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

 

[Niek Otten]

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

 

[Bernard Liengme]

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)

 

[Guest]

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

 

[Guest]

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

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

 

[recklaw]

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