|
How to average the last 10 numbers in a row?
How can I write a formula to calculate the average for the last 10
positive/negative numbers in a row eg. B4:BX4 if: 1. Row B4:BX4 has both positive and negative numbers without zeros 2. Row B4:BX4 has both positive and negative numbers and may contain zeros I have tried my best for the last 2 weeks and hope somebody can enlighten me here. Thanks. |
How to average the last 10 numbers in a row?
John,
Array enter (enter using Ctrl-Shift-Enter instead of just Enter) To include 0s but ignore blanks =AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:BX4 ),0),1)))) To ignore zeroes and blanks: =AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:BX4 ),0),1)))) I'm sure there are other ways to do this without INDIRECT but this was easy and it works.... :-) HTH, Bernie MS Excel MVP "John P" wrote in message ... How can I write a formula to calculate the average for the last 10 positive/negative numbers in a row eg. B4:BX4 if: 1. Row B4:BX4 has both positive and negative numbers without zeros 2. Row B4:BX4 has both positive and negative numbers and may contain zeros I have tried my best for the last 2 weeks and hope somebody can enlighten me here. Thanks. |
How to average the last 10 numbers in a row?
Hi,
May be this =AVERAGE(INDEX(A4:BX4,LARGE(COLUMN(A4:BX4)*(A4:BX4 <""),10)):BX4) or this =AVERAGE(INDEX(A4:BX4,LARGE(COLUMN(A4:BX4)*(A4:BX4 <0),10)):BX4) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike Both ara array formula "John P" wrote: How can I write a formula to calculate the average for the last 10 positive/negative numbers in a row eg. B4:BX4 if: 1. Row B4:BX4 has both positive and negative numbers without zeros 2. Row B4:BX4 has both positive and negative numbers and may contain zeros I have tried my best for the last 2 weeks and hope somebody can enlighten me here. Thanks. |
How to average the last 10 numbers in a row?
Just realised how you can do it without cheating by including column A in the
range =AVERAGE(INDEX(B4:BX4,LARGE(COLUMN(B4:BX4)*(B4:BX4 <"")-1,10)):BX4) or =AVERAGE(INDEX(B4:BX4,LARGE(COLUMN(B4:BX4)*(B4:BX4 <0)-1,10)):BX4) Mike "Mike H" wrote: Hi, May be this =AVERAGE(INDEX(A4:BX4,LARGE(COLUMN(A4:BX4)*(A4:BX4 <""),10)):BX4) or this =AVERAGE(INDEX(A4:BX4,LARGE(COLUMN(A4:BX4)*(A4:BX4 <0),10)):BX4) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike Both ara array formula "John P" wrote: How can I write a formula to calculate the average for the last 10 positive/negative numbers in a row eg. B4:BX4 if: 1. Row B4:BX4 has both positive and negative numbers without zeros 2. Row B4:BX4 has both positive and negative numbers and may contain zeros I have tried my best for the last 2 weeks and hope somebody can enlighten me here. Thanks. |
How to average the last 10 numbers in a row?
Thanks Bernie. Sorry I didn't make myself clear. What I meant was how to
calculate the average for the last 10 positive numbers or last 10 negative numbers in a row that has both positive and negative numbers in case 1 without zero and case 2 with zeros. Your formula gives the average for the last 10 numbers. "Bernie Deitrick" wrote: John, Array enter (enter using Ctrl-Shift-Enter instead of just Enter) To include 0s but ignore blanks =AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:BX4 ),0),1)))) To ignore zeroes and blanks: =AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:BX4 ),0),1)))) I'm sure there are other ways to do this without INDIRECT but this was easy and it works.... :-) HTH, Bernie MS Excel MVP "John P" wrote in message ... How can I write a formula to calculate the average for the last 10 positive/negative numbers in a row eg. B4:BX4 if: 1. Row B4:BX4 has both positive and negative numbers without zeros 2. Row B4:BX4 has both positive and negative numbers and may contain zeros I have tried my best for the last 2 weeks and hope somebody can enlighten me here. Thanks. |
How to average the last 10 numbers in a row?
John,
Last ten positive numbers, without zeroes: =AVERAGE(IF(B4:BX40, IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX40,COLUMN(B4:X4) ,0),10), B4:BX4))) Last ten numbers greater than or equal to zero: =AVERAGE(IF(B4:BX4=0, IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4=0,COLUMN(B4:X4 ),0),10), B4:BX4))) Last ten negative numbers, without zeroes: =AVERAGE(IF(B4:BX4<0, IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4<0,COLUMN(B4:X4) ,0),10), B4:BX4))) Last ten negative greater than or equal to zero: =AVERAGE(IF(B4:BX4<=0, IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4<=0,COLUMN(B4:X4 ),0),10), B4:BX4))) Again, array entered using Ctrl-Shift-Enter. HTH, Bernie MS Excel MVP "John P" wrote in message ... Thanks Bernie. Sorry I didn't make myself clear. What I meant was how to calculate the average for the last 10 positive numbers or last 10 negative numbers in a row that has both positive and negative numbers in case 1 without zero and case 2 with zeros. Your formula gives the average for the last 10 numbers. "Bernie Deitrick" wrote: John, Array enter (enter using Ctrl-Shift-Enter instead of just Enter) To include 0s but ignore blanks =AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:BX4 ),0),1)))) To ignore zeroes and blanks: =AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:BX4 ),0),1)))) I'm sure there are other ways to do this without INDIRECT but this was easy and it works.... :-) HTH, Bernie MS Excel MVP "John P" wrote in message ... How can I write a formula to calculate the average for the last 10 positive/negative numbers in a row eg. B4:BX4 if: 1. Row B4:BX4 has both positive and negative numbers without zeros 2. Row B4:BX4 has both positive and negative numbers and may contain zeros I have tried my best for the last 2 weeks and hope somebody can enlighten me here. Thanks. |
How to average the last 10 numbers in a row?
Thank you for your solution. It takes me some time to understand the logic
but at least your formulas are the familiar type found in Excel textbooks. Another kind brother gave me a correct formula but he used a type of nested IFs which I have not seen before and struggled to understand: IF(IF(.....),IF(...))...) with the N function. Thank you. "Bernie Deitrick" wrote: John, Last ten positive numbers, without zeroes: =AVERAGE(IF(B4:BX40, IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX40,COLUMN(B4:X4) ,0),10), B4:BX4))) Last ten numbers greater than or equal to zero: =AVERAGE(IF(B4:BX4=0, IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4=0,COLUMN(B4:X4 ),0),10), B4:BX4))) Last ten negative numbers, without zeroes: =AVERAGE(IF(B4:BX4<0, IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4<0,COLUMN(B4:X4) ,0),10), B4:BX4))) Last ten negative greater than or equal to zero: =AVERAGE(IF(B4:BX4<=0, IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4<=0,COLUMN(B4:X4 ),0),10), B4:BX4))) Again, array entered using Ctrl-Shift-Enter. HTH, Bernie MS Excel MVP "John P" wrote in message ... Thanks Bernie. Sorry I didn't make myself clear. What I meant was how to calculate the average for the last 10 positive numbers or last 10 negative numbers in a row that has both positive and negative numbers in case 1 without zero and case 2 with zeros. Your formula gives the average for the last 10 numbers. "Bernie Deitrick" wrote: John, Array enter (enter using Ctrl-Shift-Enter instead of just Enter) To include 0s but ignore blanks =AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:BX4 ),0),1)))) To ignore zeroes and blanks: =AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:BX4 ),0),1)))) I'm sure there are other ways to do this without INDIRECT but this was easy and it works.... :-) HTH, Bernie MS Excel MVP "John P" wrote in message ... How can I write a formula to calculate the average for the last 10 positive/negative numbers in a row eg. B4:BX4 if: 1. Row B4:BX4 has both positive and negative numbers without zeros 2. Row B4:BX4 has both positive and negative numbers and may contain zeros I have tried my best for the last 2 weeks and hope somebody can enlighten me here. Thanks. |
How to average the last 10 numbers in a row?
Thanks Mike for your help. Appreciate it.
"Mike H" wrote: Just realised how you can do it without cheating by including column A in the range =AVERAGE(INDEX(B4:BX4,LARGE(COLUMN(B4:BX4)*(B4:BX4 <"")-1,10)):BX4) or =AVERAGE(INDEX(B4:BX4,LARGE(COLUMN(B4:BX4)*(B4:BX4 <0)-1,10)):BX4) Mike "Mike H" wrote: Hi, May be this =AVERAGE(INDEX(A4:BX4,LARGE(COLUMN(A4:BX4)*(A4:BX4 <""),10)):BX4) or this =AVERAGE(INDEX(A4:BX4,LARGE(COLUMN(A4:BX4)*(A4:BX4 <0),10)):BX4) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike Both ara array formula "John P" wrote: How can I write a formula to calculate the average for the last 10 positive/negative numbers in a row eg. B4:BX4 if: 1. Row B4:BX4 has both positive and negative numbers without zeros 2. Row B4:BX4 has both positive and negative numbers and may contain zeros I have tried my best for the last 2 weeks and hope somebody can enlighten me here. Thanks. |
| All times are GMT +1. The time now is 05:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com