Averaging in Excel

  Ray5776 19:03 29 Jun 07
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 07

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 07

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 07

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 07

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 07

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 07

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 07

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 07

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 07

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

  Ray5776 21:41 29 Jun 07

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.

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…