• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Corrections to my formulas in RED

BL84

Member
Hi guys,

I need help with 3 formulas. They are marked by RED in the "New Pre" and "April" sheets. On the "New Pre" sheet, GIR Putt & U/D-M need a formula that will encompass whatever range is needed. I can tell you the needed info, just can't write the formula.
This would be for Round 1. GIR Putt: If there is a 1 in the GIR cell (C3), then add the putts (C2). For U/D-M: If C3 is NOT 1, and the SCORE is <= the par for the hole, count it. These would be in the proper cells in the April table shown to the right of the scores. Here are the "Pars" for each hole. 4 3 5 4 3 4 4 4 4 = Par 35. The third formula would be for the RED cells A:J and 5-9 on the "April" sheet. I have a formula there already, but that was in regards to the "Pre" sheet scores. I need a formula that will work with the Rounds listed on the "New Pre" sheet. I think the "ending" of that formula can be used, but I don't know what to put in front of it. If you have any ideas, please let me know.

Thanks

BL84
 

Attachments

  • 2015 Stats (Mini).xlsx
    251.8 KB · Views: 8
Hi,

For you requirement No. 01 where C2 is to be added in C3 if C3 is 1, can you verify the results of this formula (to be put in S6 in New Pre)

=SUMPRODUCT((C3:K3=1)*(C3:K3+C2:K2))

For T6 in the same sheet:

=SUMPRODUCT((C3:K3<>1)*(C1:K1<Pre!B3:J3)*('New Pre'!C1:K1))

For April B5

=SUMPRODUCT(('New Pre'!$A$1:$A$800=("Round "&FLOOR((ROW($A$5:$A$804)-1)/4,1)))*('New Pre'!$B$1:$B$800="Score")*('New Pre'!C$1:C$800=4))

You can replace the text in red (which is criteria) according to your requirement and drag formula to right/down.

Please fell free to give feedback.
 
Last edited:
Hi,

For you requirement No. 01 where C2 is to be added in C3 if C3 is 1, can you verify the results of this formula (to be put in S6 in New Pre)

=SUMPRODUCT((C3:K3=1)*(C3:K3+C2:K2))

For T6 in the same sheet:

=SUMPRODUCT((C3:K3<>1)*(C1:K1<Pre!B3:J3)*('New Pre'!C1:K1))

For April B5

=SUMPRODUCT(('New Pre'!$A$1:$A$800=("Round "&FLOOR((ROW($A$5:$A$804)-1)/4,1)))*('New Pre'!$B$1:$B$800="Score")*('New Pre'!C$1:C$800=4))

You can replace the text in red (which is criteria) according to your requirement and drag formula to right/down.

Please fell free to give feedback.
 
Hi Faseeh,

Wow!! That's a TON of formula! Unfortunately, they do not work. The 1st formula actually adds cells C2 and C3. I just need the total of Putts for each round where 1 is present in GIR (c"3"). The second formula gave me a total of 4 with nothing entered. As for the third formula, it came back with 0. It should have had an answer of 160. Also, the "Pre" sheet will not be used for anything. If I need to write the par for the holes, I'll do it on the "New Pre" sheet off to the side. I don't need that, since I know those numbers and automatically put them in if needed. I only need to type them once and then copy where needed. Please look at my formulas in the April table. Something as simple as those will work for GIR Putts & U/D-M. I just can't do it properly. Here's an example of what I'd write for GIR Putts. Maybe you can order it properly or add what is needed.

=sumif($b:$b,"Putts",$c:$c,if,"GIR",1) To me that says, add the putts in the C column if there is a 1 next to the GIR cell. Obviously, that doesn't make sense to excel.

This is what I'd write for U/D-M.

=countif($b:$b,"GIR",$c:$c,0,c1<=4) To me that says, add one to the proper cell if there is NO GIR and the score for that hole is less than or equal to par for that hole. (4 is par for hole #1)

The formula for the "April" sheet has to have a formula that encompasses all rounds in the needed range on the "New Pre" sheet. The last part of that formula should be able to be used. If you look at those formulas, you can see I had the range set for the first 40 rounds. I don't know how many I'll play in April, but that was an estimate that can be adjusted. Your formulas are way to high a level for me to use. Come down to my level and see if you can simply adjust mine. I appreciate all your efforts.

Thanks

BL84
 
Hi,

For you requirement No. 01 where C2 is to be added in C3 if C3 is 1, can you verify the results of this formula (to be put in S6 in New Pre)

=SUMPRODUCT((C3:K3=1)*(C3:K3+C2:K2))

Here's the formula that works.

=sumproduct(($c$4:$k$4=1)*(c3:k3))

Just 2 formulas needed now. Thanks, BL84
 
Wow!! That's a TON of formula! Unfortunately, they do not work.

First of all Thank you for the compliment! :(

Secondly, if you like, can you try this formula:

S6: =SUMPRODUCT((C3:K3=1)*(C2:K2))

T6: =SUMPRODUCT((C3:K3<>1)*(C1:K1<Pre!B3:J3)*1)

If you can pick up just one issue at a time and explain with example, it will be great.
 
Last edited:

Of course, this only works for "Round 1". I need a formula that will work for 200 + rounds. Can you adapt it to include ALL rounds in whatever range I need it to be? At least thru "Round 45" for April.

Thanks

BL84
 
First of all Thank you for the compliment! :(

Secondly, if you like, can you try this formula:

S6: =SUMPRODUCT((C3:K3=1)*(C2:K2))

T6: =SUMPRODUCT((C3:K3<>1)*(C1:K1<Pre!B3:J3)*1)

If you can pick up just one issue at a time and explain with example, it will be great.
First of all Thank you for the compliment! :(

Secondly, if you like, can you try this formula:

S6: =SUMPRODUCT((C3:K3=1)*(C2:K2))

T6: =SUMPRODUCT((C3:K3<>1)*(C1:K1<Pre!B3:J3)*1)

If you can pick up just one issue at a time and explain with example, it will be great.


I have 3 problems. GIR Putts. U/D-M. These are on the "New Pre" sheet. The Par-Bogey-Birdie-Other-Eagle cells listed on the "April" sheet are the third problem. My issue is that any formulas MUST encompass ALL rounds played in the given range. Somewhere from 1-200 +. Your formula for GIR Putts eventually worked, but only for Round 1. It has to be 1 formula that covers all the rounds needed. An example would be any of the formulas in the April table ("New Pre") except for GIR Putts & U/D-M. Those are simple formulas that encompass all rounds played. They can be adjusted to stop when I know how many rounds I play in April. (b:b,c:c) can become (b1:b210,c1:c210) Look on the "April" sheet at my formula for those categories. All you have to do is write a formula like the ones on the April table on the "New Pre" sheet and add the "ending" I have for those cells on the "April" sheet. You are at such a high level, you are making this too complicated. Doesn't take much with me and excel.

Ask me a specific question if you need clarification. I'm sure you aren't a golfer and I don't know where I may need to explain something much better.

Thanks

BL84
 
Dear Friend,

Please Note:

1. Not every one here in this forum is a golfer including myself, so you really need to explain.

2. it will be great if you pick up one issue at a time, discuss and get it resolved.

3. Try explain logic of calculation rather then focusing on 200+ round you will playing. If the logic worked for 01 round, it will eventually work for 200 or even 2000 rounds.

Your formula for GIR Putts eventually worked, but only for Round 1. It has to be 1 formula that covers all the rounds needed

Now i will adjust it for 200+ rounds dont worry.

..and can you verify the results for formula in T6?
 
Can you check for my last post?

Did I miss something? I saw some formulas that were for 1 Round. Remember the "Pre" sheet will be deleted. It's only there now to look back at incase I forget something. I added a row above round 1 on the "New Pre" sheet. I put the "par" for the holes in those cells. 4 3 5 4 3 4 4 4 4. Directly above the scores. I hope my examples give you the right idea.

Thanks

BL84
 
Dear Friend,

Please Note:

1. Not every one here in this forum is a golfer including myself, so you really need to explain.

2. it will be great if you pick up one issue at a time, discuss and get it resolved.

3. Try explain logic of calculation rather then focusing on 200+ round you will playing. If the logic worked for 01 round, it will eventually work for 200 or even 2000 rounds.


Now i will adjust it for 200+ rounds dont worry.

..and can you verify the results for formula in T6?


I had three needs for formulas. What issue needs to be discussed? I'll explain whatever you need. I'll try for the U/D-M formula and see what happens. Back at you in a few minutes.

Thanks

BL84
 
I had three needs for formulas. What issue needs to be discussed? I'll explain whatever you need. I'll try for the U/D-M formula and see what happens. Back at you in a few minutes.

Thanks

BL84


I used this formula. =sumproduct((c4:k4<>1)*(c2:k2<c1:k1)*1) and then I used Pre!b3:j3. Remember, I have a different row 1 now. Neither of them worked. 1 appeared in the u/d-m cell. Also, there's a problem with the GIR Putt formula. If 5 is entered in c3 (putts), a 1 appears in c4 (GIR). That should not happen. I won't likely ever take 5 putts on a hole, but incase I do, that result can't happen. Let me know what you need to help.

Thanks

BL84
 
Please see attachment, I have hidden the original sheet and worked out on smaller sample, see if the results in yellow highlighted cells are correct. Please pardon my ignorance with golf rules.
 

Attachments

  • 2015 Stats (Mini).xlsx
    227 KB · Views: 2
Please see attachment, I have hidden the original sheet and worked out on smaller sample, see if the results in yellow highlighted cells are correct. Please pardon my ignorance with golf rules.
We seem to be veering offline here. I input some numbers that made sense in the putts and GIR boxes. The outcomes were not correct. I'm going to bed. It's past midnight here. If you will be available tomorrow (for me), I'll surely get back to you. Thanks for your time and efforts.

BL84
 
Ok. Good Night, see you tomorrow.
Okay, I'm back for today. Here's my newest stat sheet. Table "X" on the "NEW Pre" sheet listed at N43:W47 will take the place of anything on the "April" sheet. I'll have it copied from "New Pre". Even though I don't need it, I added a row at the top showing the par for each hole. For Table X, all I need is a new range created. The range must be based on the number to the right of Score. Here's an incorrect formula attempt, but it is the right idea.

=countif(s)($b:$b,"Score",$c:$c,="4") The ="4" must be included. Just don't know how to write the proper range. I'll change all the ="4" to the proper # for each hole when a formula is found. I saw somewhere that the order of criteria can change based on countif or countifs etc. So, my order could easily be backward, mixed-up or both.

For GIR Putt on the April Table: The formula would account for this...
If there is a 1 in C4, "add" C3 to S7. It's that simple, but I can't write it.

For U/D-M: The formula would account for this...
If there is NOT a 1 in C4, and C2 is <=C1 "count" it in T7. ("count" = 1)

If Control-Shift-Enter is needed, please note that. (Array)

Thanks

BL84
 

Attachments

  • 2015 Stats a(Mini).xlsx
    227.7 KB · Views: 0
Okay, I'm back for today. Here's my newest stat sheet. Table "X" on the "NEW Pre" sheet listed at N43:W47 will take the place of anything on the "April" sheet. I'll have it copied from "New Pre". Even though I don't need it, I added a row at the top showing the par for each hole. For Table X, all I need is a new range created. The range must be based on the number to the right of Score. Here's an incorrect formula attempt, but it is the right idea.

=countif(s)($b:$b,"Score",$c:$c,="4") The ="4" must be included. Just don't know how to write the proper range. I'll change all the ="4" to the proper # for each hole when a formula is found. I saw somewhere that the order of criteria can change based on countif or countifs etc. So, my order could easily be backward, mixed-up or both.

For GIR Putt on the April Table: The formula would account for this...
If there is a 1 in C4, "add" C3 to S7. It's that simple, but I can't write it.

For U/D-M: The formula would account for this...
If there is NOT a 1 in C4, and C2 is <=C1 "count" it in T7. ("count" = 1)

If Control-Shift-Enter is needed, please note that. (Array)

Thanks

BL84
Okay, I figured out the formula for Table X. Now I just need formulas for GIR Putt & U/D-M.

Thanks

BL84
 
Well, here is where I am now. I made a new table for the April stats. I only need the GIR Putt & U/D-M formulas. I need a tweek on the 3-Putt category. At the end of my formula is "3". It needs to be >="3". I can't figure out how to make it work. If you can solve any of those, that would be fabulous.

Thanks

BL84
 

Attachments

  • 2015 Stats a(Mini).xlsx
    230.5 KB · Views: 1
If i consider April Sheet and Cell B5, what is the expected out put of the formula that this cell should show?. Please don't tell me formulas, hopefully i can write one, i just want to know what number 2, 3, 4, 5 and how did you reached that number.

P.S: Sorry to say but your sheets always contains problem statement and formulas, they never contained the correct answer of the problem. Lets say if i write a formula for Putt-3 >=3 issue how can i verify my formula results??
 
If i consider April Sheet and Cell B5, what is the expected out put of the formula that this cell should show?. Please don't tell me formulas, hopefully i can write one, i just want to know what number 2, 3, 4, 5 and how did you reached that number.

P.S: Sorry to say but your sheets always contains problem statement and formulas, they never contained the correct answer of the problem. Lets say if i write a formula for Putt-3 >=3 issue how can i verify my formula results??
I will try to use only cell locations. I solved the "April b5" issue yesterday. Apparently, it was as simple as me hitting the enter button instead of hitting control-shift-enter. I created a new table on the New Pre sheet and everything for now is right there. In the B Column, there are 4 categories. Score is the category being used for the answers in N8:V12. These are the ones previously in B5 etc. from the "April" sheet. There are 160 cells that are just to the right of the Score category. C2, C7 etc. Dealing with Column C only, the # in the cell to the right of Score, goes to the Table to the right. Every time that number is a 4, it is counted in par category in the Table to the right. (N8) As you can see, there are 157 4's in Column C just to the right of Score. There are three 5's in Column C just to the right of Score. They are counted in the Bogey Category in the Table to the right. (N9) And so on. A 3 would be counted in N10. A 6 or more would be counted in N11. A 2 would be counted in N12. I hope that is what you wanted.

As for the 3-Putt issue. Here's how you can verify if your formula works. If the # in C3,C8 etc. is a 3 or higher, it will be counted on the Table in cell N19. Right now, I have 5 examples marked in the red boxes in Column C. If you look at N19, you see the three 3's were counted and the 4 was ignored. That's all that is needed for that one. Instead of "3" at the end of that formula, it needs to be "3 or higher". I hope that helps.

I think I have explained the outcomes needed for GIR Putts & U/D-M before, but I'll do it again just in case. I have a formula in place for GIR Putts in N20, but it needs to include ALL of Column C.

For an U/D-M to be counted, several criteria must be met.
1) There must not be a 1 in C4 etc.
2) There must be a zero or one in C3 etc.
3) If 1 and 2 are true, AND C2 is <= C1 that is counted as an U/D-M.
Therefore, in the 5 rounds used for examples only Round 2 meets the criteria. (C1,C7,C8,C9) A 1 should appear in N15. There is a formula there as well that doesn't work.

For GIR Putt to be ADDED to N20 in the Table to the right, there must be a 1 in the GIR category. (C4,C9 etc.) If there is a 1 in those cells, whatever # is just to the right of Putts (B3,B8 etc.) MUST be ADDED to N20.

Those seem to be straight forward to me. No golf lingo involved.

For some reason sometimes a "circular reference in an open workbook" notice pops up when I input #'s in the Putts category. (C3,C8 etc.)
 

Attachments

  • 2015 Stats a(Mini).xlsx
    220.8 KB · Views: 0
I will try to use only cell locations. I solved the "April b5" issue yesterday. Apparently, it was as simple as me hitting the enter button instead of hitting control-shift-enter. I created a new table on the New Pre sheet and everything for now is right there. In the B Column, there are 4 categories. Score is the category being used for the answers in N8:V12. These are the ones previously in B5 etc. from the "April" sheet. There are 160 cells that are just to the right of the Score category. C2, C7 etc. Dealing with Column C only, the # in the cell to the right of Score, goes to the Table to the right. Every time that number is a 4, it is counted in par category in the Table to the right. (N8) As you can see, there are 157 4's in Column C just to the right of Score. There are three 5's in Column C just to the right of Score. They are counted in the Bogey Category in the Table to the right. (N9) And so on. A 3 would be counted in N10. A 6 or more would be counted in N11. A 2 would be counted in N12. I hope that is what you wanted.

As for the 3-Putt issue. Here's how you can verify if your formula works. If the # in C3,C8 etc. is a 3 or higher, it will be counted on the Table in cell N19. Right now, I have 5 examples marked in the red boxes in Column C. If you look at N19, you see the three 3's were counted and the 4 was ignored. That's all that is needed for that one. Instead of "3" at the end of that formula, it needs to be "3 or higher". I hope that helps.

I think I have explained the outcomes needed for GIR Putts & U/D-M before, but I'll do it again just in case. I have a formula in place for GIR Putts in N20, but it needs to include ALL of Column C.

For an U/D-M to be counted, several criteria must be met.
1) There must not be a 1 in C4 etc.
2) There must be a zero or one in C3 etc.
3) If 1 and 2 are true, AND C2 is <= C1 that is counted as an U/D-M.
Therefore, in the 5 rounds used for examples only Round 2 meets the criteria. (C1,C7,C8,C9) A 1 should appear in N15. There is a formula there as well that doesn't work.

For GIR Putt to be ADDED to N20 in the Table to the right, there must be a 1 in the GIR category. (C4,C9 etc.) If there is a 1 in those cells, whatever # is just to the right of Putts (B3,B8 etc.) MUST be ADDED to N20.

Those seem to be straight forward to me. No golf lingo involved.

For some reason sometimes a "circular reference in an open workbook" notice pops up when I input #'s in the Putts category. (C3,C8 etc.)


I made a little more progress with the U/D-M category.
The formula in cell N15 works for C1:C4. I need someone to adapt it to work for ALL of Column C.

Thanks

BL84
 

Attachments

  • 2015 Stats a(Mini).xlsx
    220.8 KB · Views: 1
Okay, I just fixed the 3-Putt + formula. Once again, just needed to hit the "array" buttons instead of just enter. Added >= and it worked. All I need now is for the formulas in N15 & N20 to be "expanded" to include B1:K800. Those are the U/D-M & GIR Putts Categories. They are RED in the April Table to the right. If anyone can help, many thanks.

BL84
 

Attachments

  • 2015 Stats a(Mini).xlsx
    220.9 KB · Views: 1
Please see if this gives you your desired result. The two red ranges that were present had formulas and i have understood that you wanted this:

For Udm, the formulas was =COUNTIFS(C4,"<1",C2,"<="&C1)

Since you wanted to include B1:K800, what i have undetstood that you wanted to this:

Round 1:

Check C4 to L4 if <1
Check C2 to L2 if < C1:L1 for respective values,
If both criteria meet then count 1
Do it for all 1 to nth rounds.

For simplification i have added the formula in column M which is a helper column.
The formula i have written is =SUMPRODUCT(("Round "&P$7=$A2:$A801)*($M$2:$M$801)) that need to be coppied against every row with label of "Round" in column A, you can see it in sheet.

Please check if it gives you the correct result [again only formulas given, no correct result mentioned if for the range B1:K800!!]

For the GIR-Putt case:

Your formulas was =IF(C4=1,C3*C4,0)

What i understood is that :

for C4:L4 if the value is 1 then C3:L3 to be multiplied with respective value of C4:L4, else give zero.

Wanted to do this for all nth rounds.

my formula is =SUMPRODUCT(("Round "&P$7=$A2:$A801)*($N$2:$N$801)) in helper column N gives sum for individual rounds.

Please check if this formula works for you.

Thanks,
 

Attachments

  • 2015 Stats a(Mini) 30.01.2015.xlsx
    113.2 KB · Views: 4
Please see if this gives you your desired result. The two red ranges that were present had formulas and i have understood that you wanted this:

For Udm, the formulas was =COUNTIFS(C4,"<1",C2,"<="&C1)

Since you wanted to include B1:K800, what i have undetstood that you wanted to this:

Round 1:

Check C4 to L4 if <1
Check C2 to L2 if < C1:L1 for respective values,
If both criteria meet then count 1
Do it for all 1 to nth rounds.

For simplification i have added the formula in column M which is a helper column.
The formula i have written is =SUMPRODUCT(("Round "&P$7=$A2:$A801)*($M$2:$M$801)) that need to be coppied against every row with label of "Round" in column A, you can see it in sheet.

Please check if it gives you the correct result [again only formulas given, no correct result mentioned if for the range B1:K800!!]

For the GIR-Putt case:

Your formulas was =IF(C4=1,C3*C4,0)

What i understood is that :

for C4:L4 if the value is 1 then C3:L3 to be multiplied with respective value of C4:L4, else give zero.

Wanted to do this for all nth rounds.

my formula is =SUMPRODUCT(("Round "&P$7=$A2:$A801)*($N$2:$N$801)) in helper column N gives sum for individual rounds.

Please check if this formula works for you.

Thanks,

Faseeh,

I'll feel kind of sad for you. You misunderstood or misread or didn't read one of my previous posts. Your formulas are beautiful. They actually work. However, they are not displaying the data correctly. Column C runs North/South. That is where the data being studied is found. YOUR data is coming from cells running East/West. When you included Column L in your formula, I knew something was wrong. ONLY data from Columns C thru Column K. That is why I requested a formula for Column C and then I would copy/adjust it for Column D thru Column K. Both formulas seem to count what they are supposed to count!! If you look at GIR-Putt in O20, you will notice there are 9 cells to the right P:X (not counting TOTAL). Round 1's stats went in P20. Round 2's stats went in Q20. Where would the "stats" from Round 10 + go? ALL of the GIR Putts in Column C should go in Cell P20. ALL of the GIR Putts in Column D would go in Q20. Same thing with the U/D-M. If you can create a formula for ONLY Column C that I can put in P15 and another formula ONLY for Column C that I can put in P20 you'd have it. You can count down as many "rounds" as you want, but the information needed is coming from Column C (North/South). You were counting ROWS (East/West). You most certainly now have a working formula, it just needs to be applied to Column C ONLY. Unless you want to make 9 formulas each for U/D-M & GIR Putt???? Not a good idea. You have shown me that a useable formula can be written. Now, can you adjust that formula to the North/South data in Column C ONLY? Sounds to me like if you do one more version you should have it. If you are willing. If what I've said here isn't clear, ask before spending time on changing the formula. You've done so much to help, I don't want you to do anymore than necessary.

Thanks for everything,

BL84

PS---When I mention Column C, I mean ALL of Column C.
 
Back
Top