Forum Discussion
'If' statement help needed
- In Excel this would read as:
=IF('Start Sheet'!B5="L", IF('Score Input'!$D$3>='Course Card'!B$11, 1, 0), IF('Score Input'!$D$3>='Course Card'!B$6, 1, 0))
Is it possible for you to upload a sample of the spreadsheet or workbook you are working with?
I ask because I have a sense that there may be another way to do this, using a Table for some of the data, and one of the various LOOKUP functions rather than the conditional IF formulation. Especially if some day you are going to want to incorporate individual handicaps into this scoring sheet....
The potential weakness of using the IF function at the heart here is that you might end up hard-coding some variables that would become harder to maintain over the long term. That's only a potential problem, so I don't mean to over-emphasize it.
Hi Mathetes
Thanks ys I was wondering about Lookup. HLOOKUP could work. What is the best way to upload the spreadsheet? I am a bit of a novice in this group. Should I do it on something like dropbox or is there a facility within this group to do it?
Regards
Keith
Hello Ollie1785,
To attach a workbook, click reply then at the bottom you will see "Drag and drop here or browse files to attach"
Just to add that the Calculate sheet is where the main work is done. Parts of others sheets are locked to prevent over typing.
Oh my, you may be "rusty in Excel skills" but you are clearly using more than the most basic functions. So far as layout is concerned, however, what you've done is not only ambitious but a bit confusing--as can happen--for somebody other than yourself. For myself, I just don't have time today to try to navigate through all the different relationships (even while realizing that to you they're obvious and clear).
But I do wish you well...I'll have this in the back of my mind all day. I suspect that if I do come back to you, it will be with some recommendation to change the whole approach, changing the input side of things to more of a database of the results of all of the teams' rounds, laid out as a simple flat file database rather than as a visual portrayal of an actual scorecard. That's just a suspicion, but it's because I'm constitutionally averse to pretty (but less functional) input sheets; Excel is really good at extracting data from a flat file database (or even from multiple but related flat file databases--i.e., a relational database) but we make it hard for ourselves when we set up the raw data in ways that mimic paper (or score-card) record keeping methods.
PReagan yes thanks I think LOOKUP may well make things a bit easier. I set this file up using my rather rusty Excel skills so its pretty basic I think in structure. File is attached. Thanks for looking at it.
Being a golfer myself, I recognise what you are trying to achieve. Based on the Course Card, I believe you made the scoring and points calculation unnecessarily complex and it doesn't seem to be correct either.
I have added a calculation example below the Course Card using the standard formula for points per hole:
Points = 2 + Strokes received - Strokes PlayedIn turn "strokes received" equals:
If Stroke Index >= Playing Hcp THEN Strokes received = Par+1 ELSE Strokes received = ParSince your score card has different Pars and Stroke Indices for Men and Ladies, it becomes a bit more complicated. Hence, the use of INDIRECT and INDEX.
Perhaps this can inspire you to revise your scoring system.
