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:
Sort by date Sort by votes
Re: Formuala for changing a column based on results in another column

Use my formula in Post # 13 for your Column AV

=IF(ISERR(AU4),AP4,IF(OR(AU4>=70,AU4<=30),AP4-IF(AP4>16,4,IF(AP4<16,2,0)),AP4))

Other than that, I can't help you any further, as your file is a Macro Enabled workbook, and your AS and AT columns are using UDFs for formulas, and I'm Not enabling Macros to view your file.

Good Luck.
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Re: Formuala for changing a column based on results in another column

Ok,

Formulas copied down:


Book1
APAQARASATAUAVAW
1lastHANDICAP REVIEWS
2H/CAPplayedNoTotal last 3
3
42Feb-1800.00.02FALSE
510Dec-1700.00.010FALSE
6000.00.00FALSE
728Feb-1800.00.028FALSE
81000.00.010FALSE
91200.00.012FALSE
102400.00.024FALSE
112400.00.024FALSE
121600.00.016FALSE
131200.00.012FALSE
143200.00.032FALSE
154000.00.040FALSE
163400.00.034FALSE
172400.00.024FALSE
1816Oct-1600.00.016FALSE
19600.00.06FALSE
202800.00.028FALSE
212400.00.024FALSE
221000.00.010FALSE
23400.00.04FALSE
241000.00.010FALSE
25000.00.00FALSE
262400.00.024FALSE
2716Sep-1600.00.016FALSE
2863150.050.06FALSE
29323201.067.032FALSE
3043143.347.84FALSE
31243107.735.924FALSE
32203160.053.320FALSE
334Mar-173184.161.44FALSE
34-203208.369.4-20FALSE
35403175.058.340FALSE
36-163153.351.1-16FALSE
37243116.638.924FALSE
38-143186.762.2-14FALSE
39283133.344.428FALSE
40-183177.759.2-18FALSE
41-43115.038.3-4FALSE
42403133.344.440FALSE
43203203.167.720FALSE
44163108.336.116FALSE
4516Jul-173120.040.016FALSE
4643165.055.04FALSE
4728366.622.224TRUE
Sheet1
Cell Formulas
RangeFormula
AU4=IF(AT4=0,0,AT4/AS4)
AV4=IF(AU4=0,AP4,IF(OR(AU4>=70,AU4<=30),AP4-IF(AP4>16,4,IF(AP4<16,2,0)),AP4))
AW4=AV4<>AP4
 
Upvote 0
Re: Formuala for changing a column based on results in another column

Amazing - thank you so much

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

There is a slight problem with the formula - if the figure in AU is equal to or less than 30 it is deducting 4 or 2 but it should be adding 4 or 2 - please can you fix please?
 
Upvote 0
Re: Formuala for changing a column based on results in another column

Also if you were prepared to tweak the formula for me I have clarity on one of your first questions if someone's handicap is 16, the rules are as follows:
= or < 16 up or down by 2 (at present I don' think anything happens if they are 16
>16 Adjust up or down by 4

So is there any chance you can do this and the adding problem above?
 
Upvote 0
Re: Formuala for changing a column based on results in another column

I really appreciate your help please please can you help me finish the formula, I have attempted to change the formula but it doesn't work - please can you fix it for me? My attempt is below - also I have no idea how to do the .... if = or < 16 up or down by 2


=IF(AV4=0,AP4,IF(OR(AV4>=70,),AP4-IF(AP4>16,4,IF(AP4<16,2,0)),AP4)),(or(AV4<=30,),AP4+IF(AP4>16,4,IF(AP4<16,2,0)),AP4))



Hope you can help me
Marc
 
Upvote 0
Re: Formuala for changing a column based on results in another column

Apologies, was a bit busy this past day, and didn't get notification of your reply.

See if this updated version addresses what you require as posted in Post # 25 & 26


Book1
APAQARASATAUAVAW
1lastHANDICAP REVIEWS
2H/CAPplayedNoTotal last 3
3
42Feb-1800.00.02FALSE
510Dec-1700.00.010FALSE
6000.00.00FALSE
728Feb-1800.00.028FALSE
81000.00.010FALSE
91200.00.012FALSE
102400.00.024FALSE
112400.00.024FALSE
121600.00.016FALSE
131200.00.012FALSE
143200.00.032FALSE
154000.00.040FALSE
163400.00.034FALSE
172400.00.024FALSE
1816Oct-1600.00.016FALSE
19600.00.06FALSE
202800.00.028FALSE
212400.00.024FALSE
221000.00.010FALSE
23400.00.04FALSE
241000.00.010FALSE
25000.00.00FALSE
262400.00.024FALSE
2716Sep-1600.00.016FALSE
2863150.050.06FALSE
29323201.067.032FALSE
3043143.347.84FALSE
31243107.735.924FALSE
32203160.053.320FALSE
334Mar-173184.161.44FALSE
34-203208.369.4-20FALSE
35403175.058.340FALSE
36-163153.351.1-16FALSE
37243116.638.924FALSE
38-143186.762.2-14FALSE
39283133.344.428FALSE
40-183177.759.2-18FALSE
41-43115.038.3-4FALSE
42403133.344.440FALSE
43203203.167.720FALSE
44163108.336.116FALSE
4516Jul-173120.040.016FALSE
4643165.055.04FALSE
4728366.622.232TRUE
Sheet1
Cell Formulas
RangeFormula
AU4=IF(AT4=0,0,AT4/AS4)
AV4=IF(AU4=0,AP4,IF(AU4>=70,AP4-IF(AP4>16,4,2),IF(AU4<=30,AP4+IF(AP4>16,4,2),AP4)))
AW4=AV4<>AP4
 
Upvote 0
Re: Formuala for changing a column based on results in another column

Thank you for all you help
 
Upvote 0
Re: Formuala for changing a column based on results in another column

You're welcome.
 
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
867
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,818
Messages
6,181,152
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