Hello, I need help with a couple things...some background...I am managing a spreadsheet for golf scores for our league. There are seven weeks. My questions are...
1) For the first week I will enter everyones score in column B. Column C I am creating a formula to subtract the par of the golf course that day from the score the person shot to create a formula. Everything is correct in my formula, but our maximum handicap is 30, and as you can see cell C6 exceeds that number. How can I create this formula so if someones handicap exceeds 30 it still only lists 30? I will need to do this every week in columns F, L, R, X, AD, and AJ.
2) I am trying to make columns F, G, and H only list a value when I have a score added in column E. I have column F correct with the exception of limiting the value as mentioned above. And I think column H is correct, but column G I am having trouble with. I want an average of column C and F, but only when there is a value in column E. As it is now, it looks fine, but when I delete the score of 88 in E4, the cell has ##### in it. I just want the entire sheet blank until I add the score for that week so when we are on say week 2, there isn't all these numbers and formulas in the following weeks.
3) for columns AO, AP, and AQ I need to do averages. I will just explain column AO as if I know how to do that I can do AP and AQ myself. So AO I want averages of columns B, E, K, Q, W, AC, and AI, but I want this to update weekly. So If I were to put the formula in of =SUM(B4,E4,K4,Q4,W4,AC4,AI4)/7 but it is only week 2, it will be dividing those two scores by 7, giving me an innaccurate number. How can I fix this?
1) For the first week I will enter everyones score in column B. Column C I am creating a formula to subtract the par of the golf course that day from the score the person shot to create a formula. Everything is correct in my formula, but our maximum handicap is 30, and as you can see cell C6 exceeds that number. How can I create this formula so if someones handicap exceeds 30 it still only lists 30? I will need to do this every week in columns F, L, R, X, AD, and AJ.
2) I am trying to make columns F, G, and H only list a value when I have a score added in column E. I have column F correct with the exception of limiting the value as mentioned above. And I think column H is correct, but column G I am having trouble with. I want an average of column C and F, but only when there is a value in column E. As it is now, it looks fine, but when I delete the score of 88 in E4, the cell has ##### in it. I just want the entire sheet blank until I add the score for that week so when we are on say week 2, there isn't all these numbers and formulas in the following weeks.
3) for columns AO, AP, and AQ I need to do averages. I will just explain column AO as if I know how to do that I can do AP and AQ myself. So AO I want averages of columns B, E, K, Q, W, AC, and AI, but I want this to update weekly. So If I were to put the formula in of =SUM(B4,E4,K4,Q4,W4,AC4,AI4)/7 but it is only week 2, it will be dividing those two scores by 7, giving me an innaccurate number. How can I fix this?