I made a formula for automatically figuring our Golf handicaps, the formula works great, but how can I leave the handicap in the weeks we haven't played yet to remain blank, until data is entered in box above? See pic included for formula used. Also can i write this formula in a shortened form?
Thanks for any help
hdcp pic.PNG
Welcome to the forum!
Like this for K4:
=IF(K3="","",your_formula)
And the formula:
=(SUM($C3:K3)-(rows($C3:K3)*36))*0.8
So, in all:
=IF(K3="","",(SUM($C3:K3)-(rows($C3:K3)*36))*0.8)
Last edited by AliGW; 02-16-2019 at 12:37 PM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
You might want to check the results of that formula, Ali!
ROWS($C3:K3) will give a single 1, not an array of 1's.
This one in C3, then dragged right will give the correct handicap scores.
=IF(C3="","",ROUND(AVERAGE(INDEX($C3:C3-36,))*0.8,1))
I wasn't trying to give an array of 1s, Jason - I was trying to return the number of rows in the range defined.
The OP has shown 36 being subtracted from each value in the range, not just once from the range. In what way do you think my formula does not do the same as the one in the image?
Exactly that way, Ali. Your formula only subtracts 36 once, not from each value.
Ah, yes!
I meant this:
=IF(K3="","",(SUM($C3:K3)-(COLUMNS($C3:K3)*36))*0.8)
Doh!!!
You need stronger coffee, Ali
You would need something like this to get the correct result with your method.
=IF(K3="","",((SUM($C3:K3)-(COLUMNS($C3:K3)*36))*0.8)/COLUMNS($C3:K3))
Looking at yours, I just realised I can drop the index array from my formula by moving the subtraction.
=IF(C3="","",ROUND((AVERAGE($C3:C3)-36)*0.8,1))
Last edited by jason.b75; 02-16-2019 at 02:11 PM.
Possibly - but the OP has been back twice and not bothered to comment on either of our offers of help, so I'm not going to bother any more. Have a nice evening, Jason!
Got it Thanks for everyones help. I did work with this formula below
=IF(K3="","",(AVERAGE(C3-36,D3-36,E3-36,F3-36,G3-36,H3-36,I3-36,K3-36)*0.8))
Sorry for not Commenting, I was retrying formulas u suggested and trying to solve when i was still getting errors. i was doing trial and error. lots of errors, but we did get there, and Thank you very much for your help. i just noticed your last post and will also try it with that formula. Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks