Hi All,

Using XL 2003.

The workbook is really driving Xl to the limits to calculate it all. The VBA
statement 'application.calculatefull' takes several minutes to complete.

As a result of this, some calculations are not correct, I'm using also
UDF's.

To give an idea:

Overall score B



11 12 Total/Avg. Pers %/Team Score

314 0 2618 114,1% C

1 0 35 83,3% 112,5% C C
14 5 143 97,3% C
12 5 183 66,3% D
24 9 457 174,4% A

1 0 35 107,7% 98,8% C C
12 1 98 88,4% C
9 1 125 99,2% C
14 6 282 113,1% C


11 & 12 represent the months, the figures below are calculated using an
array function.

The letters are calculated by an UDF. The overall score is also an UDF. For
some reasons the overall score is not always correct.

Clicking in the formula bar and <enter returns the correct value. So it
looks to me that the sequence in which XL performs the calculations is not
correct. Is there a way to say that the overall score should be calculated
as last?

With kind regards,

JP

If you post the code for your UDFs we can probably see whats wrong:
Maybe your UDF references cells that are included somewhere in the UDF
arguments?
(Excel uses the arguments to functions to determine the calculation
sequence)


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi All,

Using XL 2003.

The workbook is really driving Xl to the limits to calculate it all. The
VBA statement 'application.calculatefull' takes several minutes to
complete.

As a result of this, some calculations are not correct, I'm using also
UDF's.

To give an idea:

Overall score B



11 12 Total/Avg. Pers %/Team Score

314 0 2618 114,1% C

1 0 35 83,3% 112,5% C C
14 5 143 97,3% C
12 5 183 66,3% D
24 9 457 174,4% A

1 0 35 107,7% 98,8% C C
12 1 98 88,4% C
9 1 125 99,2% C
14 6 282 113,1% C


11 & 12 represent the months, the figures below are calculated using an
array function.

The letters are calculated by an UDF. The overall score is also an UDF.
For some reasons the overall score is not always correct.

Clicking in the formula bar and <enter returns the correct value. So it
looks to me that the sequence in which XL performs the calculations is not
correct. Is there a way to say that the overall score should be calculated
as last?

With kind regards,

JP

Hi Charles,

The Scores range is:

A B C D E
140% 120% 80% 60% 0%


Function Score(PersScore, Scores As Range) As String
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
Dim E As Double
Dim rngCell As Range

A = Scores(1, 1)
B = Scores(1, 2)
C = Scores(1, 3)
D = Scores(1, 4)
E = Scores(1, 5)

Select Case PersScore
Case ""
Score = ""
Case Is = A
Score = "A"
Case Is = B
Score = "B"
Case Is = C
Score = "C"
Case Is = D
Score = "D"
Case Is = E
Score = "E"
Case Else
Score = ""
End Select
End Function

Function OverallScore(Scores As Range) As String
Dim intCount As Integer
Dim intCountScores As Integer
Dim intTotalScores As Integer
Dim arrRange As Variant
Dim arrResult As Variant
Dim rngCell As Range

intCountScores = 0
intTotalScores = 0

For Each rngCell In Scores
If rngCell < "" Then
intCountScores = intCountScores + 1
intTotalScores = intTotalScores + Asc(UCase(rngCell)) - 64
End If
Next rngCell

arrRange = Array(0, _
intCountScores, _
intCountScores + intCountScores / 2 + 0.01, _
intCountScores * 2 + intCountScores / 2 + 0.01, _
intCountScores * 3 + intCountScores / 2 + 0.01, _
intCountScores * 4 + intCountScores / 2 + 0.01)

arrResult = Array("", "A", "B", "C", "D", "E")

If intCountScores = 0 Then
OverallScore = ""
Else
OverallScore = Application.WorksheetFunction.Lookup(intTotalScore s,
arrRange, arrResult)

End If

End Function

Thanks for your assitance.

With kind regards,

JP




"Charles Williams" wrote in message
...
If you post the code for your UDFs we can probably see whats wrong:
Maybe your UDF references cells that are included somewhere in the UDF
arguments?
(Excel uses the arguments to functions to determine the calculation
sequence)


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi All,

Using XL 2003.

The workbook is really driving Xl to the limits to calculate it all. The
VBA statement 'application.calculatefull' takes several minutes to
complete.

As a result of this, some calculations are not correct, I'm using also
UDF's.

To give an idea:

Overall score B



11 12 Total/Avg. Pers %/Team Score

314 0 2618 114,1% C

1 0 35 83,3% 112,5% C C
14 5 143 97,3% C
12 5 183 66,3% D
24 9 457 174,4% A

1 0 35 107,7% 98,8% C C
12 1 98 88,4% C
9 1 125 99,2% C
14 6 282 113,1% C


11 & 12 represent the months, the figures below are calculated using an
array function.

The letters are calculated by an UDF. The overall score is also an UDF.
For some reasons the overall score is not always correct.

Clicking in the formula bar and <enter returns the correct value. So it
looks to me that the sequence in which XL performs the calculations is
not correct. Is there a way to say that the overall score should be
calculated as last?

With kind regards,

JP

Hi JP,

I cannot see a dependency problem in your UDFs.

Try a one-off CalculateFullRebuild (or Ctrl/alt/shift/F9) to rebuild all the
dependency trees in case they are corrupted.


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi Charles,

The Scores range is:

A B C D E
140% 120% 80% 60% 0%


Function Score(PersScore, Scores As Range) As String
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
Dim E As Double
Dim rngCell As Range

A = Scores(1, 1)
B = Scores(1, 2)
C = Scores(1, 3)
D = Scores(1, 4)
E = Scores(1, 5)

Select Case PersScore
Case ""
Score = ""
Case Is = A
Score = "A"
Case Is = B
Score = "B"
Case Is = C
Score = "C"
Case Is = D
Score = "D"
Case Is = E
Score = "E"
Case Else
Score = ""
End Select
End Function

Function OverallScore(Scores As Range) As String
Dim intCount As Integer
Dim intCountScores As Integer
Dim intTotalScores As Integer
Dim arrRange As Variant
Dim arrResult As Variant
Dim rngCell As Range

intCountScores = 0
intTotalScores = 0

For Each rngCell In Scores
If rngCell < "" Then
intCountScores = intCountScores + 1
intTotalScores = intTotalScores + Asc(UCase(rngCell)) - 64
End If
Next rngCell

arrRange = Array(0, _
intCountScores, _
intCountScores + intCountScores / 2 + 0.01, _
intCountScores * 2 + intCountScores / 2 + 0.01, _
intCountScores * 3 + intCountScores / 2 + 0.01, _
intCountScores * 4 + intCountScores / 2 + 0.01)

arrResult = Array("", "A", "B", "C", "D", "E")

If intCountScores = 0 Then
OverallScore = ""
Else
OverallScore = Application.WorksheetFunction.Lookup(intTotalScore s,
arrRange, arrResult)

End If

End Function

Thanks for your assitance.

With kind regards,

JP




"Charles Williams" wrote in message
...
If you post the code for your UDFs we can probably see whats wrong:
Maybe your UDF references cells that are included somewhere in the UDF
arguments?
(Excel uses the arguments to functions to determine the calculation
sequence)


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi All,

Using XL 2003.

The workbook is really driving Xl to the limits to calculate it all. The
VBA statement 'application.calculatefull' takes several minutes to
complete.

As a result of this, some calculations are not correct, I'm using also
UDF's.

To give an idea:

Overall score B



11 12 Total/Avg. Pers %/Team Score

314 0 2618 114,1% C

1 0 35 83,3% 112,5% C C
14 5 143 97,3% C
12 5 183 66,3% D
24 9 457 174,4% A

1 0 35 107,7% 98,8% C C
12 1 98 88,4% C
9 1 125 99,2% C
14 6 282 113,1% C


11 & 12 represent the months, the figures below are calculated using an
array function.

The letters are calculated by an UDF. The overall score is also an UDF.
For some reasons the overall score is not always correct.

Clicking in the formula bar and <enter returns the correct value. So it
looks to me that the sequence in which XL performs the calculations is
not correct. Is there a way to say that the overall score should be
calculated as last?

With kind regards,

JP

Hi Charles,

What I have to add...

The range in Scores and OverallScore is not the same.

OverallScores is used in 15 sheets referring to the same range in each
sheet.

Because I have to exclude some values, I cannot use a contiguous range.
Therefore, the function is entered als

=OverallScore((W9:W30,W36:W35)). The same in each of the 15 sheets.

When I click in the formulabar and hit enter, the correct score for this
person is calculated but the same score is placed in all the other sheets,
e.g. doing this for a person having a C score and then for one with a B
score, everyone in the workbook receives a B-score.

When I change the function to =OverallScore((JP!W9:W30;JP!W36:W65)) then the
calculation is done correctly. Of course, I have to change this per sheet.

Does this sound as a known issue?

With kind regards,

JP





"Charles Williams" wrote in message
...
Hi JP,

I cannot see a dependency problem in your UDFs.

Try a one-off CalculateFullRebuild (or Ctrl/alt/shift/F9) to rebuild all
the dependency trees in case they are corrupted.


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi Charles,

The Scores range is:

A B C D E
140% 120% 80% 60% 0%


Function Score(PersScore, Scores As Range) As String
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
Dim E As Double
Dim rngCell As Range

A = Scores(1, 1)
B = Scores(1, 2)
C = Scores(1, 3)
D = Scores(1, 4)
E = Scores(1, 5)

Select Case PersScore
Case ""
Score = ""
Case Is = A
Score = "A"
Case Is = B
Score = "B"
Case Is = C
Score = "C"
Case Is = D
Score = "D"
Case Is = E
Score = "E"
Case Else
Score = ""
End Select
End Function

Function OverallScore(Scores As Range) As String
Dim intCount As Integer
Dim intCountScores As Integer
Dim intTotalScores As Integer
Dim arrRange As Variant
Dim arrResult As Variant
Dim rngCell As Range

intCountScores = 0
intTotalScores = 0

For Each rngCell In Scores
If rngCell < "" Then
intCountScores = intCountScores + 1
intTotalScores = intTotalScores + Asc(UCase(rngCell)) - 64
End If
Next rngCell

arrRange = Array(0, _
intCountScores, _
intCountScores + intCountScores / 2 + 0.01, _
intCountScores * 2 + intCountScores / 2 + 0.01, _
intCountScores * 3 + intCountScores / 2 + 0.01, _
intCountScores * 4 + intCountScores / 2 + 0.01)

arrResult = Array("", "A", "B", "C", "D", "E")

If intCountScores = 0 Then
OverallScore = ""
Else
OverallScore =
Application.WorksheetFunction.Lookup(intTotalScore s, arrRange, arrResult)

End If

End Function

Thanks for your assitance.

With kind regards,

JP




"Charles Williams" wrote in message
...
If you post the code for your UDFs we can probably see whats wrong:
Maybe your UDF references cells that are included somewhere in the UDF
arguments?
(Excel uses the arguments to functions to determine the calculation
sequence)


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi All,

Using XL 2003.

The workbook is really driving Xl to the limits to calculate it all.
The VBA statement 'application.calculatefull' takes several minutes to
complete.

As a result of this, some calculations are not correct, I'm using also
UDF's.

To give an idea:

Overall score B



11 12 Total/Avg. Pers %/Team Score

314 0 2618 114,1% C

1 0 35 83,3% 112,5% C C
14 5 143 97,3% C
12 5 183 66,3% D
24 9 457 174,4% A

1 0 35 107,7% 98,8% C C
12 1 98 88,4% C
9 1 125 99,2% C
14 6 282 113,1% C


11 & 12 represent the months, the figures below are calculated using an
array function.

The letters are calculated by an UDF. The overall score is also an UDF.
For some reasons the overall score is not always correct.

Clicking in the formula bar and <enter returns the correct value. So
it looks to me that the sequence in which XL performs the calculations
is not correct. Is there a way to say that the overall score should be
calculated as last?

With kind regards,

JP

Hi JP,

Yes, its a bug, UDFs do not work properly with multi-area ranges (partially
fixed in XL2003 & fixed in Excel 2007)

It would be safer to reprogram the udf to handle a variable number of input
Ranges using Optional or a ParamArray.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com


"JP Ronse" wrote in message
...
Hi Charles,

What I have to add...

The range in Scores and OverallScore is not the same.

OverallScores is used in 15 sheets referring to the same range in each
sheet.

Because I have to exclude some values, I cannot use a contiguous range.
Therefore, the function is entered als

=OverallScore((W9:W30,W36:W35)). The same in each of the 15 sheets.

When I click in the formulabar and hit enter, the correct score for this
person is calculated but the same score is placed in all the other sheets,
e.g. doing this for a person having a C score and then for one with a B
score, everyone in the workbook receives a B-score.

When I change the function to =OverallScore((JP!W9:W30;JP!W36:W65)) then
the calculation is done correctly. Of course, I have to change this per
sheet.

Does this sound as a known issue?

With kind regards,

JP





"Charles Williams" wrote in message
...
Hi JP,

I cannot see a dependency problem in your UDFs.

Try a one-off CalculateFullRebuild (or Ctrl/alt/shift/F9) to rebuild all
the dependency trees in case they are corrupted.


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi Charles,

The Scores range is:

A B C D E
140% 120% 80% 60% 0%


Function Score(PersScore, Scores As Range) As String
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
Dim E As Double
Dim rngCell As Range

A = Scores(1, 1)
B = Scores(1, 2)
C = Scores(1, 3)
D = Scores(1, 4)
E = Scores(1, 5)

Select Case PersScore
Case ""
Score = ""
Case Is = A
Score = "A"
Case Is = B
Score = "B"
Case Is = C
Score = "C"
Case Is = D
Score = "D"
Case Is = E
Score = "E"
Case Else
Score = ""
End Select
End Function

Function OverallScore(Scores As Range) As String
Dim intCount As Integer
Dim intCountScores As Integer
Dim intTotalScores As Integer
Dim arrRange As Variant
Dim arrResult As Variant
Dim rngCell As Range

intCountScores = 0
intTotalScores = 0

For Each rngCell In Scores
If rngCell < "" Then
intCountScores = intCountScores + 1
intTotalScores = intTotalScores + Asc(UCase(rngCell)) - 64
End If
Next rngCell

arrRange = Array(0, _
intCountScores, _
intCountScores + intCountScores / 2 + 0.01, _
intCountScores * 2 + intCountScores / 2 + 0.01, _
intCountScores * 3 + intCountScores / 2 + 0.01, _
intCountScores * 4 + intCountScores / 2 + 0.01)

arrResult = Array("", "A", "B", "C", "D", "E")

If intCountScores = 0 Then
OverallScore = ""
Else
OverallScore =
Application.WorksheetFunction.Lookup(intTotalScore s, arrRange,
arrResult)

End If

End Function

Thanks for your assitance.

With kind regards,

JP




"Charles Williams" wrote in message
...
If you post the code for your UDFs we can probably see whats wrong:
Maybe your UDF references cells that are included somewhere in the UDF
arguments?
(Excel uses the arguments to functions to determine the calculation
sequence)


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi All,

Using XL 2003.

The workbook is really driving Xl to the limits to calculate it all.
The VBA statement 'application.calculatefull' takes several minutes to
complete.

As a result of this, some calculations are not correct, I'm using also
UDF's.

To give an idea:

Overall score B



11 12 Total/Avg. Pers %/Team Score

314 0 2618 114,1% C

1 0 35 83,3% 112,5% C C
14 5 143 97,3% C
12 5 183 66,3% D
24 9 457 174,4% A

1 0 35 107,7% 98,8% C C
12 1 98 88,4% C
9 1 125 99,2% C
14 6 282 113,1% C


11 & 12 represent the months, the figures below are calculated using
an array function.

The letters are calculated by an UDF. The overall score is also an
UDF. For some reasons the overall score is not always correct.

Clicking in the formula bar and <enter returns the correct value. So
it looks to me that the sequence in which XL performs the calculations
is not correct. Is there a way to say that the overall score should be
calculated as last?

With kind regards,

JP

Hi Charles,

Thank you very much for this feedback. I could not understand why I had to
add the sheetname to get it working.

I'll use your suggestion to reprogram the UDF's.

Again, many thanks for your assistance and your time.

Witk kind regards,

JP


"Charles Williams" wrote in message
...
Hi JP,

Yes, its a bug, UDFs do not work properly with multi-area ranges
(partially fixed in XL2003 & fixed in Excel 2007)

It would be safer to reprogram the udf to handle a variable number of
input Ranges using Optional or a ParamArray.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com


"JP Ronse" wrote in message
...
Hi Charles,

What I have to add...

The range in Scores and OverallScore is not the same.

OverallScores is used in 15 sheets referring to the same range in each
sheet.

Because I have to exclude some values, I cannot use a contiguous range.
Therefore, the function is entered als

=OverallScore((W9:W30,W36:W35)). The same in each of the 15 sheets.

When I click in the formulabar and hit enter, the correct score for this
person is calculated but the same score is placed in all the other
sheets, e.g. doing this for a person having a C score and then for one
with a B score, everyone in the workbook receives a B-score.

When I change the function to =OverallScore((JP!W9:W30;JP!W36:W65)) then
the calculation is done correctly. Of course, I have to change this per
sheet.

Does this sound as a known issue?

With kind regards,

JP





"Charles Williams" wrote in message
...
Hi JP,

I cannot see a dependency problem in your UDFs.

Try a one-off CalculateFullRebuild (or Ctrl/alt/shift/F9) to rebuild all
the dependency trees in case they are corrupted.


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi Charles,

The Scores range is:

A B C D E
140% 120% 80% 60% 0%


Function Score(PersScore, Scores As Range) As String
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
Dim E As Double
Dim rngCell As Range

A = Scores(1, 1)
B = Scores(1, 2)
C = Scores(1, 3)
D = Scores(1, 4)
E = Scores(1, 5)

Select Case PersScore
Case ""
Score = ""
Case Is = A
Score = "A"
Case Is = B
Score = "B"
Case Is = C
Score = "C"
Case Is = D
Score = "D"
Case Is = E
Score = "E"
Case Else
Score = ""
End Select
End Function

Function OverallScore(Scores As Range) As String
Dim intCount As Integer
Dim intCountScores As Integer
Dim intTotalScores As Integer
Dim arrRange As Variant
Dim arrResult As Variant
Dim rngCell As Range

intCountScores = 0
intTotalScores = 0

For Each rngCell In Scores
If rngCell < "" Then
intCountScores = intCountScores + 1
intTotalScores = intTotalScores + Asc(UCase(rngCell)) - 64
End If
Next rngCell

arrRange = Array(0, _
intCountScores, _
intCountScores + intCountScores / 2 + 0.01, _
intCountScores * 2 + intCountScores / 2 + 0.01, _
intCountScores * 3 + intCountScores / 2 + 0.01, _
intCountScores * 4 + intCountScores / 2 + 0.01)

arrResult = Array("", "A", "B", "C", "D", "E")

If intCountScores = 0 Then
OverallScore = ""
Else
OverallScore =
Application.WorksheetFunction.Lookup(intTotalScore s, arrRange,
arrResult)

End If

End Function

Thanks for your assitance.

With kind regards,

JP




"Charles Williams" wrote in message
...
If you post the code for your UDFs we can probably see whats wrong:
Maybe your UDF references cells that are included somewhere in the UDF
arguments?
(Excel uses the arguments to functions to determine the calculation
sequence)


--
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JP Ronse" wrote in message
...
Hi All,

Using XL 2003.

The workbook is really driving Xl to the limits to calculate it all.
The VBA statement 'application.calculatefull' takes several minutes
to complete.

As a result of this, some calculations are not correct, I'm using
also UDF's.

To give an idea:

Overall score B



11 12 Total/Avg. Pers %/Team Score

314 0 2618 114,1% C

1 0 35 83,3% 112,5% C C
14 5 143 97,3% C
12 5 183 66,3% D
24 9 457 174,4% A

1 0 35 107,7% 98,8% C C
12 1 98 88,4% C
9 1 125 99,2% C
14 6 282 113,1% C


11 & 12 represent the months, the figures below are calculated using
an array function.

The letters are calculated by an UDF. The overall score is also an
UDF. For some reasons the overall score is not always correct.

Clicking in the formula bar and <enter returns the correct value. So
it looks to me that the sequence in which XL performs the
calculations is not correct. Is there a way to say that the overall
score should be calculated as last?

With kind regards,

JP