Problem With Formula

T

The Shadowman

New Member
Joined
May 5, 2021
Messages
47
Office Version
  1. 2019
Platform
  1. Windows
=IF(U8<34,T8+1,IF(U8="dnp",T8,IF(U8=34,T8,IF(U8=35,T8,IF(U8=36,T8,IF(U8=37,T8,IF(U8=38,T8-1,IF(U8=39,T8-1,IF(U8=40,T8-1,IF(U8>40,T8-2,IF($CH8="shots back blocked",T8)))))))))))

The formula above is in Column "V" and should respond to information from columns "T" and "U" only. However, when the data in T and U is provided it changes columns R P & N in error. I can not work out for the life of me why this is happening. R P and N should not be affected.

Is there an error in the formula? If there is I can't find it.

Please help
Thanks
Robert
 
Sort by date Sort by votes
Okay, I think I have it figured out for you. If you have the FILTER() function in Excel 2019, then this could be a little be it less wordy a calculation.
I've also used some conditional formatting to indicate while I was validating for the counts of HDCP changes. The Calculation in column CH requires no data between the last HDCP calc and column CH. If you have intervening data, then you need to adjust the scope of the ranges being selected. Please note that to do the calculation for the Total Changes, and the Embedded Calculation the ranges are offset so they align when doing the calculation for differences. If you change your spreadsheet around just note that the Handicap Columns need to be in an EVEN numbered column, you'll have to adjust formulas accordingly if you move them to ODD columns).

(Note: The conditional formatting rules do not transfer into the mini workbook, only the result of the formatting. If you want to apply the conditional formatting you will need to use the rules below and add to your worksheet yourself.)

Book1
KLMNOPQRSTUVWXYZAACDCECFCGCH
6Score 1HDCPScore2HDCPScore3HDCPScore4HDCPHDCP ADJ
740223522332336231
832182519332039193
9DNP13DNP133413DNP130
1039182419332042183
112422DNP222822DNP220
123683193883682
1335113012291332123
14
15
16INITIALThese three calculations are the same.These change with each round after first 3, but can be pasted in next round HDCP calc.
17FIXEDADJUSTEDADJUSTEDADJUSTEDADJUSTEDADJUSTEDTOTAL
18Score 1HDCPScore2HDCPScore3HDCPScore4HDCPScore5HDCPScore6HDCPHDCP ADJ
194022352233233623312437242
203218251933203919311929193
21DNP13DNP133413DNP1345114292
223918241933204218431836183
232422DNP222823DNP23352348212
243683193883684264463
253511301229133214401431143
Sheet1
Cell Formulas
RangeFormula
CH7:CH13CH7=SUM(--(((IF(ISTEXT(L7:P7),0,L7:P7))*ISEVEN(COLUMN(L7:P7)))<>(IF(ISTEXT(N7:R7),0,N7:R7))*ISEVEN(COLUMN(N7:R7))))
N19:N25,R19:R25,P19:P25N19=IF(M19="DNP",L19, IF(M19="","", IF(M19<34,L19+1, IF(M19<=37,L19, IF(M19<=40,L19-1, IF(M19>=41,L19-2,""))))))
T19:T25,V19:V25T19= IF(S19="","", IF(SUM(--(((IF(ISTEXT($L19:P19),0,$L19:P19))*ISEVEN(COLUMN($L19:P19))) <> (IF(ISTEXT($N19:R19),0,$N19:R19))*ISEVEN(COLUMN($N19:R19))))>=3,R19, IF(S19="DNP",R19, IF(S19<34,R19+1, IF(S19<=37,R19, IF(S19<=40,R19-1, IF(S19>=41,R19-2,"")))))))
CH19:CH25CH19=SUM(--(((IF(ISTEXT(L19:CD19),0,L19:CD19))*ISEVEN(COLUMN(L19:CD19)))<>(IF(ISTEXT(N19:CF19),0,N19:CF19))*ISEVEN(COLUMN(N19:CF19))))-1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
V19:V25Expression=T19<>V19textNO
T19:T25Expression=R19<>T19textNO
R19:R25Expression=P19<>R19textNO
P19:P25Expression=N19<>P19textNO
N19:N25Expression=L19<>N19textNO
 
Last edited:
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
awoohaw said:
Okay, I think I have it figured out for you. If you have the FILTER() function in Excel 2019, then this could be a little be it less wordy a calculation.
I've also used some conditional formatting to indicate while I was validating for the counts of HDCP changes. The Calculation in column CH requires no data between the last HDCP calc and column CH. If you have intervening data, then you need to adjust the scope of the ranges being selected. Please note that to do the calculation for the Total Changes, and the Embedded Calculation the ranges are offset so they align when doing the calculation for differences. If you change your spreadsheet around just note that the Handicap Columns need to be in an EVEN numbered column, you'll have to adjust formulas accordingly if you move them to ODD columns).

(Note: The conditional formatting rules do not transfer into the mini workbook, only the result of the formatting. If you want to apply the conditional formatting you will need to use the rules below and add to your worksheet yourself.)

Book1
KLMNOPQRSTUVWXYZAACDCECFCGCH
6Score 1HDCPScore2HDCPScore3HDCPScore4HDCPHDCP ADJ
740223522332336231
832182519332039193
9DNP13DNP133413DNP130
1039182419332042183
112422DNP222822DNP220
123683193883682
1335113012291332123
14
15
16INITIALThese three calculations are the same.These change with each round after first 3, but can be pasted in next round HDCP calc.
17FIXEDADJUSTEDADJUSTEDADJUSTEDADJUSTEDADJUSTEDTOTAL
18Score 1HDCPScore2HDCPScore3HDCPScore4HDCPScore5HDCPScore6HDCPHDCP ADJ
194022352233233623312437242
203218251933203919311929193
21DNP13DNP133413DNP1345114292
223918241933204218431836183
232422DNP222823DNP23352348212
243683193883684264463
253511301229133214401431143
Sheet1
Cell Formulas
RangeFormula
CH7:CH13CH7=SUM(--(((IF(ISTEXT(L7:P7),0,L7:P7))*ISEVEN(COLUMN(L7:P7)))<>(IF(ISTEXT(N7:R7),0,N7:R7))*ISEVEN(COLUMN(N7:R7))))
N19:N25,R19:R25,P19:P25N19=IF(M19="DNP",L19, IF(M19="","", IF(M19<34,L19+1, IF(M19<=37,L19, IF(M19<=40,L19-1, IF(M19>=41,L19-2,""))))))
T19:T25,V19:V25T19= IF(S19="","", IF(SUM(--(((IF(ISTEXT($L19:P19),0,$L19:P19))*ISEVEN(COLUMN($L19:P19))) <> (IF(ISTEXT($N19:R19),0,$N19:R19))*ISEVEN(COLUMN($N19:R19))))>=3,R19, IF(S19="DNP",R19, IF(S19<34,R19+1, IF(S19<=37,R19, IF(S19<=40,R19-1, IF(S19>=41,R19-2,"")))))))
CH19:CH25CH19=SUM(--(((IF(ISTEXT(L19:CD19),0,L19:CD19))*ISEVEN(COLUMN(L19:CD19)))<>(IF(ISTEXT(N19:CF19),0,N19:CF19))*ISEVEN(COLUMN(N19:CF19))))-1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
V19:V25Expression=T19<>V19textNO
T19:T25Expression=R19<>T19textNO
R19:R25Expression=P19<>R19textNO
P19:P25Expression=N19<>P19textNO
N19:N25Expression=L19<>N19textNO
Click to expand...
 
Upvote 0
Thank you so much. I just hope that I am clever enough to make all this work. I would hate to waste all the time and hard work you have spent on this.

Thanks again
 
Upvote 0
The Shadowman said:
Thank you so much. I just hope that I am clever enough to make all this work. I would hate to waste all the time and hard work you have spent on this.

Thanks again
Click to expand...
Well, then start it in a fresh worksheet. Play around with it, add your golfers and scores. When you get comfortable then plug the formulas into your worksheet and adjust them to fit your worksheet.

I do have some follow ups, one of which I should have thought of sooner.
1. When you discuss the process of "shots back blocked", the formulas I give count all the instances where the HDCP changes, not just increased.
2. I may have confused you regarding my comments about the formulas. The formulas are copyable to other columns, as long as you copy to the same row(s).
  • What mean to say, is that the first three Adjusted HDCP columns have 1 similar formula. The ranges used in each column grow so the prior columns are captured.
  • The formulas of the other columns work similarly. But these have a counter for HDCP adjustment in each formula, This counter is not needed in the first the Adjusted HDCP columns for math reasons, the number of adjustments will never exceed 3 in these 3.
  • You probably do not need to have the counter in column CH anymore unless you want to visibly see a count of the HDCP changes. But, this can also be seen by the conditional formatting that I added.
Best Wishes.​
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Copy formula across 28 day dynamic shift calendar
Replies
4
Views
334
Wolfgang17
W
J
  • Solved
Dynamic Tier System
2
Replies
18
Views
1K
Dave Patton
D
M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
98
Fluff
Fluff
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
987
felixstraube
F
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
240
acool
A

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

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