Anyone want to help me with a moving average that ignores blank cells?

Its golf related and the players are listed vertically in column A.

Next to them is their average and std dev in their own columns.

Starting in column F i have the most recent round on the pga tour and the players score. If they didnt play the event the cell is left blank. I insert every new round right at column F.

Im trying to calculate the average of the players 12 latest rounds. Problem is some players in the list have 12 consecutive columns of scores and other have 4 then 4 empty cells then 8 scores. Ive scoured the internet for help amd cant figure it out for the life of me.

I thought AVERAGE function ignored blanks.
Edit: oh I see what you mean. Hmm

Think i got it but i have to reverse my data. Still interested in peoples thoughts on best way to do it tho.

this seems like a horrific way, but for each player maybe some running count underneath of populated cells running left to right, and then summing the scores to the '12' column and dividing by 12

You can do it using an array function. This formula is in Cell A1 on your workbook, assuming that your data starts from Cell B1 and ends in Cell GZ1. When entering or changing the formula you have to hit CTRL-SHIFT-ENTER

=AVERAGE(OFFSET(A1,0,1,1,SMALL(IF(B1:GZ1<>"",COLUM N(B1:GZ1),""),12)-1))

You may have to tweak it to get it to keep working when you add new rows at the beginning. It should be fine if you use column B as blank and add new rows starting from row C.

Ive ended up going with this.

Array formula

=AVERAGE(INDEX(Table4[@],LARGE((COLUMN(Table4[@])-3)*(Table4[@]<>""),12)):Table4[@Round47])

And that gives me the average of the players last 12 rounds

I put all the scores into a table, and the table runs from Column E to Column AX

very nice

only took like 4 hours....

Along the lines of the above (although, I assume, much simpler).

What would be the best formula for calculating the averages of a range of cells, excluding blank cells and cells containing 0?

For example, I'd like to get the average from the totals listed from T5 to T66. Some cells are blank and some contain a 0 (until entries are added later). I need to exclude both the blank cells and the ones currently with a 0 in the average I need to calculate in the cell T67.

What would be the best way to do this?

Thanks!

AVERAGEIFS

I'll give this a shot, but my stats classes were a long time ago and I slept through most of them...

Home elo after game (column Z) is Ranew from the first link, Home elo before game (column U) is Raold. You first need to turn column Z into a formula based on the equations in the link. I tried doing it, but I didn't end up with the same values for column Z so I'm not sure what I did wrong. I guessed at some of the inputs since not all the columns are labeled. I assumed:

K = column Q
Sa = column S
Ra = column U
Rb = column V

If that's right, then the formula to calc Ea would be
=1/(1+POWER(10,(V2-U2)/400))
Entered in cell AB2. The formula for RaNew entered in cell AC2 would be:
=U2+(Q2*(S2-AB2))

In theory AC2 would equal Z2, but it doesn't. So either I've made a mistake creating the formulas or I'm not using the correct columns as inputs.

Lets say for the time being that those formulas were correct. Now comes a tricky bit. You'll notice that the elo before game is equal to the elo after game from that team's prior game. For example, DC United's elo before game in cell U2 is equal to DC's elo after game in cell Z3. You'd need to set up formulas for the 'before game' numbers so that they are pulled from the 'prior game' numbers. Then, since every game's prediction is a function (in part) of the prior game, when you change the K value EVERY game's elo will update. It's going to be cumbersome to set this up since you have to look for the team's prior game, yet the team could be listed in either the away column or home column. Off the top of my head I'd start by creating a key column which is a concatenation of date and team. If I spend the time to figure this part out I'll let you know.

Assuming you got this far, the next step is to have all the cells in column Q be references to a single other cell, so that you can change that one cell and have all the cells in Q update to match.

Then you add in formulas for least squares.
"Least squares means that the overall solution minimizes the sum of the squares of the errors made in the results of every single equation."
In our case, the 'error' is predicted outcome (Ea) versus actual outcome (column S; either 0, 0.5, or 1). This part I'm not sure of: how exactly is the error measured? Let's say it's just the difference between the two. Add a column with a formula to take the square of the difference. Now somewhere else on the sheet add a formula to SUM this new column of squares. This is our error aka sum of squares (I think), and we're trying to minimize it by changing the value of K. Now we can use Excel Solver.

So we still have some uncertainty in the equations and the correct definition of 'error', and some work to do with setting up all the formulas, but I think that's the gist of it. Sounds fun!

This is bananas but:

https://dl.dropboxusercontent.com/u/...elosample.xlsx

Assuming all those formulas are good (big assumption) then you open the Excel Solver and

Set Objective: $AC$2
To: Min
By Changing Variable Cells: $AC$1

=IF(MATCH(A3,B3,0),"YES","NO")

I can't remember how to return the "NO" when the function is false. Instead, it's giving me #N/A. It's something with ISNUMBER?

=IF(IFERROR(MATCH(A3,B3,0),FALSE),"YES","NO")

Thanks Spurious!

I assume, it's a simple formula, but not sure what to use.

What formula would I use to get the average from the range T5:T66 with the criteria that the cells used for the average contain a value larger than 0? This way the formula would skip all cells that are either blank or contain a 0.

Thanks in advance for the help!

=AVERAGEIFS(T5:T66,T5:T66,">"&0) (the &0 is optional I think)

That gave me a false for everything?

=if(isnumber(match(A3,B3,0)),"YES","NO")

B3 typically a range, not a single cell

The ISNUMERIC is better, but I dont fully understand what your MATCH formula wants to do.

You need to select a range of cells not just a single cell to do the match formula otherwise you could just use "=".

I pasted that in, but it didn't work. It just entered the actual text into the cell and didn't actually calculate anything.

Change cell format to "General" and recalculate the sheet

Edit: Okay, thanks. I just did =IF(A2=B@,"YES","NO") and that worked fine. Thanks!

A coworker that knows I'm an excel nerd asked me for help. When he told me his problem I immediately responded "oh that's super easy". I think I've done it in the past, but for some reason I just wasted an hour trying to figure it out and couldn't. Conditional formatting is the topic:

I want to compare 2 columns to each other, where the formatting turns each value of column A a color based on it being greater or less than the corresponding row in column B.

This is obviously very easy to do with an individual format, but it's for a very large column of numbers that are generated by their own function. When I do it for row 1 of column A and then format-paint or corner-drag to the rest of the field, it stays referrenced to row 1 of column B.

Make sense? Any thoughts?

Do you have any dollar signs in the formula?

Yes.