[Solved] Formula or Macro for Averages

[thespian1967]

I have a database of CSV of blood pressure values. The blood pressure machine takes 3 readings at a time one minute apart. The output is a CSV file with date/time then diastolic, systolic, and pulse values.

I have to give this to my doctor once a month. I would like to have a formula or math function macro that does the following:

Finds each similar date likes this: Dec 31 2018 21:21:31, Dec 31 2018 21:19:53, and Dec 31 2018 21:18:11, then averages the above values and puts those values in their own columns, labeled: Diastolic, Systolic, Pulse.

This is the first day ever I have used a spreadsheet. Thank you for any help or criticism.

[RusselB]

Welcome to the Forums.
If you are using Calc, as indicated by the section of the forum you posted in, then the simplest answer is to use the AVERAGEIF function.
This function is decently explained in the help file.
Just how you specify the conditional parameter to match the dates will depend on just how the dates are being interpreted by Calc from the CSV file.

Since you've never used a spreadsheet before, I'm going to suggest you read [Tutorial]10 Concepts that every Calc user should know
Additionally, you can hit your local library and borrow a beginners book on spreadsheets. A common one that I've seen is called "Excel for Dummies".... don't let the title deceive you... most of what is in that book applies to any spreadsheet with little or no alterations... and the "dummies" part, really, just means that it's for beginners.
If your library doesn't have that book, then the staff should be able to direct you to something similar.

[Villeroy]

1) Call menu:Tools>Options>Calc>General and check the option labeled like "Expand references when rows/columns are inserted". This general option applies to all spreadsheets. It is not a per-document setting. Most users want this option checked.

2) When you open the csv, you have to check "detect special numbers" and set the language setting to English(USA), otherwise you get wrong values or useless text values for the date fields. These settings are permanently stored until you change them.

3) Copy the new data without any leading row of column labels and paste the copied range to some master sheet which collects all values for all years and months (a real database would be preferable). The master sheet should have column labels on top. Instead of plain copy/paste, use Edit>Paste-Special, choose text, numbers, dates (default setting) and add option "Shift Down" in order to paste the data into newly inserted rows. These settings are stored for the current session.

The following has to be done only once:
------------------------------------------------
Select the whole list on the master sheet.
menu:Data>Pivot table>Create...
Confirm the cell range selection
In the next dialog drag the date box to [Row fields], drag [Pulse], [Systole], [Diastole] to data fields.
Double-click the 3 data fields and change "Sum" to "Average"
Click [More Options]
Choose A1 of a separate sheet or some cell right of your list as output location instead of the silly default.
When you confirm the dialog, you get an extra table derived from your list showing the average values for each value in the date(-time) field.
Click any date cell and call menu:Data>Group>Group... check Year and Month.
------------------------------------------
Now your pivot table shows the average values for each month in each year. This will work for many years to come and years in the past.
There are no formulas involved. This is more or less a pure database feature transfered to a spreadsheet application.
If you perform the paste-special with row insertion, the pivot table will always refer to the right cell range. This is also the preferable methods to keep spreadsheet formulas in sync with growing input ranges.
You can drag around the grey pivot table cells to change the positions of fields. Play with that.
menu:Data>Pivot>Refresh or Right-click>Refresh requeries the pivot table.

[thespian1967]

Thank you, sir. I'm feel blessed.

[Villeroy]

Pivot_Heart.ods

(63.77 KiB) Downloaded 109 times

the values look sick since they are random.