Formula for changing a column based on results in another column

M

marcidee

Board Regular
Joined
May 23, 2016
Messages
196
Office Version
  1. 2019
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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)
 
Upvote 0
Re: Formuala for changing a column based on results in another column

jtakw said:
Hi,

What if AP is exactly 16?

APAQAU
Present HCAdjusted HC

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]71[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]29[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]69[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]31[/TD]

</tbody>
Sheet636​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ2[/TH]
[TD="align: left"]=IF(OR(AU2>=70,AU2<=30),AP2-IF(AP2>16,4,IF(AP2<16,2,0)),AP2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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
Click to expand...
 
Upvote 0
Re: Formuala for changing a column based on results in another column

marcidee said:
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
Click to expand...

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:
Upvote 0
Re: Formuala for changing a column based on results in another column

jtakw said:
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
Present HCAdjusted HC

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

</tbody>
Sheet636​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ2[/TH]
[TD="align: left"]=IF(AP2="","",IF(OR(AU2>=70,AU2<=30),AP2-IF(AP2>16,4,IF(AP2<16,2,0)),AP2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AW2[/TH]
[TD="align: left"]=AP2<>AQ2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
 
Upvote 0
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)
 
Upvote 0
Re: Formuala for changing a column based on results in another column

jtakw said:
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)
Click to expand...


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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Golf Handicap Formula help
2
Replies
11
Views
597
Gulfcourse
G
S
  • Question Question
Fix Column Reference? Changes with External Data Refresh
Replies
1
Views
314
Scotster
S
A
  • Question Question
VBA to change cell colour based on content
Replies
3
Views
866
Snakehips
Snakehips
M
  • Question Question
ICON set auto change based on cell value.
Replies
1
Views
686
bobaol
B
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
284
Pingaware
P

Forum statistics

Threads
1,224,814
Messages
6,181,126
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top