Another Problem 3

[dik]

Can someone show me how to 'skip' cells to provide data every 'n'th year, in the sheet attached, I've used 3 years as 'n', but, it could be 5 or 10 or whatever. incrementing each year from a fixed amount to an uniform increase.

Dik

 

[dik]

I think I have a solution... Is there another? or an easier one?

Dik

 

[Hoaokapohaku]

The attached will work, unless you wanted additional rows for each set of variables. Can easily adapt if that is what you want.

 

[GregLocock]

That's pretty much the way to do it. You might get fancy with using mod() but it doesn't really matter.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[dik]

Thanks Hoaok... I'll have to sit down and sort it out... The increment is annual, not occurrance... good place to start, and I use variables all the time... it's a more correct approach...

Thanks, Dik

 

[BigInch]

Those solutions are a WHOLE lot more complex than necessary and don't give contiguous output.
Better is to learn what the OFFSET function can do for you.

"People will work for you with blood and sweat and tears if they work for what they believe in......" - Simon Sinek

 

[yakpol]

Just another way, modify formula in cell C9 of original spreadsheet:

=IF(MOD(C7-$B$7,$C$4),"",$C$3+(C6-1)*$C$2)

and copy it along row 9.

 

[electricpete]

Lots of good solutions. All work.
Fwiw, I like the elegance of BigInch's solution.
Easier to build, understand, maintain, and gather the output (for example lookup)

=====================================
(2B)+(2B)' ?

 

[BigInch]

I get thousands of milepost and elevation data points along pipeline routes. Most are totally unnecessary for any purpose, so I have to immediately eleminate 99 out of every 100 or even thousands at a time, or I have to grab groups of points within intervals to scan for high and low points within the range. I came up with using the offset method as an easy way to extract only a setof the points within any interval that looks useful at the time, or even extract random points within set intervals. The offset function is quite versitile at those tasks. The contiguous output is useful for easily plotting elevation profiles too.

"People will work for you with blood and sweat and tears if they work for what they believe in......" - Simon Sinek

 

[dik]

Thanks, Yak... that does it about as slick as anything... See the revised file...

Dik

 

[BigInch]

Looks like that's just more proof that you can give water, but can't make them drink.

"People will work for you with blood and sweat and tears if they work for what they believe in......" - Simon Sinek

 

[dik]

BigInch... your solution method is good, and I will use it, but not for this project. I like your OFFSET function approach and will use it. The one presented by Yakpol is similar to mine, except he, more clearly, uses the MOD function and not the INT function that I used. The final method I posted is modified from his. It can be used as the framework for a Condominium Costing program where the sums along the bottom are the expenditures for the year and the horizontal sum, modified for present or future, value can be determined. If you use an interval of 4 years in your example, the values don't change... whereas the completed one automatically adjusts the data to show each of four years...

If you needed a professional nutcracker that cost 1000 today, but the cost increased by 10 each year, but you only needed the services each three or four years... the final example accommodates that... and I can copy the lines easily... You can easily change the info in case the nutcracker was 1500 today and increased 20 each year but you only needed the services every six years.

Your OFFSET function approach to data is an excellent one, and I will use it... thanks...

Dik

 

[BigInch]

If by "interval" you mean "skip year" = 4, my spreadsheet certainly changes when equal to 4 and seems to get the correct answers.
In fact I believe mine works for any "skip year" value >= 0

Are you using Excel?


"People will work for you with blood and sweat and tears if they work for what they believe in......" - Simon Sinek

 

[dik]

Using an early Excel and the light green table changes with yours. It's the long horizontal one that I want to be able to have different intervals to sum that doesn't. Sorry, I wasn't clear. You can see from the last posting that I can sum/modify any and all of the years in question. As I noted, I liked your solution, and will use the method for other work. Sorry, I didn't mean to criticise your effort; it's a good solution for a different type of problem.

I posted the final method for others to gain from this brief 'learning excursion'...

Dik

 

[BigInch]

I guess I still don't get what you want to do, but no matter. If you got what you need now, we're cool.
No offense taken at all. Totally cool.

"People will work for you with blood and sweat and tears if they work for what they believe in......" - Simon Sinek