• 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.

How do I add/average cells that are not consecutive, with data in-between?

BL84

Member
Hi guys,

This is my new "Entry Sheet". The formulas I need will be directed at the tables on this sheet. I have formulas to re-direct this info to my monthly sheets. I listed simple formulas that work for 1 round in the April table. I'll need formulas that will encompass all rounds for any given table. The totals from my monthly sheets are set to go to my "Year" sheet already. This is a par 35, nine hole course. 4-3-5-4-3-4-4-4-4 = 35. I don't know how to combine criteria inside a formula at all. I couldn't come up with formulas to show you for GIR Putt or Up/Down-Made. I'll explain what's needed there to help if I can.

GIR Putt: If there is a "bullseye" (CHAR 180) the putt total needs to be counted. There were 5 GIR in "round 1", which I copied to the cells next to the April table.

U/D-M: If there is NOT a "bullseye" (CHAR 180) the Score must be <=to par for the hole.

On my "April" sheet in the score category, you can see that data is set to appear from b5:b45 on the "Pre" sheet. The b45 part is just a guess that would be adjusted. If I use this "New Pre" sheet as my data entry, I'll need a formula that adds Score C1:C? for each month. I don't want to do C1+C6+C11 etc. The video I watched earlier talks about this issue. Something like: A:C, "Score", blah blah blah. I don't know the "signs" to put in etc. If someone can get the formulas to work on this table, I should be able to figure out how to transfer that data forward.

Thanks for any help,

BL84
 

Attachments

  • 2015 Stats (Colors).xlsx
    213.7 KB · Views: 4
The formula you would be looking for is:
=SUMIF('New Pre'!$B:$B,"Score",'New Pre'!C:C)

Copy to the right to get the scores for hole 2, 3, ..., 9.
Formula says to look in column B for word "Score", and everywhere it finds it, add the number from col C. As you copy to the right, the first array will stay looking at col B, but the latter will shift to the right.
 
The formula you would be looking for is:
=SUMIF('New Pre'!$B:$B,"Score",'New Pre'!C:C)

Copy to the right to get the scores for hole 2, 3, ..., 9.
Formula says to look in column B for word "Score", and everywhere it finds it, add the number from col C. As you copy to the right, the first array will stay looking at col B, but the latter will shift to the right.


That formula will be helpful for my monthly sheets. I applied it to "April" and it worked just fine. However, I don't know formulas for the categories in the table I provided. I need a formula for each of those. I had a formula set already to count Par-Bogey-Birdie-Other-Eagle on my monthly sheet based on scores entered, but now I can't use that. Also, I input this formula in L1000. =average('New Pre'!$B:$B,"Score",'New Pre'!L:L) and this is what appeared...###. If you are willing, I need formula help for each category. You can see the formula I had for Par-Bogey etc. on the "April" sheet B:J & 5-9. Once again, if you are willing we should start with the table listed on the New Pre sheet. I need a formula for 1 hole in each category and I'll copy to the other holes. If you are still with me, how about doing them in order? GIR first? Seems like the formulas would be VERY similar with a few key components that are different. Whew!! I hope this is worth all your time. I know in the end it will be for me. It's difficult for me to lose some formulas that worked for me even though I know I'll gain new ones that will be more effective.

Thanks

BL84
 
That formula will be helpful for my monthly sheets. I applied it to "April" and it worked just fine. However, I don't know formulas for the categories in the table I provided. I need a formula for each of those. I had a formula set already to count Par-Bogey-Birdie-Other-Eagle on my monthly sheet based on scores entered, but now I can't use that. Also, I input this formula in L1000. =average('New Pre'!$B:$B,"Score",'New Pre'!L:L) and this is what appeared...###. If you are willing, I need formula help for each category. You can see the formula I had for Par-Bogey etc. on the "April" sheet B:J & 5-9. Once again, if you are willing we should start with the table listed on the New Pre sheet. I need a formula for 1 hole in each category and I'll copy to the other holes. If you are still with me, how about doing them in order? GIR first? Seems like the formulas would be VERY similar with a few key components that are different. Whew!! I hope this is worth all your time. I know in the end it will be for me. It's difficult for me to lose some formulas that worked for me even though I know I'll gain new ones that will be more effective.

Thanks

BL84
I forgot the "IF". That one works now too.
 
The formula you would be looking for is:
=SUMIF('New Pre'!$B:$B,"Score",'New Pre'!C:C)

Copy to the right to get the scores for hole 2, 3, ..., 9.
Formula says to look in column B for word "Score", and everywhere it finds it, add the number from col C. As you copy to the right, the first array will stay looking at col B, but the latter will shift to the right.


I used that formula for GIR, Fairways, and Overall Average Score. I need help with the other categories on the table @ Q64 "New Pre" sheet. For the 1-2-3+ Putt category, somehow the numbers 1,2,3(4...didn't have any of those last year, but you never know) need to be in the formula. I have formulas that work with a single round, but I need a formula that will include all rounds in a certain range. The next 2 categories after Putting need formulas. I just didn't know how to write the formulas. They are not difficult, just don't know the "grammar" to write them. If I can help explain in some way, please let me know.

Thanks

BL84
 
The formula you would be looking for is:
=SUMIF('New Pre'!$B:$B,"Score",'New Pre'!C:C)

Copy to the right to get the scores for hole 2, 3, ..., 9.
Formula says to look in column B for word "Score", and everywhere it finds it, add the number from col C. As you copy to the right, the first array will stay looking at col B, but the latter will shift to the right.

Okay, here's what I have now. 6 of the 8 categories in the April table in the "New Pre" sheet are good to go. I just needed to use a countifs formula instead of countif. It accepted the last part of the data entered then. I still need the GIR Putts & U/D-M categories. Also, on the "April" sheet the Par-Bogey-Birdie-Other-Eagle cells need to be adapted to the range of SCORES in the "New Pre" sheet. I can't figure out how to do the 1st part of the formula. The end of the formula is okay, I think. Here's a sentence describing what the formula should look like for GIR Putts & U/D-M.

GIR Putts: If the GIR cell (C3) has a 1 in it, the putts (C2) can be added.
For all rounds in the included range for both of these categories.

U/D-M: If there is not a 1 in the GIR cell, count 1 if the SCORE is <= to par for the hole. 4 3 5 4 3 4 4 4 4 = 35 is par for the course.

Just 3 formulas to go!

Thanks

BL84
 
Back
Top