Formula for changing a column based on results in another column

marcidee

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

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: Formuala for changing a column based on results in another column

Hi,

What if AP is exactly 16?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>AP</th><th>AQ</th><th>AU</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Present HC</td><td style=";">Adjusted HC</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">17</td><td style="text-align: right;;">13</td><td style="text-align: right;;">71</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">15</td><td style="text-align: right;;">13</td><td style="text-align: right;;">29</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">17</td><td style="text-align: right;;">17</td><td style="text-align: right;;">69</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">31</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet636</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AQ2</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">AU2>=70,AU2<=30</font>),AP2-IF(<font color="Red">AP2>16,4,IF(<font color="Green">AP2<16,2,0</font>)</font>),AP2</font>)</td></tr></tbody></table></td></tr></table><br />
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
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
5,146
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)

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>AP</th><th>AQ</th><th>AU</th><th>AV</th><th>AW</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Present HC</td><td style=";">Adjusted HC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">17</td><td style="text-align: right;;">13</td><td style="text-align: right;;">71</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">15</td><td style="text-align: right;;">13</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">17</td><td style="text-align: right;;">17</td><td style="text-align: right;;">69</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet636</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AQ2</th><td style="text-align:left">=IF(<font color="Blue">AP2="","",IF(<font color="Red">OR(<font color="Green">AU2>=70,AU2<=30</font>),AP2-IF(<font color="Green">AP2>16,4,IF(<font color="Purple">AP2<16,2,0</font>)</font>),AP2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AW2</th><td style="text-align:left">=AP2<>AQ2</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

marcidee

Board Regular
Joined
May 23, 2016
Messages
159

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
5,146
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
159

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

Thanks, misread your post # 3

Try it this way:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>AP</th><th>AQ</th><th>AU</th><th>AV</th><th>AW</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Present HC</td><td style=";">Adjusted HC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">17</td><td style="text-align: right;;">13</td><td style="text-align: right;;">71</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">15</td><td style="text-align: right;;">13</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">17</td><td style="text-align: right;;">17</td><td style="text-align: right;;">69</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">17</td><td style="text-align: right;;">17</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet636</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AQ2</th><td style="text-align:left">=IF(<font color="Blue">AU2=0,AP2,IF(<font color="Red">OR(<font color="Green">AU2>=70,AU2<=30</font>),AP2-IF(<font color="Green">AP2>16,4,IF(<font color="Purple">AP2<16,2,0</font>)</font>),AP2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AW2</th><td style="text-align:left">=AP2<>AQ2</td></tr></tbody></table></td></tr></table><br />
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
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
5,146
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,323
Members
409,695
Latest member
EmmaFos

This Week's Hot Topics

Top