Formula for changing a column based on results in another column

marcidee

Board Regular
Joined
May 23, 2016
Messages
177
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Re: Formuala for changing a column based on results in another column

Hi,

What if AP is exactly 16?


Book1
APAQAU
1Present HCAdjusted HC
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)
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
177
Re: Formuala for changing a column based on results in another column

Hi,

What if AP is exactly 16?

APAQAU
1Present HCAdjusted HC
2171371
3151329
4171769
5151531

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet636

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

<thead>
</thead><tbody>
</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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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
1Present HCAdjusted HC
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:

marcidee

Board Regular
Joined
May 23, 2016
Messages
177

ADVERTISEMENT

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
1Present HCAdjusted HC
2171371TRUE
3151329TRUE
4171769FALSE
531FALSE
629FALSE
7151531FALSE

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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

<thead>
</thead><tbody>
</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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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)
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
177

ADVERTISEMENT

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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Re: Formuala for changing a column based on results in another column

Thanks, misread your post # 3

Try it this way:


Book1
APAQAUAVAW
1Present HCAdjusted HC
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
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
177
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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,212
Messages
5,836,034
Members
430,401
Latest member
Kalleahlqvist

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top