Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average Daily Sales
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average Daily Sales
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average Daily Sales
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average Daily Sales
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average Daily Sales
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Daily Sales to Date calculation | Excel Discussion (Misc queries) | |||
How do I set up a daily average of unit sales formula | Excel Discussion (Misc queries) | |||
How do I set up a daily average of unit sales formula | Excel Programming | |||
track daily sales | Excel Discussion (Misc queries) |