Averageif And #div/0 problem

**Darren70** · 12-02-2008, 02:54 AM

Hi all,

I have created a Spreadsheet to track blood pressure, i have cols set up in Date, Time, Systolic, Diastolic, Pulse. There will be more than one entry for each day, ie every 2 hrs. So to graph each day i set up a daily average for each reading using the following =AVERAGEIF($B$3:$B$380,"4/12/2008",D3:D380) i have entered each date in this formula to the end of the month once a figure is added it calculates fine, but blank cells is giving me a #div/0 and subsequently the total average sum for the month will not work until i resolve this error. does anyone have an idea how to merge the two formulas ?

Thanks in advance
Darren

**DonkeyOte** · 12-02-2008, 04:48 AM

Darren it's slightly unclear as to how your data is laid out but perhaps you could think about using an array formula - ie committed to Excel using CTRL + SHIFT + ENTER:

=AVERAGE(IF($B$3:$B$380="4/12/2008",IF(ISNUMBER($D$3:$D$380),$D$3:$D$380,0)))

Once array is set the above will appear encased within { }

The above will generate a 0 average for each empty day.

Obviously you could also set your formula such that the date value is held within a cell -- eg replace "4/12/2008" with Z1 if Z1 holds the date.

**Barking_Mad** · 12-02-2008, 05:33 AM

I have a similar problem, I have cells:

1) J5:J27 containing numbers to two decimal places. (ie. 3.67)
2) These numbers are only displayed once data has been entered elsewhere on a weekly basis.
3) As a result of this they turn up a #DIV/0 error.
4) In cell J28 I want to work out the average of J5:J27 but it will not do so as

a) It counts all the #DIV/0! entries toward any average
b) It will not give an average but instead displays #DIV/0!

Any ideas?

**Darren70** · 12-02-2008, 05:50 AM

Thank You DonkeyOte, using your suggestion i have managed to get rid of the div/0 mind you it is replaced with 0.00 at least it calculates now

The only weird thing is i had to modify the formula slightly, didn't need to CSE.

Here is a copy of what i modified. (all i need to do is now is ref the date to a cell as u suggested)

=AVERAGEIF($B$3:$B$380,"3/12/2008",IF(ISNUMBER($D$3:$D$380),$D$3:$D$380,0))

I have 2 tables set up with the headings on columns as below

Table1
Date, Time, Systolic, Diastolic, Pulse

Table2 (Daily Averages)
Date, Systolic, Diastolic, Pulse

Final cell in this table
Total Averages

The reason for the second table is merely to graph as i don't want to clutter a graph with times bp is taken just a daily average.

Each month is in a separate worksheet, and a final worksheet with a overall summary of YTD.

If anyone has any suggestions how i can simplify this project i am all ears!

Thanks again!!

**DonkeyOte** · 12-02-2008, 05:52 AM

EDIT: below is in relation to Barking Mad post

2 options:

1 - Simply adapt your formulae in J5:J27 so as not to return DIV0 error, eg. assuming J5 = 10/I5, if I5 is blank you will get a DIV/0 error... changing formula to: =IF(I5,10/I5,"") should resolve the DIV/0 errors and in turn resolve the subsequent Average issue.

2 - Alter the AVERAGE to ignore errors using an array (formula confirmed with CTRL + SHIFT + ENTER):
=AVERAGE(IF(ISERROR(J5:J27)=FALSE,J5:J27))

It is better not to have errors so option 1 is arguably the preferred method.

**DonkeyOte** · 12-02-2008, 06:04 AM

Darren, have you thought of running a Pivot Chart based off Table 1 thus removing the need for Table 2 altogether ?

For ex... highlight Table1 data, via 2007 ribbon: Insert - > Pivot Chart

Axis Field: Date
Sigma Values: Systolic, Diastolic, Pulse --- set all to "Average" as opposed to Sum (via Value Field Settings)

You can alter the chart type etc...

Should point you in the right direction I think.