Averaging in Excel

  Ray5776 19:03 29 Jun 2007
Locked

Hi everyone,
Me again with more Excel.
Can someone tell the formula for working out the averages in column F of the example please bearing in mind that some columns may have no entries.

Date Time 1 2 3 Average
25.06.07 7.00 120/84 135/80 127/82
25.06.07 17.00 125/86 112/78
25.06.07 23.30 122/88
26.06.07 7.30 134/80 131/80
26.06.07 17.00 134/84 132/82 128/85

  VoG II 19:10 29 Jun 2007

Try

=AVERAGE(F1:F100)

and adjust to suit your range. Excel will ignore blank cells. However, I'm not sure what entries like 127/82 are - if they are text you will get an error.

=AVERAGE(F:F)

will also work if you enter this formula in a column other than F.

  Ray5776 19:31 29 Jun 2007

Hi Vog that was quick,
the values represent blood pressures ie 122 over 88 so I would think that an entry with a forward slash would be text.
I will try your suggestion but am now fairly sure I will get an error, perhaps I should enter the data in a different way ie 122 in one row and 88 in the next.

Ray

  Ray5776 19:37 29 Jun 2007

Date Time 1 2 3 Average
25.06.07 7.00 120/84 135/80 127/82 #DIV/0!
25.06.07 17.00 125/86 112/78
25.06.07 23.30 122/88

  Ray5776 19:52 29 Jun 2007

Hi again Vog,
Your formala works fine if I make the entries on 2 lines which will do but ideally I would like it on one line, can I split the cell in half horizontally?
and the enter the 2 values and the formula in the same row?

Ray

  VoG II 20:13 29 Jun 2007

You need them in two columns. Select Column F, Data > Text to Columns, follow the prompts and enter / as the delimiter.

I don't know enough about blood pressure readings to advise whether you can then simply take the average of each column by applying a formula like

=AVERAGE(F1:100) & "/" & AVERAGE((G1:G100)

  VoG II 20:29 29 Jun 2007

I think that you can average the diastolic and systolic values separately click here

Whether reporting the average of the two as A/B is meaningful I do not know.

  daba 21:20 29 Jun 2007

OK, I've got the average working as a single cell formula : its a biggy, so i suggest use copy/paste :-

=FIXED(AVERAGE(VALUE(LEFT(C2,FIND("/",C2)-1)),VALUE(LEFT(D2,FIND("/",D2)-1)),VALUE(LEFT(E2,FIND("/",E2)-1))),0,TRUE)&"/"&FIXED(AVERAGE(VALUE(RIGHT(C2,LEN(C2)-FIND("/",C2))),VALUE(RIGHT(D2,LEN(D2)-FIND("/",D2))),VALUE(RIGHT(E2,LEN(E2)-FIND("/",E2)))),0,TRUE)

this is for data in row 2, and so far it does depend on 3 readings, which you said may not be true.

I'm working on that, but it may need more calculation columns to be effective.

If i get it, i'll post back.

  daba 21:23 29 Jun 2007

Am i right in thinking the readings will be left justified ? as in :-

120/84 130/90 140/85
110/76 122/82

or could the readings be missing anywhere ?

  VoG II 21:24 29 Jun 2007

You seem to be operating on a range of columns. I understood that they were all in column F.

  Ray5776 21:41 29 Jun 2007

Hi Vog, yes you can but I am not looking to do that.
The reason for taking the average of three readings is that it should be more accurate than one, blood pressure varies all the time and the instruments for measuring it are not very acurate even in this day and age.
I will user two lines as said before, this is not really a problem just not the best presentation.
The general idea is to monitor BP over a monthly or quarterly period and look for highs, lows or other blips.
Having given this some more thought it is probably better not to average out the readings but just to record them as they are and give this info to those concerned.
Trying to be too clever here, don`t need the impressive spreadsdheet just the readings really
That`s the trouble with Excel, you want to and
try to make it absolutely right because it`s there.
I am sure you understand this Vog but I can just write them on a sheet of paper which serves the same purpose and email them.

Ray

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Intel Coffee Lake release date and specifications

12 Amazing British Craft Beer Label Designs

watchOS 4 review

Les meilleurs navigateurs internet 2017