COUNT and AVERAGE reading zeros, but not other numbers.

Yes, here is the original formula:

=IF(NOT(ISBLANK(A)),IF(E3="Optimal",SUBSTITUTE(E3,E3,0),IF(E3="Normal",SUBSTITUT E(E3,E3,1) ,IF(E3="High-normal",SUBSTITUTE(E3,E3,2),IF(E3="mild",SUBSTITUTE(E3,E3,3),IF(E3 ="Moderate",SUBSTITUTE(E3,E3,4),IF(E3="Severe",SUBSTITUTE(E3,E3,5),IF(E3=TRUE,SU BSTITUTE(E3,E3,3)))))))),"")

But I've tried different ones, some quite simple and straight forward, including =SUM(F$2:F8) right underneath the range F2:F8)

Thanks for your interest.

Yes, here is the original formula:

=IF(NOT(ISBLANK(A)),IF(E3="Optimal",SUBSTITUTE(E3,E3,0),IF(E3="Normal",SUBSTITUT E(E3,E3,1) ,IF(E3="High-normal",SUBSTITUTE(E3,E3,2),IF(E3="mild",SUBSTITUTE(E3,E3,3),IF(E3 ="Moderate",SUBSTITUTE(E3,E3,4),IF(E3="Severe",SUBSTITUTE(E3,E3,5),IF(E3=TRUE,S U BSTITUTE(E3,E3,3)))))))),"")

But I've tried different ones, some quite simple and straight forward, including =SUM(F$2:F8) right underneath the range F2:F8.

The data referred to by the IF function is a column of 6 categories (mentioned in the long formula above). The overriding NOT(ISBLANK() condition is because I only need this report in respect of every fourth row, differentiated by the day of the month in column A. What's interesting is that if I remove the final "" (leaving in the comma), I get a zero as the return - and, lo, the COUNT function loves that. It counts the number of zeros; the same, of course, if I manually insert digits. I tried making sure that the format of these source cells is set to number, also automatic.

The final test with "TRUE" is only necessary to avoid a "TRUE" return where no data has yet been entered, substituting the more or less norm of "3" as an interim placeholder - hardly relevant, I'm sure.

Thanks for your interest.

Thank you Ian, very much. I had no idea how remote I was from attaining my goal. I see now how SUBSTITUTE was wrecking my traction and my account of what I was getting may be a little faulty, leading to the puzzle for you about what I was "getting".

Following your advice and using VLOOKUP, I've generated the running average I wanted. But now I've reached the next stage: to pick up the last running average and 'add it' to the formula in the adjacent column to feature there as its default return. It calculates the category that two prior data cells (in the row) fall into. Until such data is inserted the return is TRUE: by way of the default IF condition. It could readily be changed to FALSE, by inseting a NOT function. That probably doesn't matter, however.

If that default return is being given, I need to 'add' a reference to the cell to the right immediately above so that the return is the latest running average. And I need this factor to be automatically carried down to the end of the column.

It may make that easier to have the last running average figure repeated for all subsequent rows; then, no need to refer to the cell up-right, but sufficient to refer to the one to the right in the same row. That means that the first step would be to modify the running average formula to provde for it a default return and so obviate the present error return.

All this is to be able to produce two charts, one that displays the original data trend on the basis of a day's average (which is entered as a daily row), the second automatically updating, day by day, the levels that the raw data itself reveals. The reasons don't matter, but I still think it can most probably be done.

If all this is too hard to follow, just your first impression could be very helpful. I am extremely obliged to you for the help youv'e already given me. Many thanks.

Regards, Peter White

That was a real help, Ian. I didn't give you enough information for you to be able to see exactly where I was going, but you got me through the main difficulty just the same. My "running average" was needed to enable a new graph (based on the raw data in two columns) to provide a projection of the so far accumulated graph, at any point during a month, to its end — a feature that had several advantages. Unfortunately I don't have the skill you do to make my screenshot of the finished product available to you. But here are the formulas that worked.

=IF(ISBLANK(A),"",IF(F3=TRUE,F3,VLOOKUP(E3,Index::A:WHO graph level,2,0))). Index is what I called my second table.

The formula that gave TRUE or FALSE so the blank return really was an empty cell that did not spoil the COUNT function imbedded in AVERAGE, along the line you suggested, was =IFERROR(NOT(ISBLANK(A)),), with the initial cell being simply a copy of the source cell.

The formula that then provided me with a running average, so that when the point was reached where data was not yet available I could invoke the last available average for the month to provide a forward projection to the end of the current month, was, =IF(F3=TRUE,AVERAGE(G$2:G3),"").

I was able to hide the two columns and consolidate the whole exercise onto one page. It satisfies a need that goes well beyond my need to keep track of my own blood pressure. So I'm most grateful for your help.

Regards, Peter White.

Hi Ian, again. I don't quite get the running average you refer to, but it doesn't matter. I think what I wanted and managed to obtain was what you call an accumulative average. You'll see that in the second graph in my screenshot the last four columns are just a projection of that average as accumulated from the beginning of the month. Exactly what I needed.

I'm now going to ***** the camera icon and add the screenshot...

FAILED. And although I've used Grab to get a screenshot of the toolbar above my reply, for the same reason I cannot include that either. I'm using a Mac. Is that why I don't have a "camera icon"? I do have an insert image icon and also one for inserting a Link. So I have wondered if the solution might be to drop the pic ifile nto Dropbox and provide a link to it there. Any advice?

Regards, Peter White

Here it is, Ian. Fortunately I've worked around rather than pursued or tinkered with the Russian dolls. Once I knew that the "Insert image" icon was what you were pointing me too, I persevered and (eventually) the Choose option emerged.

You can see that the data has been entered for all but the last 4 days of the month (top graph) and that in the lower graph the last 4 days are all the same — because they're the projection of an average accumulated to date. That's just what I wanted.

Thanks again for your interest.

Regards, Peter White.