Conditional if formula.

  • Thread starter Thread starter Redi
  • Start date Start date
R

Redi

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.
 
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))))
 
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?
 
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.
 
Back
Top