UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Skip Every X Cells Formula, Office 2010    
 
   
doctor9
post Apr 21 2017, 12:39 PM
Post#1


UtterAccess Editor
Posts: 17,210
Joined: 29-March 05
From: Wisconsin


So, I have a user who has like 4,000 rows of raw data, which he wants to plot on a chart. But he wants to skip every 4 rows, to thin out the values that are being charted. But he also wants to be able to change it to skipping every 5 rows or 3 rows, so he wants something dynamic and fairly simple. Here's what I came up with, but I'd like to know if someone out there has something even simpler.

1. Name the first raw value cell as "FirstValue".
2. Name a cell somewhere off to the side of the raw data as "SkipBy". Format the cell so it's easy to find, so make it yellow or give it a border. This is where you dynamically set how many cells to skip.
3. Create a "helper column" starting in the same row as the raw data for each column of raw data you want to replicate/thin out.
4. In the first helper column cell next to FirstValue, use this formula: =FirstValue
5. In the first helper column's 2nd row of data, use this formula: =OFFSET(FirstValue,(ROW(B4)-ROW(FirstValue))*SkipBy,0) - where "B4" is the cell you're writing this formula to.
6. Copy the formula from the 2nd data row of the first helper column down to fill in the rest of the helper column.

That's it. No matter which row the raw data starts on, it should work. If you type a 2 in the SkipBy cell, the helper column displays every other value from the raw data column. If you type a 3 in SkipBy, the helper column displays every third value from the raw data column. If you have 2 or more columns of raw data, the 2nd helper column would use =OFFSET(FirstValue,(ROW(B4)-ROW(FirstValue))*SkipBy,1) where the 1 refers to how many columns to the right of FirstValue that you want to grab data from.

The only real drawback is that the formula starts returning zeroes when it starts referring to the blank cells below the raw data. I could insert an IF test to prevent that, but I wanted to keep this as simple as possible.

Any suggestions for improving this?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Daniel_Stokley
post Apr 21 2017, 01:35 PM
Post#2



Posts: 222
Joined: 22-December 14
From: Grand Junction, CO, USA


Hello,

How about this: Use two "helper" columns and a cell named "SkipBy". In the first helper the formula is = Row(yy) where yy is a cell in the first data row. The formula in the second "helper" column is =MOD(xx,SkipBy) where xx is the cell in the first "helper" column.
The results look like this:
Attached File  SkipBy.gif ( 8.12K )Number of downloads: 1

The cell at the upper right is "SkipBy"
The first "helper" column title is "Counter". The second "helper" is "In Chart". So, you would only include those rows where the MOD result is zero.
This post has been edited by Daniel_Stokley: Apr 21 2017, 01:39 PM
Go to the top of the page
 
Daniel_Stokley
post Apr 21 2017, 01:42 PM
Post#3



Posts: 222
Joined: 22-December 14
From: Grand Junction, CO, USA


Strike that. You only need one helper column, the one called "In Chart". The formula would be =MOD(ROW(xx),_SkipBy) where xx is a cell in the data row.
Go to the top of the page
 
dflak
post Apr 21 2017, 04:31 PM
Post#4


Utter Access VIP
Posts: 5,977
Joined: 22-June 04
From: North Carolina


My thoughts are similar to Daniel's. But it depends on what you mean by skip by. If you want to put "holes" in the chart this will work. If you want to "compress" the chart then take one more step and add a pivot table.
Attached File(s)
Attached File  Skip_By.zip ( 14.55K )Number of downloads: 2
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
doctor9
post Apr 21 2017, 05:18 PM
Post#5


UtterAccess Editor
Posts: 17,210
Joined: 29-March 05
From: Wisconsin


dflak,

No, I want to chart every Nth value from the raw data, but without any gaps, hence the helper column idea. Just trying to "thin out" the data on the chart.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th June 2017 - 10:37 PM