ExcelBanter - View Single Post

If Exp1<Exp2...
If Exp1Exp2...
If Exp1= Exp3...
End If <=== get rid of this line
End Function

HTH
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

"Bill_De" wrote:

I don't know how to do this and keep it short. I have created an excel
workbook to do tournament events and the entire process is now automated
except for changing the start times of the events. I need a formula or custom
function that does 3 arguments. The one I created calculates the first two
arguments but not the third. I then tried to create a custom function but
that does not calculate the time either I get an error message. I have both
the formula and function for someone to review. Please help if you can.
Here is the formula and explination:

=If(and(SUM(M27,$AT$5,INDEX!$M$20)=(INDEX!M$21-$AT$5),iNDEX!$M$23,SUM(M27,$AT$5,INDEX!$M$20)) This would be if 10:00 =10:30,1:00,10:00
,+IF(SUM(M27,$AT$5,INDEX!$M$20)<=INDEX!$M$23,INDEX !$M$23,SUM(M27,$AT$5,INDEX!$M$20)) This would be if 10:00<=1:00,1:00,10:00
,+IF(SUM(M27,$AT$5,INDEX!$M$20)<=SUM(M3,$AT$5,INDE X!$M20),SUM(M27,$AT$5,INDEX!$M$20,INDEX!$M$22),SUM (M27,$AT$5,INDEX!$M$20)))
This would be if 10:00<=10:15,10:40,10:00

To sum up what I need to have the time calculation do is not enter any times
between for example 11:00 and 1:00 and then I need to make sure that the time
does not overlap a previous time. The third part of the formula makes sure
that there is at least 20 to 30 min from the end of the previous game time.

When this didn't work I tried creating a custom function. I assigned all the
dim statements as variants and then assigned names to each cell and
expression. However I received the following error message when running the
function: AN END IF STATEMENT MUST HAVE A CORRESPONDING IF STATEMENT.

Function Evtime(time1,time2)
Dim statements as variant

Gameint=Index!M20
Lunchbk=Index!M21
Addtm=Index!M22
Pmstart=Index!M23
Gametm=AT5
Exp1=(Time1+Gametm+Gameint)
Exp2=(Lunchbk-Gametm)
Exp3=(Time2+Gametm+Gameint)
Exp4=(Time1+Gametm+Gameint+Addtm)

Result 1 is the first half of the calculation

If Exp1<Exp2 then Result 1=Exp1 Else Result 1=Pmstart
If Exp1Exp2 Then Result 1=Exp1 Else Result 1=Pmstart
If Exp1= Exp3 then evtime=Result 1 Else Evtime=Exp4
End If
End Function

If anyone can help me with this problem and get me on the right track I
would be most greatful. As I said this is the only thing from stopping me
from having a completely automated scoring system.

Thanks and sorry to be so lengthy

Bill_De