Thanks a lot Valko... the shorter the better
Here is another question...
same worksheet...
column "a" all numbers...
column "b" i have words but some cells are empty...
Formula will be in cells under column "c"
if cell "b1" is not blank give "a1" on "c1"
If cell "b2" is blank give blank on "c2"
if cell "b3" is not blank give "a2+a3" on "c3"

This is the same problem as the previous one, the only difference is
that instead of averaging after blank cells, you just add.

Thanks again!

On Jul 8, 1:41*pm, "T. Valko" wrote:
We can trim a few more keystrokes by replacing COUNTIF(N$1:N1,"*") with
COUNTA:

=IF(N2="","",IF(COUNTA(N$1:N1),AVERAGE(INDEX(E:E,L OOKUP(2,1/(N$1:N1<""),RO*W(N:N))+1):E2),AVERAGE(E$1:E2)))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message

...



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 containformulablanks.

--
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 theformulais 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"ifthis 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 -- Hide quoted text -

- Show quoted text -