How to average both positive and negative numbers
Requirement:
On the user's data sheet from Col D through Col Z is where the data is kept. The numbers in in Col D through Col Z, can be both negative and positive numbers, so the user wants to average, the last three numbers in the range for a three day average, and the last 5 days for a average, and the last ten. There will be zeros when no information is required. Also not all people will have a 10,5,3 day information for a average number.
Example:
Col D row 3: 65.02
Col E row 3: -2.03
Col F row 3: .00
Col H row 3: 15.00
Col I row 3: .00
Col J row 3: -.23
Solution:
=AVERAGEIF(INDIRECT(ADDRESS(3,SUMPRODUCT(LARGE((COLUMN(A3:AZ3))*(A3:AZ3<>"")*(A3:AZ3<>0),$C$6)),4)&":"&ADDRESS(3,SUMPRODUCT(MAX((COLUMN(A3:AZ3))*(A3:AZ3<>"")*(A3:AZ3<>0))),4)),"<>0",INDIRECT(ADDRESS(3,SUMPRODUCT(LARGE((COLUMN(A3:AZ3))*(A3:AZ3<>"")*(A3:AZ3<>0),$C$6)),4)&":"&ADDRESS(3,SUMPRODUCT(MAX((COLUMN(A3:AZ3))*(A3:AZ3<>"")*(A3:AZ3<>0))),4)))
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to average time between dates |
| How to Average every certain # of columns |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
