Excel help

Clauric

Not sure if this is the right place - please move if it is better elsewhere.

I've got a large excel spreadsheet containing the performance of various golfers over 40+ weeks. I am trying to so some statistical analysis of their performance. The data I have contains a large number of blanks (as naturally, not every player plays, or even makes the cut, every week).

The data is stored in rows, by date (i.e. A2 = name, B2 = score week 1, C2 = score week 3, etc.). What I want to do is for the sheet to take the weekly score, and post it to another cell (e.g. BB2 = C2). However, if the source cell is blank, I want it to post the next non-blank cell (e.g. if C2 has data, BB2 = C2, if C2 is blank, check D2, E2, F2, etc. until non blank cell is found, and post to BB2). I then want it to keep going for each next cell, until all non blank cells in the row have been entered (e.g. if E2 non blank, post to BC2, then next non blank posts to BD2, etc.)

Is there any way to do this with a simple formula, or do a simple VBA macros? The reason i don't want to do this manually, is that there are going to be 100+ columns, and 500+ rows for this sheet, and another 4 sheets of similar size.

Any help is greatly appreciated.

PS. Is there any way to filter horiztonally (i.e. by rows), or is it just vertically (i.e. columns)?

Thoie

Before we disappear down a rabbit hole, why do you want to remove the blanks?

Excel will ignore the blanks in most statistical calculations. Eg if you get the average of 80, blank, 40, it will correctly return as 60 (80+40 / 2), not as 40 (80 + 40 / 3).
The average of 80, 0 , 40 would be 40.



If you still want to delete all blanks, here's what I'd do.

1. Make a copy of the originals (perhaps a new file)
2. Follow the instructions here to select only blanks : http://www.extendoffice.com/documents/excel/1065-excel-select-empty-cells.html
3. Once your blanks are selected, then right click one of the blanks and pick Delete. When asked, say "shift cells left".

That will do it all in one go for you.

I've never found a good way to filter horizontally, but you can copy everything, and paste it as transposed, which switches the rows and columns if that helps. So in your case you'd end up with the player names across the top and the weeks down the left.


For what you're doing it sounds like a pivot table may be the way to go.

Clauric

Thoie,

The rabbit hole is that I'm looking to do both moving average and exponential moving average calcs on each row. Having blanks in the row will change the returned values.

For example (using AVERAGE()):

Blanks75 72 628068 7271 73.572677170747071.5No blanks75726280687271 69.6771.3370.0073.3370.33

The "Blanks" line is wrong, for my purposes, and will return an error if I do an exponential moving average. I can do the manual process, it is just that it is exceptionally time consuming, and I am looking for an automated way of doing it.

Thoie

Clauric wrote: »

Thoie,

The rabbit hole is that I'm looking to do both moving average and exponential moving average calcs on each row. Having blanks in the row will change the returned values.

For example (using AVERAGE()):

Blanks75 72 628068 7271 73.572677170747071.5No blanks75726280687271 69.6771.3370.0073.3370.33

The "Blanks" line is wrong, for my purposes, and will return an error if I do an exponential moving average. I can do the manual process, it is just that it is exceptionally time consuming, and I am looking for an automated way of doing it.

The instructions I gave will remove the blanks for you, but I 100% swear that average() doesn't take blanks into account. However I can see from above that some of your blanks are actually spaces - I'd do a ctrl-h and replace all spaces with "nothing" to make sure they're really blank first.

Clauric

Thoie wrote: »

The instructions I gave will remove the blanks for you, but I 100% swear that average() doesn't take blanks into account. However I can see from above that some of your blanks are actually spaces - I'd do a ctrl-h and replace all spaces with "nothing" to make sure they're really blank first.

My apologies, maybe I explained my problem badly. I need the blanks to be removed, because if I use them, they give the wrong impression with the moving average.
If we take the following:

79 ___ 82 ___ 72 77 81 ___ 71 69 68 67


We get the following 3 day moving average:


___ ___ 80.5 82 77 74.5 76.67 79 76 70 69.33 68

However, this gives the wrong value, as it isn’t the 3 day moving average of the scores given. Using the same numbers above, with the blanks removed, we get:

79 82 72 77 81 71 69 68 67

___ ___ 77.67 77 76.67 76.33 73.67 69.33 68

What I am looking for is an automated way to remove the blanks (“___”), or am I required to do this by hand?

Overheal

Clauric wrote: »

My apologies, maybe I explained my problem badly. I need the blanks to be removed, because if I use them, they give the wrong impression with the moving average.
If we take the following:

79 ___ 82 ___ 72 77 81 ___ 71 69 68 67


We get the following 3 day moving average:


___ ___ 80.5 82 77 74.5 76.67 79 76 70 69.33 68

However, this gives the wrong value, as it isn’t the 3 day moving average of the scores given. Using the same numbers above, with the blanks removed, we get:

79 82 72 77 81 71 69 68 67

___ ___ 77.67 77 76.67 76.33 73.67 69.33 68

What I am looking for is an automated way to remove the blanks (“___”), or am I required to do this by hand?

I'm not by a computer but try formatting the cells as numbers or using data validation to force those cells to only contain numbers

Thoie

Clauric wrote: »

What I am looking for is an automated way to remove the blanks (“___”), or am I required to do this by hand?

I'm getting confused between blanks and spaces, but either way, my original method will do them all quickly. In case you have spaces in there (as well as blank cells), I'm adding a step 1a


1. Make a copy of the originals (perhaps a new file)
1a. Use ctrl-h (find and replace) and put a single space into "Find what" and do nothing in the "Replace with" field, and tell it to replace all. That will ensure that empty cells are properly showing as blanks.
2. Follow the instructions here to select only blanks : http://www.extendoffice.com/document...pty-cells.html
3. Once your blanks are selected, then right click one of the blanks and pick Delete. When asked, say "shift cells left".

That method will remove hundreds of blanks in under 30 seconds. Total number of clicks required from you is <10.

Overheal

Im intrigued by the problem though and I don't mind exercising my Excel brain tissue. OP do you have a sample workbook to take a look at? You can PM it if you'd rather not have the file public. Also, an explanation of the math you're doing would be nice. I haven't the slightest clue what a moving average is.