Average Daily Sales

[Soniya]

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

 

[Bob Phillips]

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

=AVERAGE(IF(B2:B1000>0,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]

Thanks Bob,

I am showing the result in a UserForm

Thanks

 

[Tom Ogilvy]

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

 

[Jim May]

Tom:

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

TIA,
Jim

 

[Tom Ogilvy]

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.