Why use VBA, you can do it with a formula?

=AVERAGE(IF(B2:B10000,B2:B1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Soniya" wrote in message
ps.com...
Hello,

I have the following in a sheet.

Date Loc1 Loc2 Loc 3

These are the sales data for Location 1,2, 3 etc.

the locations are keep on adding and for the start date say 1/1/06 Loc1
data will be there and at a later date Loc2 data will appear and even
at a later date Loc3 data will start appearing.

I want to get an average daily sales for these locations.
since the data is extracted by using formula even if a Location is not
established it may appear zero sales.

How I can define a range from the first data till last data for each
location so that I can have daily average (by using VBA)

thanks

Thanks Bob,

I am showing the result in a UserForm

Thanks

Bob Phillips wrote:
Why use VBA, you can do it with a formula?

=AVERAGE(IF(B2:B10000,B2:B1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Soniya" wrote in message
ps.com...
Hello,

I have the following in a sheet.

Date Loc1 Loc2 Loc 3

These are the sales data for Location 1,2, 3 etc.

the locations are keep on adding and for the start date say 1/1/06 Loc1
data will be there and at a later date Loc2 data will appear and even
at a later date Loc3 data will start appearing.

I want to get an average daily sales for these locations.
since the data is extracted by using formula even if a Location is not
established it may appear zero sales.

How I can define a range from the first data till last data for each
location so that I can have daily average (by using VBA)

thanks

Textbox1.Value = Evaluate("AVERAGE(IF(B2:B10000,B2:B1000))")

--
Regards,
Tom Ogilvy


"Soniya" wrote in message
ps.com...
Thanks Bob,

I am showing the result in a UserForm

Thanks

Bob Phillips wrote:
Why use VBA, you can do it with a formula?

=AVERAGE(IF(B2:B10000,B2:B1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Soniya" wrote in message
ps.com...
Hello,

I have the following in a sheet.

Date Loc1 Loc2 Loc 3

These are the sales data for Location 1,2, 3 etc.

the locations are keep on adding and for the start date say 1/1/06 Loc1
data will be there and at a later date Loc2 data will appear and even
at a later date Loc3 data will start appearing.

I want to get an average daily sales for these locations.
since the data is extracted by using formula even if a Location is not
established it may appear zero sales.

How I can define a range from the first data till last data for each
location so that I can have daily average (by using VBA)

thanks

Tom:

Does Evaluate() have something to do with the
Fact that the formula enclosed s/b a CSE formula?

TIA,
Jim

"Tom Ogilvy" wrote in message
:

Textbox1.Value = Evaluate("AVERAGE(IF(B2:B10000,B2:B1000))")

--
Regards,
Tom Ogilvy


"Soniya" wrote in message
ps.com...

Thanks Bob,

I am showing the result in a UserForm

Thanks

Bob Phillips wrote:

Why use VBA, you can do it with a formula?

=AVERAGE(IF(B2:B10000,B2:B1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Soniya" wrote in message
ps.com...

Hello,

I have the following in a sheet.

Date Loc1 Loc2 Loc 3

These are the sales data for Location 1,2, 3 etc.

the locations are keep on adding and for the start date say 1/1/06 Loc1
data will be there and at a later date Loc2 data will appear and even
at a later date Loc3 data will start appearing.

I want to get an average daily sales for these locations.
since the data is extracted by using formula even if a Location is not
established it may appear zero sales.

How I can define a range from the first data till last data for each
location so that I can have daily average (by using VBA)

thanks

I don't see anything in the Excel help on CSE formulas.

If you mean the proper term, "array formula", then yes. The only way to
evaluate a formula that would normally require entry with Ctrl+Shift+enter
in a cell would be with evaluate - which acts as a "virtual" cell.

It would be better not to use "Slang" in the newsgroup in my opinion.

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:fGVTg.1900$b23.1070@dukeread07...
Tom:

Does Evaluate() have something to do with the
Fact that the formula enclosed s/b a CSE formula?

TIA,
Jim

"Tom Ogilvy" wrote in message
:

Textbox1.Value = Evaluate("AVERAGE(IF(B2:B10000,B2:B1000))")

--
Regards,
Tom Ogilvy


"Soniya" wrote in message
ps.com...

Thanks Bob,

I am showing the result in a UserForm

Thanks

Bob Phillips wrote:

Why use VBA, you can do it with a formula?

=AVERAGE(IF(B2:B10000,B2:B1000))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Soniya" wrote in message
ps.com...

Hello,

I have the following in a sheet.

Date Loc1 Loc2 Loc 3

These are the sales data for Location 1,2, 3 etc.

the locations are keep on adding and for the start date say 1/1/06
Loc1
data will be there and at a later date Loc2 data will appear and
even
at a later date Loc3 data will start appearing.

I want to get an average daily sales for these locations.
since the data is extracted by using formula even if a Location is
not
established it may appear zero sales.

How I can define a range from the first data till last data for each
location so that I can have daily average (by using VBA)

thanks