| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional if formula.
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
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 - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
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? ... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
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 - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional if formula.
Try this:
C1: =IF(B1="","",A1) C2 copied down as needed: =IF(B2="","",SUM(A$1:A2)-SUM(C$1:C1)) -- Biff Microsoft Excel MVP "Redi" wrote in message ... 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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
| Formula, Conditional Formula Needed | Excel Discussion (Misc queries) | |||
| Help With Conditional Formula | Excel Discussion (Misc queries) | |||
| Conditional Formula to indicate Formula in cell | New Users to Excel | |||
| conditional sum formula | Excel Worksheet Functions | |||
Linear Mode
