'If' statement help needed

[Ollie1785]

What I trying to do may not be possible. I use the statement below to indicate what I want to do rather than being an accurate formula. What I need to to test a cell for a value (first set  of brackets) and if its true execute the activity in the second set brackets and if not execute the activity in the third set of brackets. So my example is:

=IF('Start Sheet'!B5='L')THEN('Score Input'!$D$3>='Course Card'!B$11,1,0)ELSE('Score Input'!$D$3>='Course Card'!B$6,1,0)

So any takers? 

Thanks

[#2 · Accepted solution]

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))

[#3]

Your example does not tell me what you need exactly. Can you just explain in words?

[#4]

@Jan Karel Pieterse

Hi Jan, yes, sure. Thanks for coming back to me.

The example used here is more from by Basic programming days I guess in concept. Here is the formula again:

=IF('Start Sheet'!B5='L')THEN('Score Input'!$D$3>='Course Card'!B$11,1,0)ELSE('Score Input'!$D$3>='Course Card'!B$6,1,0)

The actual excel example may possibly be more like:

=IF('Start Sheet'!B5='L'),('Score Input'!$D$3>='Course Card'!B$11,1,0),('Score Input'!$D$3>='Course Card'!B$6,1,0)

I have created a golf scoring spreadsheet already (and working fine) only to find that if ladies have different Stroke Indexes on holes and different pars. So if the person is a lady then I have to do a the same calculation but from a different table to produce a correct points calculation. So if the person is a lady I will enter 'L' in Start Sheet!B5. If B5 = L I will have to go to the ladies table (Course Card'!B$11) to calculate the score from the ladies card. If there is no 'L' in Start Sheet!B5 then the calculation will be performed against the men's table 'Course Card'!B$6. Does that make sense?

[#5]

@Ollie1785 Did you check my reply?  I think I already posted a solution.

[#6]

@Ollie1785 

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.

[#7]

@Savia 

Thanks Savia, I'll check it out and let you know if it does the job!!

Regards

Keith

[#8]

@mathetes 

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

[#9]

Hello @Ollie1785,

To attach a workbook, click reply then at the bottom you will see "Drag and drop here or browse files to attach"

[#10]

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

[#11]

@PReagan 

Just to add that the Calculate sheet is where the main work is done. Parts of others sheets are locked to prevent over typing. 

[#12]

@Ollie1785 

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.

[#13]

@Ollie1785 

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 Played

 In turn "strokes received" equals:

If Stroke Index >= Playing Hcp THEN Strokes received = Par+1 ELSE Strokes received = Par

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

[#14]

Thanks Mathetes. I appreciate your input here.

[#15]

Hi @Savia

Yes thanks it does work. I have solved the issue. I appreciate your input.

Regards

[#16]

Yes, I think you are right. I have it working now with 'IF' statements, but it is really complex, and may be difficult to come back to in a year or two. But, we cant play golf at the moment anyhow due to the CoronaVirus so I'll put some effort into INDEX and INDIRECT over the coming weeks. Thanks for you input....Much appreciated. Keep well!