LibreOffice Calc - average formula with dynamic range, copy/paste to other cells?

PianoPlayer88Key · May 2, 2020

So I didn't want to post here (cause recent experience in another subforum had me not having any helpful responses after a month), but... here goes..

Basically, does anyone know how to put in a formula to calculate an average over a a DYNAMIC / VARIABLE range, and copy/paste that formula so it properly changes the range each place I paste it, based on the data nearby? I'll explain in more detail....

I'm working on a spreadsheet in LibreOffice Calc, where I'm calculating the average price drop of hard drives over a certain length of time, for several different instances.

To start, I put in the prices and capacities of hard drives from several magazine ads I have from around 1990 to 2007. (1991 and 1992 are missing, but the others have approximately one per year, but different times of the year.)

Then, I want to put a formula above each range that's in the upper portion, and below each range in the lower portion, and have it calculate ONLY the contiguous cells (skipping the single blank cell between the formula and the data.)

In the attached screenshot, I highlighted some of the cells where I'd copy / paste my average formula in light blue, outlined the ranges I want to average in medium green, then highlighted the range that my current formula in light red. (I selected the first cell in the formula so the formula would show in the text entry bar.)

As you can probably see, with my current formula, on some of the ranges it will ignore cells that I want to include in the average, and on others it will include cells I do NOT want.

Google search has been NO help. It's only come up maybe with ways to average or sum non-contiguous ranges, or ignore blanks or zeroes, or how to average a dynamic range where the range in the SAME area changes (like someone adds new rows of data, or similar), but not what I'm trying to do.

Also, I want to be able to copy / paste the same formula in the lower portions of the data, and have it average the group of data ABOVE it. (Basically, I would copy/paste the formula ... first it would look down and if "is first cell below this blank and does second cell contain a number" was NOT true, it would go in the reverse direction (up). Then, in the middle cell between the sets of data, I want to have it divide the lower-cell average by the upper-cell average. (I haven't even gotten to the point where I'd also be including the dates in the formulas, and calculating the price drop rate per year or whatever.)

I want to do this for several sheets / ranges. (You can see I have quite a few sheets in the spreadsheet, and there's a good chance I may add even more.)

In the example in the screenshot ... I'm calculating the price drop over approximately a year and 5 months or so. (Some ranges are only 11 months apart, some are almost 2 years apart - that's just the data I had readily available to work with for now. I have more magazines with PC / parts ads from different dates in that range, just don't have scans of them right now, just the paper copies.)

That year and 5 months is based on how long it has been since I bought a 1TB 970 Evo for about $228 or so. I'm wanting to calculate what price I should expect to pay for an SSD now for the same capacity, or how much TB I should expect for the same price, based on price trends. Based on recent trends, though, I'm prepared to be a little disappointed. For example, if you look at the March 2003 vs August 2004 data in the spreadsheet, there was about a 1/3 reduction in cost per GB then. So today based on that, I would expect to pay about $75 for a 1TB 970 Evo now, or $150 for a 2TB or $300 for a 4TB. (Looking now though, the 1TB 970 Evo Plus is $229, and the 2TB is $450, basically NO price drop. )

I also want to do the same calculations for other ranges. On example calculation I already have done (but by typing in the formula MANUALLY for each instance, no automation here ) was ... I bought two 5TB HGST Deskstar NAS HDDs for $99 each about 3 years 5 months ago. Looking at a similar price drop trend from the past magazine ads .... from February 1997 to January 2000, there was an 0.0707x change in price. (In Feb 1997 you might get 1.3 GB for $183, then in Jan 2000 you might get 20.5 GB for $189.) Based on the same trend, I should have been able to get 70 TB of HDD for $100 by now.

Anyway I was going to split my gripe about SSD and HDD prices not falling fast enough in another thread, but looks like it ended up being included with this one. (Mostly because my data for that would be reliant on the data of my as-yet-incomplete spreadsheet.)

Basically I was thinking the last couple days about getting a couple SSDs - a 1TB or 2TB NVMe boot drive for my desktop so I can split my Linux install onto a separate physical SSD, plus maybe a couple cheap 1 or 2TB 2.5" SATA SSDs so I can get rid of some of my smaller HDDs. (Maximum 2TB so I can use them to experiment with booting older OS's that don't support >2 TB, or in systems that don't support larger drives.)

But, I really don't want to pay $60 for 500GB, $120 for 1TB, $300 for 2TB SATA, or $160 for 1TB NVMe. (I also want something with plenty of endurance - preferably at least 1 PB per TB of capacity.) I suppose I could get maybe a 500GB HDD for like $30 for some of the "<2TB" OS stuff I want to try, but a lot of the listings I'm seeing for smaller hard drives are older discontinued drives. )

P.S. ... I decided to go ahead and attach the actual spreadsheet. (I used LibreOffice Calc 5.4.3.2) Also linked below are the shared photo albums (google photos) where I have the scans of the old magazine ads saved, showing the prices then. (They also have some RAM, CPU, motherboard, system, GPU, etc. prices - might be interesting to look at as well.) BTW I use YYYY-MM-DD date notation.

some hdd prices CE 1990-2007, NE 2008-2020.ods ... the reference to NE (Newegg) was because I was also using Internet Archive to look up prices they had for HDDs for several years starting in 2008. I was having issues with it though, so that's quite incomplete.

1990-12-07

1993-03-19

1994-01-07 - also look at the tape backup vs HDD prices in the last ad (S.G. Computers - I added a 2nd copy of the ad where I highlighted those sections, and a couple specific products. The fact that I can't get backup media for the same cheaper cost relative to the cost of HDDs is the primary reason why I do NOT have hardly any backups right now. For the past year or so it's been possible to get an 8TB HDD for the price that a 250MB HDD was then ($215), but I haven't found any 8TB backup media for the $20 that the 250MB tape was then, nor have I found an 8TB backup drive for around $150 like the 250MB tape drive was then.

About 20 or 25 years ago or so, I used to have another box of the same magazines dating back to 1987, but I can't find them now. (I wonder if I got rid of them... I thought I had saved a few articles / ads from them, but I can't find those either.) Also the gap between 1990-12-07 to 1993-03-19 was because I'm missing pretty much all of those as well. I did find one in that range after that, but I haven't added it to the photos, and I have since lost it. I would eventually like to find an automated way to generate digital searchable copies of ALL of them, so I can get rid of the couple boxes of paper-and-ink copies I have that are taking up space I'd rather use for something else. (I may have located the original publisher online (he runs an auto hotkey blog at least recently), but I kinda doubt he'd still have the original data from which he generated the published magazines, or that it could be easily converted into a modern format like PDF.)