Hi all, i need help puting together a formula.
on column "e" i have all numbers
on column "n" i have words but some cells are empty.
my formula will be located on column "q"
if cell "n1" is blank then "q1" should be blank, but if cell "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,
but if "n6" is not blank, then average "e3:e6"

Let me know if anyone can come up with something.

In Q1:
=IF(N1="","",E1)
In Q2:
=IF(N2="","",IF(COUNT($I$1:I1)=0,AVERAGE($E$1:E2), AVERAGE(OFFSET($E$1,SUMPRODUCT(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" if this post helped you!*


"Redi" wrote:

Hi all, i need help puting together a formula.
on column "e" i have all numbers
on column "n" i have words but some cells are empty.
my formula will be located on column "q"
if cell "n1" is blank then "q1" should be blank, but if cell "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,
but if "n6" is not blank, then average "e3:e6"

Let me know if anyone can come up with something.

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 -

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 -

Thanks a lot for your help.
Sorry but i am new at this, how do i click "yes"? to say that your
post has helped me?

On Jul 8, 12:19*pm, Luke M wrote:
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 -

You couldn't click "yes", unless you were using the Microsoft web interface
to the newsgroup, and in general that interface is not recommended.

Those who say things like "*Remember to click "yes"ifthis post helped you!*"
have forgotten, or don't care, that other people use more conventional
methods of accessing a newsgroup, and that such requests are liable to cause
confusion.
--
David Biddulph

"Redi" wrote in message
...
Thanks a lot for your help.
Sorry but i am new at this, how do i click "yes"? to say that your
post has helped me?
...

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 -

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<""),ROW(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 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 -