Solved: Join 2 formulas to work as 1

[Barryj]

I have included a attachment that displays the 2 formulas working individually, I have tried to get them to work together to no avail.

The first formula is if E9 is = or less than 18
The second formula is if E9 is greater than 18

.=IF(C13>=E9,1,0)+IF(E9+18<=C13,1)

.=IF(C13>18,IF(E9>18,IF(C13<E9,1,IF(C13>=E9,2))))

I hope this makes some sense to someone and that the example is reasonable clear.

Thanks again for any intrest.

[RonMcK3]

G'day, Barryj,


I have included a attachment that displays the 2 formulas working individually, I have tried to get them to work together to no avail.

The first formula is if E9 is = or less than 18
The second formula is if E9 is greater than 18

.=IF(C13>=E9,1,0)+IF(E9+18<=C13,1)

.=IF(C13>18,IF(E9>18,IF(C13<E9,1,IF(C13>=E9,2))))


From your worksheet, we have this additional information:


If C13 is less than 18 then if C13 = or is less than E9 then 1 will be shown, if C13 is greater than E9 up to 18 then 0 is shown.
[and]
If C13 is greater than 18 then 1 will be shown, when C13 = or is greater than E9 then 2 is shown.

My formulas, based on your rules, above, yield your data IF I swap all instances of C13 (Handicap) and E9 (Index) in the rules. My formulas are:

1.=IF(E$9<18,IF(E$9<=$C$13,1,IF(E$9-$C$13<=18,0)),0)
2.=IF(E$9>18,1,IF(E$9>=$C$13,2,0))

The combination of 1 + 2: (I substituted minus values in equation 2 piece to highlight which part of the combined equation was doing the work.)

=IF(E$9<18,IF(E$9<=$C$13,1,IF(E$9-$C$13<=18,0)),IF(E$9>18,-1,IF(E$9>=$C$13,-2,0)))

The only cell where my data fails to agree with yours is hole 18, I get 1 not 2 since e9>18 is satisfied thus we never test whether C13=E9.

I hope that this gets you closer to your desired result.

[Barryj]

When the 2 formulas are joined together the results should either be 0, 1, 0r 2 as per the example.

That is why I am having trouble getting this to work as one formula as the criteria chnges depending on cell C13 which then changes cell E9 the index.

Thanks for your time in looking at this.

[RonMcK3]

When the 2 formulas are joined together the results should either be 0, 1, 0r 2 as per the example.

That is why I am having trouble getting this to work as one formula as the criteria chnges depending on cell C13 which then changes cell E9 the index.

Thanks for your time in looking at this.
Barryj,

Try this one:

=IF(E$9<18,IF(E$9<=$C$13,1,IF(E$9-$C$13<=18,0)),IF(E$9>18,IF($C13<E$9,-1,IF($C13>=E$9,-2)),0))

Again, so we can see which part of the formula is working its magic, I gave the 2nd portion negative values. If you're happy with the result, change them to positives. The zero added among the closing parentheses gets rid of the "FALSE" message in the cells where the 2nd formula yields a 0.

This is the combination of my rewrite of the first formula and a portion of your second one, I copied your original #2 below and highlighted the portion I use in the combo formula.

=IF($C$13>18,IF(E$9>18,IF($C$13<E$9,1,IF($C$13>=E$9,2,0))))

Happy golfing!
:cool:

[Barryj]

I still cannot get the 2 formulas to work as one but they work perfectly by themselves, I may have to use a concatenate formula to achieve the result.

But on the lower if index is greater than 18 how do I get it to not show false when the handicapp is less than 19, I tried adding the )),0)) at the end but it did not remove the false from showing.

[RonMcK3]

Barryj,

Try this one:

=IF(E$9<18,IF(E$9<=$C$13,1,IF(E$9-$C$13<=18,0)),IF(E$9>18,IF($C13<E$9,-1,IF($C13>=E$9,-2)),0))


Barry,

This one formula should give you the results that you want; it is the combination of my first formula and the important (red) part of your second formula. Please look at the bottom of your worksheet in the file I returned, you should see the line at work.

Please change the -1 and -2 in the red part to 1 and 2, so, you get the desired positive numbers.

Drop another line if you are still having problems, or if I missed something in my formula.

In the meantime, I'm off to bed. :hi:

[daniel_d_n_r]

wRITE A vba Function

[Barryj]

I think a problem is that sometimes the index is as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18


.=IF(C13>=E9,1,0)+IF(E9+18<=C13,1)
This part of the formula works fine regardless if the handicapp is 0 to 36.

When the index is written say 12/28 and the handicapp determines which index is used that is where I am having problems.

But the other formula works when the handicapp is over 18, it's just getting them to work together.

.=IF(C13>18,IF(E9>18,IF(C13<E9,1,IF(C13>=E9,2))))

Currently I am using the first formula with fixed index from 1 to 18, but sometimes the courses have a rating system on holes 12/28 and the handicapp determines how many shots the person should receive, either 0,1,2.

That is why I am trying to get the 2 formulas to work together so that which ever system of rating index is used the formula will determine the correct amount of shots to be allocated.


Thanks again for your intrest and help.

[RonMcK3]

Daniel_d_n_r

Good point. Once Barry and I sort out the formulas and get a set that works well for him across the full range his data may fall in, I'll write a Function.

Thanks,

Ron

[Barryj]

What I have come up with that gives the desired result is to put the formula: .=IF(C13>=E9,1,0)+IF(E9+18<=C13,1) on one row.

Formula: .=IF(C13>18,IF(E9>18,IF(C13<E9,1,IF(C13>=E9,2)))) on another row.

Then placed this formula across the third row: : =SUM(E14:E15)

Then on the forth row the formula: .=IF(E16=3,E16-1,E14:E15)

And this gives the results that I need regardless of the index system used.

[david000]

What I have come up with that gives the desired result is to put the formula: .=IF(C13>=E9,1,0)+IF(E9+18<=C13,1) on one row.

Formula: .=IF(C13>18,IF(E9>18,IF(C13<E9,1,IF(C13>=E9,2)))) on another row.
These two lines don't play nice together...

C13>=E9,1
C13>=E9,2

They are both evaluating to true and coming up with a different unit.

[johnske]

your basic logic is =IF($C$13>18,"C13 is > 18","C13 is <= 18")


for the "C13 is > 18" condition:
you say "If C13 is greater than 18 then 1 will be shown, when C13 = or is greater than E9 then 2 is shown" this gives... IF($C$13>=E9,2,1)

putting this into your basic formula gives
=IF($C$13>18,IF($C$13>=E9,2,1),"C13 is <= 18")


for the "C13 is <= 18" condition:
you say "If C13 is less than 18 then if C13 = or is less than E9 then 1 will be shown, if C13 is greater than E9 up to 18 then 0 is shown" this gives... IF($C$13<=E9,1,0)

putting this into your formula gives
=IF($C$13>18,IF($C$13>=E9,2,1),IF($C$13<=E9,1,0))

[RonMcK3]

What I have come up with that gives the desired result is to put the formula:
Formula: .=IF(C13>=E9,1,0)+IF(E9+18<=C13,1) on one row.

Formula: .=IF(C13>18,IF(E9>18,IF(C13<E9,1,IF(C13>=E9,2)))) on another row.

Then placed this formula across the third row: : =SUM(E14:E15)

Then on the forth row the formula: .=IF(E16=3,E16-1,E14:E15)

And this gives the results that I need regardless of the index system used.
Barry,

I've translated your various formulas into a Function that I named Stableford. To load it, while in your workbook with this worksheet, open the VBE (click alt-F11), then, select Insert > Module, then copy and paste the code listed below into the module.

Next, in a cell under Hole 1, enter the following function:

=Stableford(e$9,$c$13) syntax is 'Stableford(cell with index, cell with handicap)

and highlight the range of holes and select Edit > Fill > Right.


Option Explicit

Public Function Stableford(indx As Long, handicap As Long)
Dim sum1, sum2, sum3 As Long

Formula1:
If handicap >= indx Then
sum1 = 1
End If
If indx + 18 <= handicap Then
sum2 = 1
End If

Stableford = sum1 + sum2

Formula2:
If handicap > 18 Then
If indx > 18 Then
If handicap < indx Then
sum3 = 1
Else
If handicap >= indx Then
sum3 = 2
End If
End If
End If
End If
Formula3:
Stableford = Stableford + sum3
Formula4:
If Stableford = 3 Then Stableford = 2

End Function


I believe this should work for you.

[RonMcK3]

What I have come up with that gives the desired result is to put the formula: .=IF(C13>=E9,1,0)+IF(E9+18<=C13,1) on one row.

Formula: .=IF(C13>18,IF(E9>18,IF(C13<E9,1,IF(C13>=E9,2)))) on another row.

Then placed this formula across the third row: : =SUM(E14:E15)

Then on the forth row the formula: .=IF(E16=3,E16-1,E14:E15)

And this gives the results that I need regardless of the index system used.

Barry,

I thought about it a bit more. Change your first formula as follows to add IF(E9>18 and I believe that you can get rid of the 4th row formula that forces the right answer in certain cases.

Revised Formula 1: =IF(E9>18,IF(C13>=E9,1,0))+IF(E9>18,IF(E9+18<=C13,1,0))

The function then becomes:


Public Function Stableford(indx As Long, handicap As Long)
Dim sum1, sum2, sum3 As Long
Formula1:
If indx < 18 Then
If handicap >= indx Then
sum1 = 1
End If
If indx + 18 <= handicap Then
sum2 = 1
End If
End If
Stableford = sum1 + sum2
Formula2:
If handicap > 18 Then
If indx > 18 Then
If handicap < indx Then
sum3 = 1
Else
If handicap >= indx Then
sum3 = 2
Else
sum3 = 0
End If
End If
End If
End If
Debug.Print "sum3=", sum3
Formula3:
Stableford = Stableford + sum3
End Function


Let me know what you think of this one. :)

[RonMcK3]

Barry,

I [should have not clicked submit so quickly] thought about it a bit more. Change your first formula as follows to add IF(E9>18 [no it's IF 'E9<18'] and I believe that you can get rid of the 4th row formula that forces the right answer in certain cases.

Revised Formula 1: =IF(E9>18,IF(C13>=E9,1,0))+IF(E9>18,IF(E9+18<=C13,1,0))

Corrected Formula 1: =IF(E9<18,IF(C18>=E9,1,0))+IF(E9<18,IF(E9+18<=C13,1,0))

:doh:

The function has it right.

[Barryj]

I have modified your last formula and it seems to work,
:=IF(E$9<=18,IF($C$13>=E$9,1,0))+IF(E$9<=18,IF(E$9+18<=$C$13,1,0))+IF(E$9>18,IF($C13<E$9,1,IF($C13>=E$9,2,1)),0) I high lighted in red the things I changed.

Thanks for the assistance Ron, just check that its working on your example.

[Barryj]

I have modified your last formula and it seems to work,
:=IF(E$9<=18,IF($C$13>=E$9,1,0))+IF(E$9<=18,IF(E$9+18<=$C$13,1,0))+IF(E$9>18,IF($C13<E$9,1,IF($C13>=E$9,2,1)),0) I high lighted in red the things I changed.

Thanks for the assistance Ron, just check that its working on your example.

[RonMcK3]

I have modified your last formula and it seems to work,
:=IF(E$9<=18,IF($C$13>=E$9,1,0))+IF(E$9<=18,IF(E$9+18<=$C$13,1,0))+IF(E$9>18,IF($C13<E$9,1,IF($C13>=E$9,2,1)),0) I high lighted in red the things I changed.

Thanks for the assistance Ron, just check that its working on your example.

Barry,

You're welcome. I see that formula 1 will apply in cases where the Index = 18.

I'm uploading a revised worksheet using your final formulas in the function as well.

Ron