|
Average, IF
I'm trying to write a formula to return the AVERAGE by calendar quarter, IF
the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
Hi,
I'm sorry but i dont think i understand your question properly. If you want to average all values greater than 0, use the following array formula (Ctrl+Shift+Enter) =average(if((range0),range)) Please pardon me if my interpretation of your question is incorrect. You may mail me if you need any further clarifications. Regards, Ashish Mathur India "JMS" wrote: I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
Just use
=AVERAGE(IF(A1:A3<"",A1:A3) which is an array formula, so commit with Ctrl-Shift-Enter. If the cells will contain 0, then use =AVERAGE(IF(A1:A3<0,A1:A3) instead -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter values based on a number of response each month. Some months may have activity and have a number, other may have no activity and have 0 - ex: Jan A1 10 Feb A2 20 Mar A3 0 Apr A4 10 May A5 20 Jun A6 30 etc.... Your suggestion returned the average for the given quarter even if the third month had not been filled in yet. I want to wait to perform the average until either a whole number or a 0 are entered. Thanks for your help with this. "Bob Phillips" wrote: Just use =AVERAGE(IF(A1:A3<"",A1:A3) which is an array formula, so commit with Ctrl-Shift-Enter. If the cells will contain 0, then use =AVERAGE(IF(A1:A3<0,A1:A3) instead -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
|
Average, IF
Thanks Ashish, would you see my reply to Bob's suggestion and see if this
helps to clarify the question? Thanks. "Ashish Mathur" wrote: Hi, I'm sorry but i dont think i understand your question properly. If you want to average all values greater than 0, use the following array formula (Ctrl+Shift+Enter) =average(if((range0),range)) Please pardon me if my interpretation of your question is incorrect. You may mail me if you need any further clarifications. Regards, Ashish Mathur India "JMS" wrote: I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
I anticipated that by offering the alternative of
=AVERAGE(IF(A1:A3<0,A1:A3) -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... Thanks Bob, but that didn't quite get it. What I have is a situation where I have each month of the year & enter values based on a number of response each month. Some months may have activity and have a number, other may have no activity and have 0 - ex: Jan A1 10 Feb A2 20 Mar A3 0 Apr A4 10 May A5 20 Jun A6 30 etc.... Your suggestion returned the average for the given quarter even if the third month had not been filled in yet. I want to wait to perform the average until either a whole number or a 0 are entered. Thanks for your help with this. "Bob Phillips" wrote: Just use =AVERAGE(IF(A1:A3<"",A1:A3) which is an array formula, so commit with Ctrl-Shift-Enter. If the cells will contain 0, then use =AVERAGE(IF(A1:A3<0,A1:A3) instead -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
Hi!
I want to wait to perform the average until either a whole number or a 0 are entered. =IF(COUNT(A1:A3)<3,"",AVERAGE(A1:A3)) Biff "JMS" wrote in message ... Thanks Bob, but that didn't quite get it. What I have is a situation where I have each month of the year & enter values based on a number of response each month. Some months may have activity and have a number, other may have no activity and have 0 - ex: Jan A1 10 Feb A2 20 Mar A3 0 Apr A4 10 May A5 20 Jun A6 30 etc.... Your suggestion returned the average for the given quarter even if the third month had not been filled in yet. I want to wait to perform the average until either a whole number or a 0 are entered. Thanks for your help with this. "Bob Phillips" wrote: Just use =AVERAGE(IF(A1:A3<"",A1:A3) which is an array formula, so commit with Ctrl-Shift-Enter. If the cells will contain 0, then use =AVERAGE(IF(A1:A3<0,A1:A3) instead -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
Does this work for you:
=IF(COUNT(A1:A3)=3,AVERAGE(A1:A3),"Missing Info") ? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMS" wrote in message ... Thanks Bob, but that didn't quite get it. What I have is a situation where I have each month of the year & enter values based on a number of response each month. Some months may have activity and have a number, other may have no activity and have 0 - ex: Jan A1 10 Feb A2 20 Mar A3 0 Apr A4 10 May A5 20 Jun A6 30 etc.... Your suggestion returned the average for the given quarter even if the third month had not been filled in yet. I want to wait to perform the average until either a whole number or a 0 are entered. Thanks for your help with this. "Bob Phillips" wrote: Just use =AVERAGE(IF(A1:A3<"",A1:A3) which is an array formula, so commit with Ctrl-Shift-Enter. If the cells will contain 0, then use =AVERAGE(IF(A1:A3<0,A1:A3) instead -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
Sorry Bob, but they both seemed to work the same.
If A3 (March) is blank (nothing entered in the cell) I want NO average returned. If A3 March has any number, including 0 I want the average for Jan, Feb, March returned. Thanks for your patience! "Bob Phillips" wrote: I anticipated that by offering the alternative of =AVERAGE(IF(A1:A3<0,A1:A3) -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... Thanks Bob, but that didn't quite get it. What I have is a situation where I have each month of the year & enter values based on a number of response each month. Some months may have activity and have a number, other may have no activity and have 0 - ex: Jan A1 10 Feb A2 20 Mar A3 0 Apr A4 10 May A5 20 Jun A6 30 etc.... Your suggestion returned the average for the given quarter even if the third month had not been filled in yet. I want to wait to perform the average until either a whole number or a 0 are entered. Thanks for your help with this. "Bob Phillips" wrote: Just use =AVERAGE(IF(A1:A3<"",A1:A3) which is an array formula, so commit with Ctrl-Shift-Enter. If the cells will contain 0, then use =AVERAGE(IF(A1:A3<0,A1:A3) instead -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
Average, IF
BINGO!
That got it. Thanks everyone for your help. Now, any suggestions for easily updating this formula into my worksheet in 60+ different areas? Thanks again! "Biff" wrote: Hi! I want to wait to perform the average until either a whole number or a 0 are entered. =IF(COUNT(A1:A3)<3,"",AVERAGE(A1:A3)) Biff "JMS" wrote in message ... Thanks Bob, but that didn't quite get it. What I have is a situation where I have each month of the year & enter values based on a number of response each month. Some months may have activity and have a number, other may have no activity and have 0 - ex: Jan A1 10 Feb A2 20 Mar A3 0 Apr A4 10 May A5 20 Jun A6 30 etc.... Your suggestion returned the average for the given quarter even if the third month had not been filled in yet. I want to wait to perform the average until either a whole number or a 0 are entered. Thanks for your help with this. "Bob Phillips" wrote: Just use =AVERAGE(IF(A1:A3<"",A1:A3) which is an array formula, so commit with Ctrl-Shift-Enter. If the cells will contain 0, then use =AVERAGE(IF(A1:A3<0,A1:A3) instead -- HTH RP (remove nothere from the email address if mailing direct) "JMS" wrote in message ... I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE? Thanks! |
| All times are GMT +1. The time now is 04:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com