Capture values over 0 and populate results in another column

**rookie3333** · 01-05-2011, 07:40 PM

Hello. This is my first post and I have been racking my brain for several days trying to figure out a way to capture values greater than zero in a column and then populate an adjacent column with the results without leaving any blank cells. I would also like the formula to be dynamic such that it automatically updates with new data that is entered into my original column. Here is a basic example of what I am looking to accomplish.

Column G

0
0
99
0
94
0
114
0
91
86
111

Column K

99
94
114
91
86
111

I have attached a sample spreadsheet for your review. Any assistance would be greatly appreciated.

**MarvinP** · 01-05-2011, 08:02 PM

Hi rookie3333 and welcome to the forum,

There is a feature in Excel called Advanced Filters. I've used it 4 times to do your problem. See the attached. I'm sure some of the smart forurm guru's will come up with a better solution. There are most likely many ways to do this problem.

**martindwilson** · 01-05-2011, 08:13 PM

with functions helpers on sheet 2

**rookie3333** · 01-06-2011, 01:16 AM

Fantastic!!! That was exactly what I needed. I would have never figured that out in a million years.

I do have one more question that I would like to ask now that I have my data formatted exactly the way I wanted. With the newly populated data in column K, I have computed a simple 5 day moving average in column L. I would like to have a specific cell (let's say cell P25) that displays the current 5 day moving average figure from my chart. The current 5 day moving average figure that I would always want displayed in cell P25 would be the figure in column L that aligns with the last data generated from column K. For example, in the chart below, I would like the cell P25 to display the amount 94.4 despite other data following after thsi figure. I only want the current 5 day moving average. However, with every new data added to column K, I would like this cell to automatically populate with the new data. So, if I entered two more data fields in column K, I would now like the cell P25 to automatically display the new 5 day moving average calculated in cell L14. I hope my explanation makes sense. Again, I am sure this will be something simple but I cannot figure it out. Please let me know if you can assist.

I have included the revised workbook below with the current data. Thank you!