[Solved] Last 90 days of data

[revronfrancey]

this is my first post as a newbie. your suggestions on how to properly proceed will be a help.

here's what i'm trying to do.

i'm trying to create a rotating 90 days of recording my a1c blood levels, then as i add past the 90th day, i wish to remove the line for day one so that my calculations stay at a 90 day limit. my columns have iteration number, day number, month name, a1c, sum of a1cs, avg a1c. once i've done 90 days, i have the a1c over a 90 day period, the number should agree with the results of my lab work and can be compared to my dr's results.

i'm having trouble doing the rotation. i've tried copy/pasting from days 2 to to 91 over days 1 to 90, thus scrolling in place, but my formulas get destroyed.

i've also thought that creating a macro to do repetitive operations might work and i've read thru the calc manual but am not understanding enough to get it done.

btw, i used to be a s/w engineer but that was many moons ago. fortran, basic, assembler languages

what would be the right formula to do the following: if (month name is blank, day number is day number +1; else day number = 1)
and how can i set up a macro to cut/paste day 2 thru 91 over day 1 to 90?

all this stuff is most likely pretty simple, i'm just even more simple.

tyia!

[Villeroy]

You can not set up any macro at all. Macros are the opposite of "simple", obsolete and counterproductive in most cases where beginners ask for them.
Please upload a small example of your data.
[Forum] How to attach a document here

[revronfrancey]

Sample Size Month Day Daily A1C Sum of A1Cs 90 Day A1C
October 0
1 1 5.3 5.3 5.30
2 2 5.8 11.1 5.55
3 3 5.9 17 5.67





91 30 5.4 536.7 5.90
92 31 5.3 542 5.89
93 January 1 5.1 547.1 5.88

sample size is iteration.
month is listed only at start of each month.
day is number of day in month.
a1c is each days a1c
sum of a1c is total of a1cs.
next is rolling a1c taking total a1c and dividing by sample size.

i've attempted to gather the first 3 months and a day, then to delete day 1's row thus rolling up 3 months data.
this does not work; i get value errors errors.

#VALUE! #VALUE! 5.8 #VALUE! #VALUE!
#VALUE! #VALUE! 5.9 #VALUE! #VALUE!
#VALUE! #VALUE! 5.9 #VALUE! #VALUE!


i hope this is understandable.

ron

.

[robleyd]

Rather useless as it gives no idea of what is actually in your spreadsheet. As Villeroy suggested, upload an actual spreadsheet with sample data and the formulae you are using.

[FJCC]

I suggest you do not delete data but use formulas that do calculations for the last 90 days or to the beginning of the data, whichever is closest. The attached file shows one way to do this. The formulas in columns E and F change at row 90 where the 90 day threshold is reached. I also put at the top of columns H and I that display the most recent sum and average values so you do not have to scroll down to see the.

[Villeroy]

Simple data collections are a whole lot easier to maintain in a simple database.
Open the attached document.
Open the input form therein.
Enter a value into the focussed white box. It takes whole numbers between 0 and 20.
By default every entered value is saved with today's date and the current time. Enter date and time only if they are different from now.
Some simple statistics for the past 90 days appear below the input area.
Data of the past 90 days appear in the grid after hitting the update button. There you can correct any mistakes.
There is a database report to print out the last 90 days of data.
-------------
If you want to import existing spreadsheet data:
Copy 2 columns with dates and values.
Open the database, click on [Tables], select the table icon "TBL" and paste.
Choose action "Append Data", make sure that the table name "TBL" is correct, check the colun headers option if your copied data include one header row with column labels.
Click [Next] and map the 2 column sets to each other, so the copied date column and the copied value column appears side by side with the corresponding D and V columns.

[John_Ha]

See attached spreadsheet for a rolling average of the last ten numbers.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[revronfrancey]

See attached spreadsheet for a rolling average of the last ten numbers.

john,

the problem is not the math to do the computation; it is that i don't want the length oh the spreadsheet to increase line by line as the next days get added. instead, i want the oldest line of data to be deleted and replaced by the 2nd oldest line of data thus keeping the length of the spreadsheet more manageable. i'm doing this because when one goes to the doctor quarterly for blood work, the a1c one gets is only for the past 90 days.

Clipboard02.png

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[Villeroy]

There is no "mechanism" which is able to delete any data automatically. Just keep all data and let the software do what you need.

[RoryOF]

Using a database and a report allows only the last 90 days to be shown.

[John_Ha]

Highlight the row labels of the rows you want to hide > r-click > Hide. They are hidden.

I am wondering if you could put the data elsewhere on the sheet and copy the 90 days data you want based on row numbers of (today's date less 90) to (today's date).

Or, do the data with the most recent at the top and the oldest at the bottom. The older data just gets pushed further down but, as you don't need to see it that doesn't matter.

If you put an image - a white rectangle - it gets anchored to the page and does not move when you add rows. It could therefore blank off unwanted lines.

[Villeroy]

Spreadsheets are inadequate when you need to keep track of something.

[revronfrancey]

Highlight the row labels of the rows you want to hide > r-click > Hide. They are hidden.

holy moly! it was that simple to do the rotation! i had no idea about "hide".

the only remaining thing i'd like to do is set up a macro or use some other tool such that i invoke the macro and then i only have to enter the next days a1c, then the formulas are copied to the new row, the oldest row is highlighted, then hidden. this completes adding the new data and doing the rotation.

suggestions?

ron

Clipboard01.png

Clipboard03.png

I am wondering if you could put the data elsewhere on the sheet and copy the 90 days data you want based on row numbers of (today's date less 90) to (today's date).

Or, do the data with the most recent at the top and the oldest at the bottom. The older data just gets pushed further down but, as you don't need to see it that doesn't matter.

If you put an image - a white rectangle - it gets anchored to the page and does not move when you add rows. It could therefore blank off unwanted lines.

[Villeroy]

All your requirements are met by my database solution.

[John_Ha]

the only remaining thing i'd like to do is set up a macro or use some other tool such that i invoke the macro and then i only have to enter the next days a1c, then the formulas are copied to the new row, the oldest row is highlighted, then hidden. this completes adding the new data and doing the rotation.

I cannot think of a way.

Villeroy's solution with a database is the best way to do what you want.

[revronfrancey]

highlighting the rows, then hiding them simplifies my work going forward. my work also includes a linear graph showing the a1c data points over time and a separate trend line. this is close enough for me going forward.

i thank all of you for your suggestions and support!

ron

[Villeroy]

You could also write the 90 dates and values on a sheet of paper and the next 90 on another sheet of paper.

[revronfrancey]

You could also write the 90 dates and values on a sheet of paper and the next 90 on another sheet of paper.

not really as i want the rolling 90 days but ty!

ron