|
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 -- recklaw ------------------------------------------------------------------------ recklaw's Profile: http://www.excelforum.com/member.php...o&userid=32469 View this thread: http://www.excelforum.com/showthread...hreadid=524494 |
Complicated formula help required
=IF(OR(MOD(ABS(A1-B1)/2,1)<0,ABS(A1-B1)22),0,12-ABS(A1-B1)/2)
-- Kind regards, Niek Otten "recklaw" wrote in message ... 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 |
Complicated formula help required
Assuming X is in A1 and Y in A2:
=MAX(12-(INT(ABS(B2-B1)/2)),0) HTH -- AP "recklaw" a écrit dans le message de ... 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 |
Complicated formula help required
"recklaw" wrote in
message ... 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 |
Complicated formula help required
"Niek Otten" wrote in message
... "recklaw" wrote in message ... 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 |
Complicated formula help required
Try =IF(A1<"",MAX(0,12-CEILING(ABS(A1-B1)/2,1)),"") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=524494 |
Complicated formula help required
You are right, thanks.
I think the OP's question has been answered already. -- Kind regards, Niek Otten "David Biddulph" wrote in message ... "Niek Otten" wrote in message ... "recklaw" wrote in message ... 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 |
Complicated formula help required
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" wrote in message ... 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 |
Complicated formula help required
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 |
Complicated formula help required
=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" a écrit dans le message de ... 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 |
Complicated formula help required
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) -- recklaw ------------------------------------------------------------------------ recklaw's Profile: http://www.excelforum.com/member.php...o&userid=32469 View this thread: http://www.excelforum.com/showthread...hreadid=524494 |
| All times are GMT +1. The time now is 08:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com