formula implementing

[Rome75]

I have 3 columns in Sheet1 with 3 data series...and I'd like to implement some formulas in sheet2 using a loop.
Basically, I'd like to write a loop able to implement the average formula of each columns in sheet1 and write the result in the cella A1, B1 and C1.
I don't know the symtax

I'd appreciate your help.
thanks

rome75

 

[Jooky68]

If i understand this correctly... you just want to take the average of each column.

simply in the cells where you want the average to come in save a1 on whatever sheet in the formula bar simply put

=AVERAGE(<name of sheet>!A1:A7)

A1:A7 is the range, simply adjust it to what values you need to take the average of. Do the same for B1 and C1. Whenever values are inputted into the columns A1,B1,C1 will automatically be updated..Is this what you needed?

 

[Rome75]

Yes I need something like that,
but I have to loop so How to do that?
In the sense that I'm trying to do something like this

For i = 1 To Sheet1.UsedRange.Columns.Count / 2

Sheet2.Cells(2, i + 1).FormulaR1C1 = &quot;=AVERAGE(Sheet1!R[2]C[i+1]:R[Sheet1.UsedRange.Rows.Count]C[i+1])&quot;

Next i
but it doesn't work!!

did you understand now?? I think I cannot use A1:A7 like you told to do what I want to do..right?

 

[Jooky68]

What i todl you before must be put right into the formula bar for the cell within excel...not in the vba coding. I dont think i quite understand what you are trying to do by the coding you just posted that cant be done using the built in average function within excel. Can you please clarify for me a little more. Sorry if im not getting this...

 

[Rome75]

I got it!! I just solved my problem.
I have two sheets
sheet1 with input data and sheet 2 where I want to put the results.
this is what I wanted to do and how I wrote it

For i = 1 To Sheet1.UsedRange.Columns.Count / 2
Sheet2.Cells(2, i + 1).Formula = _
&quot;=Average('Sheet1'!R1C&quot; & Format(i * 2) & &quot;:R&quot; & _
Format(Sheet1.UsedRange.Rows.Count) & &quot;C&quot; & Format(i * 2) & &quot&quot;
Next i