Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of 12 cells
I need three formulas
1. Will find the first 12 non-empty cells in the range (a single row) and sum them, if there are not 12 non-empty cells it will average the non-empty cells and multiply by 12. 2. Will find the next 12 non-empty cells (13-24) and add them. If there are not 12 non- empty cells in this second group, average them and multiply by 12. 3. Will find the next 12 non-empty cells (25-36) and add them, if there arent 12 non-empty cells average the non-empty cells and multiply by 12. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of 12 cells
You say you want to find the first 12 non-empty cells in a row, but if there
are not 12, then average etc, thereafter repeat this excercise another two times. However if you find the first 12 non-empty cells, then there cannot be empty cells? Are you actually saying that you want a formula that will sum 12 adjacent cells, on conditions that they are all not empty, else average these 12 cells and multiply the result by 12. Iow, sum(C12:N12) if all are not empty. If there are empty cells in C12:N12, then average the non empty cells in C12:N12 and multiply by 12. -- Hth Kassie Kasselman Change xxx to hotmail "BillyRogers" wrote: I need three formulas 1. Will find the first 12 non-empty cells in the range (a single row) and sum them, if there are not 12 non-empty cells it will average the non-empty cells and multiply by 12. 2. Will find the next 12 non-empty cells (13-24) and add them. If there are not 12 non- empty cells in this second group, average them and multiply by 12. 3. Will find the next 12 non-empty cells (25-36) and add them, if there arent 12 non-empty cells average the non-empty cells and multiply by 12. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of 12 cells
kassie,
I need to use the formula on data that doesn't always start on the same column. There can be up to 3 years (36 months worth of data in the row-but not more) The sales data starts in different columns(months) - they don't all start on the same months. the data will be consecutive that is there won't be cells with sales data then empty cells followed by more sales data. ex 10 11 12 11 09 07 09 11 12 13 15 13 11 12 11 11 11 11 12 11 12 12 12 12 11 11 11 11 etc. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "kassie" wrote: You say you want to find the first 12 non-empty cells in a row, but if there are not 12, then average etc, thereafter repeat this excercise another two times. However if you find the first 12 non-empty cells, then there cannot be empty cells? Are you actually saying that you want a formula that will sum 12 adjacent cells, on conditions that they are all not empty, else average these 12 cells and multiply the result by 12. Iow, sum(C12:N12) if all are not empty. If there are empty cells in C12:N12, then average the non empty cells in C12:N12 and multiply by 12. -- Hth Kassie Kasselman Change xxx to hotmail "BillyRogers" wrote: I need three formulas 1. Will find the first 12 non-empty cells in the range (a single row) and sum them, if there are not 12 non-empty cells it will average the non-empty cells and multiply by 12. 2. Will find the next 12 non-empty cells (13-24) and add them. If there are not 12 non- empty cells in this second group, average them and multiply by 12. 3. Will find the next 12 non-empty cells (25-36) and add them, if there arent 12 non-empty cells average the non-empty cells and multiply by 12. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of 12 cells
ok that example didn't turn out right...it looks like the beginning spaces
were removed. the difficult part is that i don't know which column the data will start in and not all rows will have data starting in the same column - it depends on when that particular store was opened. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: kassie, I need to use the formula on data that doesn't always start on the same column. There can be up to 3 years (36 months worth of data in the row-but not more) The sales data starts in different columns(months) - they don't all start on the same months. the data will be consecutive that is there won't be cells with sales data then empty cells followed by more sales data. ex 10 11 12 11 09 07 09 11 12 13 15 13 11 12 11 11 11 11 12 11 12 12 12 12 11 11 11 11 etc. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "kassie" wrote: You say you want to find the first 12 non-empty cells in a row, but if there are not 12, then average etc, thereafter repeat this excercise another two times. However if you find the first 12 non-empty cells, then there cannot be empty cells? Are you actually saying that you want a formula that will sum 12 adjacent cells, on conditions that they are all not empty, else average these 12 cells and multiply the result by 12. Iow, sum(C12:N12) if all are not empty. If there are empty cells in C12:N12, then average the non empty cells in C12:N12 and multiply by 12. -- Hth Kassie Kasselman Change xxx to hotmail "BillyRogers" wrote: I need three formulas 1. Will find the first 12 non-empty cells in the range (a single row) and sum them, if there are not 12 non-empty cells it will average the non-empty cells and multiply by 12. 2. Will find the next 12 non-empty cells (13-24) and add them. If there are not 12 non- empty cells in this second group, average them and multiply by 12. 3. Will find the next 12 non-empty cells (25-36) and add them, if there arent 12 non-empty cells average the non-empty cells and multiply by 12. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of 12 cells
Since you asked in functions, will this do it with a formula.
=IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2) *12)) -- Don Guillett Microsoft MVP Excel SalesAid Software "BillyRogers" wrote in message ... I need three formulas 1. Will find the first 12 non-empty cells in the range (a single row) and sum them, if there are not 12 non-empty cells it will average the non-empty cells and multiply by 12. 2. Will find the next 12 non-empty cells (13-24) and add them. If there are not 12 non- empty cells in this second group, average them and multiply by 12. 3. Will find the next 12 non-empty cells (25-36) and add them, if there arent 12 non-empty cells average the non-empty cells and multiply by 12. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of 12 cells
Thanks for the help Don, but I need a formula that can "figure out" which
column to start in. (hence I used the phrase "find the first non-empty cell" in the range. Suppose that all the data will be in a given range say columns 10-50. Not all rows will start on the same columns (which represent months). There can be up to 36 columns (months) of data. What the data represents is store sales. What I need is three annual totals for these stores. Some stores don't have a full 36 months worth of data. Also, they didn't all start in the same month(column). It's kind of confusing to explain. If I could attacha screen shot it would make it much easier to understand. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Don Guillett" wrote: Since you asked in functions, will this do it with a formula. =IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2) *12)) -- Don Guillett Microsoft MVP Excel SalesAid Software "BillyRogers" wrote in message ... I need three formulas 1. Will find the first 12 non-empty cells in the range (a single row) and sum them, if there are not 12 non-empty cells it will average the non-empty cells and multiply by 12. 2. Will find the next 12 non-empty cells (13-24) and add them. If there are not 12 non- empty cells in this second group, average them and multiply by 12. 3. Will find the next 12 non-empty cells (25-36) and add them, if there arent 12 non-empty cells average the non-empty cells and multiply by 12. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of 12 cells
You can upload a screencap here (it's free) and then post a link to the
screencap: http://imageshack.us/ -- Biff Microsoft Excel MVP "BillyRogers" wrote in message ... Thanks for the help Don, but I need a formula that can "figure out" which column to start in. (hence I used the phrase "find the first non-empty cell" in the range. Suppose that all the data will be in a given range say columns 10-50. Not all rows will start on the same columns (which represent months). There can be up to 36 columns (months) of data. What the data represents is store sales. What I need is three annual totals for these stores. Some stores don't have a full 36 months worth of data. Also, they didn't all start in the same month(column). It's kind of confusing to explain. If I could attacha screen shot it would make it much easier to understand. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Don Guillett" wrote: Since you asked in functions, will this do it with a formula. =IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2) *12)) -- Don Guillett Microsoft MVP Excel SalesAid Software "BillyRogers" wrote in message ... I need three formulas 1. Will find the first 12 non-empty cells in the range (a single row) and sum them, if there are not 12 non-empty cells it will average the non-empty cells and multiply by 12. 2. Will find the next 12 non-empty cells (13-24) and add them. If there are not 12 non- empty cells in this second group, average them and multiply by 12. 3. Will find the next 12 non-empty cells (25-36) and add them, if there aren't 12 non-empty cells average the non-empty cells and multiply by 12. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of 12 cells
Ok thanks, I'll do that tomorrow. have to go now.
-- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "T. Valko" wrote: You can upload a screencap here (it's free) and then post a link to the screencap: http://imageshack.us/ -- Biff Microsoft Excel MVP "BillyRogers" wrote in message ... Thanks for the help Don, but I need a formula that can "figure out" which column to start in. (hence I used the phrase "find the first non-empty cell" in the range. Suppose that all the data will be in a given range say columns 10-50. Not all rows will start on the same columns (which represent months). There can be up to 36 columns (months) of data. What the data represents is store sales. What I need is three annual totals for these stores. Some stores don't have a full 36 months worth of data. Also, they didn't all start in the same month(column). It's kind of confusing to explain. If I could attacha screen shot it would make it much easier to understand. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Don Guillett" wrote: Since you asked in functions, will this do it with a formula. =IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2) *12)) -- Don Guillett Microsoft MVP Excel SalesAid Software "BillyRogers" wrote in message ... I need three formulas 1. Will find the first 12 non-empty cells in the range (a single row) and sum them, if there are not 12 non-empty cells it will average the non-empty cells and multiply by 12. 2. Will find the next 12 non-empty cells (13-24) and add them. If there are not 12 non- empty cells in this second group, average them and multiply by 12. 3. Will find the next 12 non-empty cells (25-36) and add them, if there aren't 12 non-empty cells average the non-empty cells and multiply by 12. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
How to use macros to copy a range of cells which can exclude some cells which I didn't want to be copied? | Excel Worksheet Functions | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
Pasting single cells from Word to multiple cells in Excel | Excel Worksheet Functions |