Formula for changing a column based on results in another column

[marcidee]

Can someone help with a formula o script - I have present handicaps in column AP - based on whether their present handicap is 16 and over or 16 and below - I need the following to happen based n results in column AU:


if a number in column AU is higher than 70
Present handicap over 16 (column AP) - decrease by 4
Present handicap under 16 (column AP) - decrease by 2


if a number in column AU is less than 30
Present handicap over 16 (column AP)- increase by 4
Present handicap under 16 (column AP) - increase by 2

All rows with a result between 31 an 69 between nothing happens and the handicap in column AP doesn't change

Your help would be very much appreciated

Marc

 

[marcidee]

Re: Formuala for changing a column based on results in another column

you are correct - I ahve now changed the formula in AU and the cells that were giving zero ae now giving #DIV0 ! - are you able to give me something that where it see #DIV0 ! it inputs the value of AP?

 

[jtakw]

Re: Formuala for changing a column based on results in another column

Probably a better idea to "Fix" your formula in AU, please post the entire formula you're using in AU.

 

[jtakw]

Re: Formuala for changing a column based on results in another column

Well, if you don't want to fix your AU formula, here's a workaround:

Book1
	AP	AQ	AU	AV	AW
1	Present HC	Adjusted HC
2	17	13	71		TRUE
3	15	13	29		TRUE
4	17	17	69		FALSE
5	15	15	31		FALSE
6	17	17	#DIV/0!		FALSE
7	15	15	#DIV/0!		FALSE
8	15	15	31		FALSE
Sheet636

Cell Formulas
Range	Formula
AQ2	=IF(ISERR(AU2),AP2,IF(OR(AU2>=70,AU2<=30),AP2-IF(AP2>16,4,IF(AP2<16,2,0)),AP2))
AU6	=1/0
AW2	=AP2<>AQ2

 

[marcidee]

Re: Formuala for changing a column based on results in another column

So the result in AU is AS * AT - in some cases that is 0 * 0 resulting in #DIV0 !- - so in that instance I would like AU to insert the value of AP (unchanged)

 

[jtakw]

Re: Formuala for changing a column based on results in another column

If your formula in AU is AS*AT, it is impossible to get the #DIV/0 error, as you're doing multiplication, Not division, if anything, you'll get #VALUE error, so, if you want help fixing your AU formula, please post the entire AU formula, including your AS and AT formula might also help.

 

[marcidee]

Re: Formuala for changing a column based on results in another column

You are right it is a divide - bascally as AS and AT are zeros - if you are ablw to come up with a solution I would be ver grateful - How do I send you the sheet?

 

[jtakw]

Re: Formuala for changing a column based on results in another column

AQ formula in Post # 13 will handle the #DIV/0 error in AU.

Or, use something like this in AU:

=IF(OR(AS2=0,AT2=0),0,AS2/AT2)

Then use AQ formula in Post # 8.

 

[marcidee]

Re: Formuala for changing a column based on results in another column

Not sure what I am supposed to do - I can't place that formula into AU as AU is = AT/AS so I need that formula where there are values in those cells - the formula in #13 doesn't work now I have corrected the formula where there was previously zeros

 

[jtakw]

Re: Formuala for changing a column based on results in another column

Upload your file to a Free file host like Dropbox, and post the link here.

 

[marcidee]

Re: Formuala for changing a column based on results in another column

https://mab.to/gZcytPt7M

thank you