Problem With Formula

[The Shadowman]

=IF(U8<34,T8+1,IF(U8="dnp",T8,IF(U8=34,T8,IF(U8=35,T8,IF(U8=36,T8,IF(U8=37,T8,IF(U8=38,T8-1,IF(U8=39,T8-1,IF(U8=40,T8-1,IF(U8>40,T8-2,IF($CH8="shots back blocked",T8)))))))))))

The formula above is in Column "V" and should respond to information from columns "T" and "U" only. However, when the data in T and U is provided it changes columns R P & N in error. I can not work out for the life of me why this is happening. R P and N should not be affected.

Is there an error in the formula? If there is I can't find it.

Please help
Thanks
Robert

 

[awoohaw]

Does it change when you change values in those columns? Or does it change when you drag the formula to other cells?

 

[The Shadowman]

The Image may help

Does it change when you change values in those columns? Or does it change when you drag the formula to other cells?

In this case, placing Information in U should should make V respond accordingly. So to answer the question It's changing the values in U that R P and N are responding to. The formulars in R P and N are the same as T and V

 

[awoohaw]

That a much too busy graphic for me to comprehend. Can you use the xl2bb add in to post a mini worksheet (link is below).
If you cannot use that, please post the data (with columns labeled) in a table that can be copied and pasted. Images cannot paste into cells.

 

[awoohaw]

Try this;

Book2
	T	U	V	W	X
7			formula from post
8	5	2	=IF(U8<34,T8+1, IF(U8="dnp",T8, IF(U8=34,T8, IF(U8=35,T8, IF(U8=36,T8, IF(U8=37,T8, IF(U8=38,T8-1, IF(U8=39,T8-1, IF(U8=40,T8-1, IF(U8>40,T8-2, IF($CH8="shots back blocked",T8)))))))))))	=IF(U8="dnp",T8, IF(U8<34,T8+1, IF(AND(U8>=34,U8<38),T8, IF(AND(U8>=38,U8<=40),T8-1, IF(U8>40,T8-2, IF(U8="shots back blocked",T8,""))))))	6
9	10	dnp			10
10	10	34			10
11	10	35			10
12	10	36			10
13	10	37			10
14	10	38			9
15	10	39			9
16	10	40			9
17	10	41			8
18	10				11
19	10	shots back blocked			8
20	10	42			8
Sheet1

Cell Formulas
Range	Formula
W8	W8	=FORMULATEXT(X8)
X8:X20	X8	=IF(U8="dnp",T8, IF(U8<34,T8+1, IF(AND(U8>=34,U8<38),T8, IF(AND(U8>=38,U8<=40),T8-1, IF(U8>40,T8-2, IF(U8="shots back blocked",T8,""))))))

 

[The Shadowman]

I'll give it a try.

Many thanks

 

[Alex Blakenburg]

I don't understand the last condition. It is not going to execute in any of the formulas shown so far.
ie ="shots back blocked">40 is TRUE and this condition comes before the IF($CH8="shots back blocked",T8)
in Post #1 so it is not going to get to that If statement
I don't think @awoohaw knew what to do with it either and has just added it to the end but changed the reference to T8 and that condition will also never be executed.

I think this will do the same thing. as any of the previous formulas.

=T8 +
IF(U8="dnp",0,
IF(U8>40,-2,
IF(U8>=38,-1,
IF(U8>=34,0,1))))

 

[Cubist]

@awoohaw One issue with your formula is U8="shots back blocked" will never get executed.
Edit: @Alex Blakenburg beats me to it, but an option without nested IF.

=T8+(MATCH(TRUE,INDEX({41;38;34;0}<=U8,0),0)-3)*ISNUMBER(U8)

 

[awoohaw]

I don't understand the last condition. It is not going to execute in any of the formulas shown so far.
ie ="shots back blocked">40 give TRUE and this condition comes before the IF($CH8="shots back blocked",T8)
in Post #1 so it is not going to get to that If statement
I don't think @awoohaw knew what to do with it either and has just added it to the end but changed the reference to T8 and that condition will also never be executed.

I think this will do the same thing. as any of the previous formulas.

=T8 +
IF(U8="dnp",0,
IF(U8>40,-2,
IF(U8>=38,-1,
IF(U8>=34,0,1))))

Doh!!! I didn't even notice that was a different cell reference. All OP was focused on were columns T and U, I just tried to reconstruct the formula so it would calculate based on only values in Column T and U. And, assumed when I was building mine that all the references were to U.

Regardless, I'm not sure what OP is asking for regarding why values are changing with respect to the other 3 columns.
OP: Can you help clarify what this means, what do R, P, and N mean in reference to T and U?

"It's changing the values in U that R P and N are responding to. The formulars in R P and N are the same as T and V"

 

[The Shadowman]

Each column with a coloured square is a golf score for one week and each row is an individual player. What is supposed to happen is that the golf score is entered and in the next column is the handicap adjusted according to the score. the handicap adjustment reference is taken from the column before the coloured score column. However, when I enter the score the handicap is changed for several weeks back. Only three shots back are allowed over the season hence the text "shots back blocked" message. Now what should happen is if the "shots back blocked" appears the handicap should not go up anymore. I suspect that this is where it's going wrong, but I can't work out why. Is my formula sequenced correctly??