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

Hi @Phillip Richcreek

(see the screenshot)

Try the following formula in cell C2. This formula returns the average of WEIGHT.

=AVERAGE(TOCOL(D7:D25 / (A7:A25 < TODAY()), 3))

Try the following formula in cell C3. This formula returns the average of TEMP.

=AVERAGE(TOCOL(B7:B25 / (A7:A25 < TODAY()), 3))

Note: the data in red has been fictitiously added by me. So you can put your own data in its place.

If my reply is correct for you then please mark my answer and kindly upvote it. But if you still haven't got the right answer, then explain to me where the problem lies so that I can try to help you again.

IlirU

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.