A few keystrokes shorter:

Q1:

=IF(N1="","",E1)

Q2 copied down as needed:

=IF(N2="","",IF(COUNTIF(N$1:N1,"*"),AVERAGE(INDEX( E:E,LOOKUP(2,1/(N$1:N1<""),ROW(N:N))+1):E2),AVERAGE(E$1:E2)))

Assuming the empty cells are empty and don't contain formula blanks.


--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
Ooops, my mistake. Forgot to change it over from my workbook I was in. All
the I's should be Q's (the column the formula is in)

=IF(N2="","",IF(COUNT($Q$1:Q1)=0,AVERAGE($E$1:E2), AVERAGE(OFFSET($E$1,SUMPR*ODUCT(MAX(ROW($Q$1:Q1)*( $Q$1:Q1<""))),0,ROW()-SUMPRODUCT(MAX(ROW($Q$1:Q1)**(Q$1:Q1<""))),1))))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Redi" wrote:

Thanks for your response.
Whats in column "I"?





On Jul 8, 11:17 am, Luke M wrote:
In Q1:
=IF(N1="","",E1)
In Q2:
=IF(N2="","",IF(COUNT($I$1:I1)=0,AVERAGE($E$1:E2), AVERAGE(OFFSET($E$1,SUMPR*ODUCT(MAX(ROW($I$1:I1)*( $I$1:I1<""))),0,ROW()-SUMPRODUCT(MAX(ROW($I$1:I1)**(I$1:I1<""))),1))))

Copy down as desired. This will generate an average everytime N is not
blank, or numbers that have not previously been counted.

--
Best Regards,

Luke M
*Remember to click "yes"ifthis post helped you!*



"Redi" wrote:
Hi all, i need help puting together aformula.
on column "e" i have all numbers
on column "n" i have words but some cells are empty.
myformulawill be located on column "q"
ifcell "n1" is blank then "q1" should be blank, butifcell "n2" is
not blank, then i need the average of "e1" and "e2".
if"n3", "n4", and "n5" are blank, then "q3", "q4" and "q5" are blank,
butif"n6" is not blank, then average "e3:e6"

Let me knowifanyone can come up with something.- Hide quoted text -

- Show quoted text -