Forum Discussion

Copper Contributor

Feb 25, 2020

Solved

'If' statement help needed

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

excel

Savia
Feb 25, 2020
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))

JKPieterse

Silver Contributor

Feb 25, 2020

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

Ollie1785

Copper Contributor

Feb 25, 2020

JKPieterse

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?

mathetes
Gold Contributor
Feb 25, 2020
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.
- Ollie1785
  Copper Contributor
  Feb 25, 2020
  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
  - PReagan
    Bronze Contributor
    Feb 25, 2020
    Hello Ollie1785,
    
    To attach a workbook, click reply then at the bottom you will see "Drag and drop here or browse files to attach"
Savia
Iron Contributor
Feb 25, 2020
Ollie1785 Did you check my reply? I think I already posted a solution.
- Ollie1785
  Copper Contributor
  Mar 31, 2020
  Hi Savia
  
  Yes thanks it does work. I have solved the issue. I appreciate your input.
  
  Regards