Hey I'm trying to find the average of all of the numbers within a column, where the rows represent the day and the column represents the total number of products sold for the day. I want the average number of products sold to continue to factor in each new number added and not account for cells that have either no value, or the word "OUT." Is there a way to do this?
=AVERAGE(A:A)
Average of the whole column assuming your data is in column A and is dynamic as you enter more values. It will not pick up text, just numbers. Change to B:B, C:C, etc as you change your columns.
B.Econ, CFA
Something like:
=AVERAGEIFS(A1:INDEX(A:A,ROW()-1),A1:INDEX(A:A,ROW()-1),"<>",A1:INDEX(A:A,ROW()-1),"<>OUT")
this will average from A1 to row above the one your formula is in...
copied across the columns...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Actually they both worked, I tried the first one before I even posted but I must have made my own calculations wrong when checking against Excel's answer. Thanks for the help.
Yeah, I overcomplicated it.. not sure what I drug I was on... but I also had assumed that your putting the formula in the same column and were trying to average the rows above it, while inserting new rows... that is where the A1:INDEX(A:A,ROW()-1) comes in.
Just wondering, NBVC, your formula works only on MS Excel 2010? Or 2003 - 2010? Cheers.
Only in 2007 and later (AVERAGEIFS is new in those versions)... but your AVERAGE() formula was for sure better to use in this case....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks