Problem: How to define my variable?

[APV]

Good morning everybody,

This is my code:

Dim Avrg As ????
Avrg = WorksheetFunction.Average(LS)

Dim FirstQ As ????
FirstQ = WorksheetFunction.CountIf(LS, "< " & Avrg)

Where LS is a Range of numbers with, usually, a lot of decimals. Using Variant to define Avrg (thats is simply the mean of LS) i obtain what im looking for (ex: 0,78271728392). BUT the problem is that when i use Avrg in the countif function i always obtain 0 (zero). The question is: Which type of variables I have to use in order to count LS without loosing decimals in the variable Avrg? (ex: Integer works but rounding 0,78271728392 in 1 is not what i want ).

Thank you

[Norie]

The code you posted works for me with both Avrg and FirstQ declared as Variant and LS being a range.

For example.

Please Login or Register  to view this content.

[APV]

Thank you Norie for your availability.

Unfotunately, what works for you it doesnt for me. The only difference with your code is that in my code LS range is defined with Cells(,).
I tried both with Variant and Double but i think the problem is that numbers in LS range have decimals and the countif function seems not to be able to "read" their average. For example if a put Integer to define the average, the function works!

[Norie]

Can you show how you defined LS?

[Norie]

Here's the code I posted using Cells and Range.

The range used in the code is A1:A15 and it's populated using the RAND function with decimal values under 1.

Please Login or Register  to view this content.

[APV]

Yes sure

Please Login or Register  to view this content.

x,y,z are other variables (Long), because all the code is inside a For loop, so they change every next.

[Norie]

Looks fine to me, and there's no reason using Cells instead of Range should make a difference, you should still end up with a range object.

Also, the numbers having decimals shouldn't make a difference either.

If you were to put the same formulas you have in the code into worksheet cells what results do you get?

[APV]

Thats really strange..

I tried exactly your code, and countif works! but it gave me a count of 15, thats the number of the entire range! How is it possible that a countif function above the mean of a range has the entire range self count as result?

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[APV]

Here it is

[Norie]

The code in that workbook works fine for me, mind you it is the code I posted as far as I can see.

[APV]

I can not believe it!! I found the problem! The only difference between me and you, is that may windows system was set up with "." as thousands separator and "," as decimals separator. I just changed this configuration from the control panel and now it works!!

OMG finally..

Thank you Norie

[Norie]

I was actually wondering if that might have been the problem but dismissed it as I thought things would automatically adjust.

Perhaps because VBA is kind of US-centric you need to use the US decimal/thousand separators.