Application.worksheetfunction
 

Hello
Im trying to create a function for a userform that averages a range on a
sheet based on weather the values are not = "".

EX: the formula in my cells on the sheet (time cells) is this:
=IF(ISBLANK($B7),"",SUM(B7:E7))

Im not sure why, but even on my sheet I cannot get the right answer but
anyways:

On the userform, Im taking the average of cells F3:F53
I ve tried the Application.WorksheetFunction.Sumif
(F3:F53,"<""""")/Application.WorksheetFunction.CountIf(F3:F53,"<"" """)

But as most of you probably know, it did not work (overflow).
I tried placing the range into a range variable set fr=range("Times!F3:F53")
and using this instead of the range in the worksheetfunction formula.

How can I place this into a textbox on my userform as the average of that
range (assuming there will be values that have a "" value?

Thank you so much
Terry V

Application.worksheetfunction
 

the overflow error is probably caused by the fact that you assign the
result to an integer and the result 32767

assign the result to a double or variant.

count counts the numeric entries
countA counts all non-blank entries
countBlank counts the blanks.


Sub Foo()
Dim x As Double, y As Double
Dim n As Long, m As Long

Dim r As Range
Set r = Range("F3:F53")
With Application.WorksheetFunction
'use range variable
x = .Sum(r) / .Count(r)
'use range directly in arguments
y = .Sum(Range("F3:F53")) / .Count(Range("F3:F53"))
'count the blanks can be done several ways
n = .CountIf(Range("F3:F53"), "=" & vbNullString)
m = .CountBlank(Range("F3:F53"))
End With
'now have a look at the values of the variables.
Application.VBE.Windows("Locals").Visible = True
Stop
End Sub

Sub Overflow()
'this will cause overflow!
Dim i As Integer
i = 32767 'ok
i = 32768 'ouch!
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Terry V" wrote:

Hello
Im trying to create a function for a userform that averages a range on
a sheet based on weather the values are not = "".

EX: the formula in my cells on the sheet (time cells) is this:
=IF(ISBLANK($B7),"",SUM(B7:E7))

Im not sure why, but even on my sheet I cannot get the right answer
but anyways:

On the userform, Im taking the average of cells F3:F53
I ve tried the Application.WorksheetFunction.Sumif
(F3:F53,"<""""")/Application.WorksheetFunction.CountIf(F3:F53,"<"" """
)

But as most of you probably know, it did not work (overflow).
I tried placing the range into a range variable set
fr=range("Times!F3:F53") and using this instead of the range in the
worksheetfunction formula.

How can I place this into a textbox on my userform as the average of
that range (assuming there will be values that have a "" value?

Thank you so much
Terry V

Application.worksheetfunction
 

You want to get the average of the range F3:F53, ignoring cells that contain
empty text or are blank, right?

AVERAGE ignores blanks and text, so why can't you use just

A = Application.SUM(Range("F3:F53"))

instead of SUMIF and COUNTIF?


On Mon, 11 Oct 2004 09:29:24 GMT, "Terry V" wrote:

Hello
Im trying to create a function for a userform that averages a range on a
sheet based on weather the values are not = "".

EX: the formula in my cells on the sheet (time cells) is this:
=IF(ISBLANK($B7),"",SUM(B7:E7))

Im not sure why, but even on my sheet I cannot get the right answer but
anyways:

On the userform, Im taking the average of cells F3:F53
I ve tried the Application.WorksheetFunction.Sumif
(F3:F53,"<""""")/Application.WorksheetFunction.CountIf(F3:F53,"<"" """)

But as most of you probably know, it did not work (overflow).
I tried placing the range into a range variable set fr=range("Times!F3:F53")
and using this instead of the range in the worksheetfunction formula.

How can I place this into a textbox on my userform as the average of that
range (assuming there will be values that have a "" value?

Thank you so much
Terry V

Application.worksheetfunction
 

Or did you mean

A = Application.Average(Range("F3:F53"))

--
Regards,
Tom Ogilvy


"Myrna Larson" wrote in message
...
You want to get the average of the range F3:F53, ignoring cells that
contain
empty text or are blank, right?

AVERAGE ignores blanks and text, so why can't you use just

A = Application.SUM(Range("F3:F53"))

instead of SUMIF and COUNTIF?


On Mon, 11 Oct 2004 09:29:24 GMT, "Terry V" wrote:

Hello
Im trying to create a function for a userform that averages a range on a
sheet based on weather the values are not = "".

EX: the formula in my cells on the sheet (time cells) is this:
=IF(ISBLANK($B7),"",SUM(B7:E7))

Im not sure why, but even on my sheet I cannot get the right answer but
anyways:

On the userform, Im taking the average of cells F3:F53
I ve tried the Application.WorksheetFunction.Sumif
(F3:F53,"<""""")/Application.WorksheetFunction.CountIf(F3:F53,"<"" """)

But as most of you probably know, it did not work (overflow).
I tried placing the range into a range variable set
fr=range("Times!F3:F53")
and using this instead of the range in the worksheetfunction formula.

How can I place this into a textbox on my userform as the average of that
range (assuming there will be values that have a "" value?

Thank you so much
Terry V

Application.worksheetfunction
 

Yes, I think I did! Thank's for catching that!

On Mon, 11 Oct 2004 15:53:57 -0400, "Tom Ogilvy" wrote:

Or did you mean

A = Application.Average(Range("F3:F53"))

Application.worksheetfunction
 

I need to be able to average the column. In the column, I will have blank
strings ( "" ) because of the formula I have. So I cannot use Average
because it averages "all values".
So I figured if I sum all cells that are <"" and divide it by the count of
all cells <"", it would give me the results I was hoping for.

Hence the
Application.WorksheetFunction.Sumif()/Application.WorksheetFunction.Countif(
)

Thank you
TerryV

"Tom Ogilvy" wrote in message
...
Or did you mean

A = Application.Average(Range("F3:F53"))

--
Regards,
Tom Ogilvy


"Myrna Larson" wrote in message
...
You want to get the average of the range F3:F53, ignoring cells that
contain
empty text or are blank, right?

AVERAGE ignores blanks and text, so why can't you use just

A = Application.SUM(Range("F3:F53"))

instead of SUMIF and COUNTIF?


On Mon, 11 Oct 2004 09:29:24 GMT, "Terry V" wrote:

Hello
Im trying to create a function for a userform that averages a range on
a
sheet based on weather the values are not = "".

EX: the formula in my cells on the sheet (time cells) is this:
=IF(ISBLANK($B7),"",SUM(B7:E7))

Im not sure why, but even on my sheet I cannot get the right answer but
anyways:

On the userform, Im taking the average of cells F3:F53
I ve tried the Application.WorksheetFunction.Sumif

(F3:F53,"<""""")/Application.WorksheetFunction.CountIf(F3:F53,"<"" """)

But as most of you probably know, it did not work (overflow).
I tried placing the range into a range variable set
fr=range("Times!F3:F53")
and using this instead of the range in the worksheetfunction formula.

How can I place this into a textbox on my userform as the average of
that
range (assuming there will be values that have a "" value?

Thank you so much
Terry V

Application.worksheetfunction
 

Thank you
This gives me a good Idea of the different ways to count. I like the example
you gave because I knew nothing about the Locals Windows... which was really
helpful...

Again, Thank you so much
Terry V
"keepITcool" wrote in message
...
the overflow error is probably caused by the fact that you assign the
result to an integer and the result 32767

assign the result to a double or variant.

count counts the numeric entries
countA counts all non-blank entries
countBlank counts the blanks.


Sub Foo()
Dim x As Double, y As Double
Dim n As Long, m As Long

Dim r As Range
Set r = Range("F3:F53")
With Application.WorksheetFunction
'use range variable
x = .Sum(r) / .Count(r)
'use range directly in arguments
y = .Sum(Range("F3:F53")) / .Count(Range("F3:F53"))
'count the blanks can be done several ways
n = .CountIf(Range("F3:F53"), "=" & vbNullString)
m = .CountBlank(Range("F3:F53"))
End With
'now have a look at the values of the variables.
Application.VBE.Windows("Locals").Visible = True
Stop
End Sub

Sub Overflow()
'this will cause overflow!
Dim i As Integer
i = 32767 'ok
i = 32768 'ouch!
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Terry V" wrote:

Hello
Im trying to create a function for a userform that averages a range on
a sheet based on weather the values are not = "".

EX: the formula in my cells on the sheet (time cells) is this:
=IF(ISBLANK($B7),"",SUM(B7:E7))

Im not sure why, but even on my sheet I cannot get the right answer
but anyways:

On the userform, Im taking the average of cells F3:F53
I ve tried the Application.WorksheetFunction.Sumif
(F3:F53,"<""""")/Application.WorksheetFunction.CountIf(F3:F53,"<"" """
)

But as most of you probably know, it did not work (overflow).
I tried placing the range into a range variable set
fr=range("Times!F3:F53") and using this instead of the range in the
worksheetfunction formula.

How can I place this into a textbox on my userform as the average of
that range (assuming there will be values that have a "" value?

Thank you so much
Terry V

Application.worksheetfunction
 

PLEASE..... look at Help for the AVERAGE function. It does NOT average "all
values". It averages all NUMBERS.

Here's the relevant statement from Help: "If an array or reference argument
contains text, logical values, or empty cells, those values are ignored;
however, cells with the value zero are included."

Why don't you try your more complicated formula and compare the result with a
simple AVERAGE function. I'm sure that if you've described your situation
correctly, the results will be identical.

On Tue, 12 Oct 2004 03:29:01 GMT, "Terry V" wrote:

I need to be able to average the column. In the column, I will have blank
strings ( "" ) because of the formula I have. So I cannot use Average
because it averages "all values".
So I figured if I sum all cells that are <"" and divide it by the count of
all cells <"", it would give me the results I was hoping for.

Hence the
Application.WorksheetFunction.Sumif()/Application.WorksheetFunction.Countif(
)

Thank you
TerryV

"Tom Ogilvy" wrote in message
...
Or did you mean

A = Application.Average(Range("F3:F53"))

--
Regards,
Tom Ogilvy


"Myrna Larson" wrote in message
...
You want to get the average of the range F3:F53, ignoring cells that
contain
empty text or are blank, right?

AVERAGE ignores blanks and text, so why can't you use just

A = Application.SUM(Range("F3:F53"))

instead of SUMIF and COUNTIF?


On Mon, 11 Oct 2004 09:29:24 GMT, "Terry V" wrote:

Hello
Im trying to create a function for a userform that averages a range on
a
sheet based on weather the values are not = "".

EX: the formula in my cells on the sheet (time cells) is this:
=IF(ISBLANK($B7),"",SUM(B7:E7))

Im not sure why, but even on my sheet I cannot get the right answer but
anyways:

On the userform, Im taking the average of cells F3:F53
I ve tried the Application.WorksheetFunction.Sumif

(F3:F53,"<""""")/Application.WorksheetFunction.CountIf(F3:F53,"<"" """)

But as most of you probably know, it did not work (overflow).
I tried placing the range into a range variable set
fr=range("Times!F3:F53")
and using this instead of the range in the worksheetfunction formula.

How can I place this into a textbox on my userform as the average of
that
range (assuming there will be values that have a "" value?

Thank you so much
Terry V