Formula for changing a column based on results in another column

marcidee

Board Regular
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

Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Re: Formuala for changing a column based on results in another column

Hi,

What if AP is exactly 16?

Book1
APAQAU
2171371
3151329
4171769
5151531
Sheet636
Cell Formulas
RangeFormula
AQ2=IF(OR(AU2>=70,AU2<=30),AP2-IF(AP2>16,4,IF(AP2<16,2,0)),AP2)

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

Hi,

What if AP is exactly 16?

APAQAU
2171371
3151329
4171769
5151531

</tbody>
Sheet636

Worksheet Formulas
CellFormula
AQ2=IF(OR(AU2>=70,AU2<=30),AP2-IF(AP2>16,4,IF(AP2<16,2,0)),AP2)

</tbody>

<tbody>
</tbody>

Thank yo very much for this, it's excellent, I will check on the rule if they are actually 16 - are you able to add something where if column AU has zero (they didn't play) nothing happens - at present it it is deducting 2 or 4

Also is it possible add something so the result replaces column AP (to save copying the results to AP) - if there was a way to highlight those that have changed - that would even be better

Thanks

Marc

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

Thank yo very much for this, it's excellent, I will check on the rule if they are actually 16 - are you able to add something where if column AU has zero (they didn't play) nothing happens - at present it it is deducting 2 or 4
Also is it possible add something so the result replaces column AP (to save copying the results to AP) - if there was a way to highlight those that have changed - that would even be better
Thanks
Marc

Formula adjusted for when AP is Blank, return Blank (above in red)
It is Not possible to have a formula change another cells value, you'll need VBA if you want AP changed ( I won't be able to help with this ), (above in green)
For my sample select AQ2 down, CF, use formula, copy formula from AW2, paste, Select format of your choice for highlight. (above in blue)

Book1
APAQAUAVAW
2171371TRUE
3151329TRUE
4171769FALSE
531FALSE
629FALSE
7151531FALSE
Sheet636
Cell Formulas
RangeFormula
AQ2=IF(AP2="","",IF(OR(AU2>=70,AU2<=30),AP2-IF(AP2>16,4,IF(AP2<16,2,0)),AP2))
AW2=AP2<>AQ2

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

Formula adjusted for when AP is Blank, return Blank (above in red)
It is Not possible to have a formula change another cells value, you'll need VBA if you want AP changed ( I won't be able to help with this ), (above in green)
For my sample select AQ2 down, CF, use formula, copy formula from AW2, paste, Select format of your choice for highlight. (above in blue)

APAQAUAVAW
2171371TRUE
3151329TRUE
4171769FALSE
531FALSE
629FALSE
7151531FALSE

</tbody>
Sheet636

Worksheet Formulas
CellFormula
AQ2=IF(AP2="","",IF(OR(AU2>=70,AU2<=30),AP2-IF(AP2>16,4,IF(AP2<16,2,0)),AP2))
AW2=AP2<>AQ2

</tbody>

<tbody>
</tbody>

Thank you so much for this - one tweak if possible - it's column that sometimes has a zero (not AP that has a blank) - so at the moment where AU has a zero it's still adding / deducting 2 or 4

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

Need clarification:

What result do you want in AQ...

1. When AP is Blank
2. When AP is 0 (zero)
3. When AU is Blank
4. When AU is 0 (zero)

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

Need clarification:

What result do you want in AQ...

1. When AP is Blank
2. When AP is 0 (zero)
3. When AU is Blank
4. When AU is 0 (zero)

To clarify
AP is not blank or zero, AU is not blank but may contain zero - if AU is zero I wold like to see the value of AP entered in AU unchanged

Thank you again

Marc

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

Try it this way:

Book1
APAQAUAVAW
2171371TRUE
3151329TRUE
4171769FALSE
5151531FALSE
617170FALSE
715150FALSE
8151531FALSE
Sheet636
Cell Formulas
RangeFormula
AQ2=IF(AU2=0,AP2,IF(OR(AU2>=70,AU2<=30),AP2-IF(AP2>16,4,IF(AP2<16,2,0)),AP2))
AW2=AP2<>AQ2

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

Thank you - but I am still getting the same problem - if AU has zero - the result is deducting / adding 2 or 4

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

Then AU is not zero (even if it appears to be), where do these values in AU come from?

In an unused cell, enter =AU2=0
What do you get?
Also try =ISNUMBER(AU2)
What do you get?

Where AU2 is the cell that contains the zero that you see, and the formula fails.

Replies
1
Views
207
Replies
3
Views
222
Replies
6
Views
178
Replies
1
Views
200
Replies
0
Views
119

1,207,257
Messages
6,077,337
Members
446,278
Latest member
hoangquan2310

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back