Help me annotate/explain a spreadsheet. I need help with the formulas.

Question

The Spreadsheet in question defines blood pressure readings and temperatures. I need help to decide what functions I might use to further explain the raw data.

Thanks for your help.
PR

Answer 1

The AVERAGE function will ignore empty cells. You can calculate the average temperature for the entire range with

=AVERAGE(B4:Bxx)

A similar approach will work for weight in column D.

You can convert the 1xn array of text in Column C to a 3xn array of numbers with the formula

=IFERROR(TEXTSPLIT(TEXTJOIN("|",FALSE,C3:C12),"/","|",FALSE)+0,"")

If you put this off to the right somewhere, such as in G3, you could then use a series of conditional formatting formulas that apply to C3:Cxx to highlight the entries of interest. For example

1 If either the systolic or diastolic values exceed some thresholds, then color red.

2 If both values are below some thresholds, then color blue

=AND(G3<>"",H3<>"",OR(G3>140,H3>80))
=AND(G3<>"",H3<>"",G3<115,H3<75)

It gets a little bit trickier and the formulas become pretty long if you don't want to process all the available data but stop at some particular date. In that case, a macro may be a simpler approach that will be easier to understand and maintain.