I have a data with duplicate name in column A and seal no in column which are unique in nature, can you help me in getting the data horizontally with text in column C falling in vertical below the Names. I think that an array formula will not help you with this task. Please see the following example that may help you: Enter five values to A1: A5 Use the following formula to get the first multiplier address in 25 resulting rows: ADDRESS(TRUNC((ROW()-6)/5+1,0),1) Use this formula to get the second multiplier address: ADDRESS(IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)),1) To get a value using the address, please enter the INDIRECT function. The final formula in rows A6: A31 will be as follows: =INDIRECT(ADDRESS(TRUNC((ROW()-6)/5+1,0),1))*INDIRECT(ADDRESS(IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)),1)). That was brilliant, Fedor! I applied your example solution to my dataset and got exactly what I requested.

Excel provides two types of array formulas: Array formulas that perform several calculations to generate a single result and array formulas that calculate multiple results. Some worksheet functions return arrays of values, or require an array of values as an argument. For more information, see Guidelines and examples of array formulas.

Unfortunately, I found out that Excel (2007) can only handle 32,000 rows for graphing or statistical analysis. Now am I thinking of condensing the 250,000 rows generated with your formula down to 25,000 rows. This can be done by sorting the dataset, then averaging each successive 10 rows. I tried that, but I am getting a moving average rather than averaging 10 rows to make one new row, then averaging the next 10 rows to make the second new row, and so on. Could you suggest an approach? I am sorry to trouble you again, but you are the maestro. Hi, Please could you help with a formula.

I am trying to work out a moving average for the following: Week Commencing Number Value 7 £2,000.00 37 £5,000.00 73 £397,270.97 40 £238,580.87 So for example week I'm using the formula =sum(d2/c2). I want a continuous formula that adds the weekly numbers as each week passes so for the second week the sum would be 7+37 =44 and the value would be £2,000 + 5,000 = 7,000 so £7,000/44.

I require the formula to be continuous as I have many weeks to work out the average. Thanks for your help Jason.

Does anyone know how to solve this problem? I want to be able to provide a validation list for a lot of the fields in my workbook.

I want to only use 1 formula for all the validation lists. My workbook has at least 8 sheets in it. One of those sheets is a definition sheet and it lists the names of each worksheet, the column header name, and a description of what I use the column for and has indicators as to whether or not the column should be validated with a list. If so, the list I want to be comprised to validate the cell would come from the contents of the definition sheet. I am just not grasping how to get the formula I want, though I know the answer is in there. I want to have a formula look up a specific item (say 'sam's apples) and then fill in the corresponding rows with the last year's sales by month.