If any cell in in a range is blank then don't perform the sum

[Jak]

Hi Guys

I need help with a formula =ROUND(SUM(A12:A14/3),0)

In the image below, Example 1, the value in B14 is derived from the formula above. This formula has been filled down in column B and the rounded, summed and divided values are shown in Examples 2 - 4.

What I would like to do is return "0" in column B where there are blank cells in column A.

In Example 2 above, A15 has no value. So rather than rounding and summing the values in A13:A15 and dividing by 3, I would like B15 to show "0". When a value is added into A15, then it will calculate and return the value in B15.

So unless there are 3 values to use in the calculation, cell B should be "0". Hope this makes sense.

Any help with the formula is appreciated.

 

[Snakehips]

@Jak
For me, your logic would impact upon the result for rows 3 & 4 ????
Here perhaps is one possibility.

Book9
	A	B
1
2	Points	Average
3	34	0
4	10	0
5	16	20
6	7	11
7	31	18
8	8	15
9	23	21
10	20	17
11	8	17
12	11	13
13	20	13
14		0
15		0
16		0
Sheet2

Cell Formulas
Range	Formula
B3:B16	B3	=IF(SUM(1*ISNUMBER(A1:A3))<3,0,ROUND(SUM(A1:A3/3),0))

 

[jdellasala]

Please use XL2BB (Link in my signature), and update your profile to indicate what version of Excel you're using.

Not sure why you're always dividing by 3 instead of just using average:

Book1
	A	B
1	Example
2	Points	Average
3	34	0
4	10	0
5	16	20
6	7	11
7	31	18
8	8	15
9	23	21
10	20	17
11	8	17
12	11	13
13	20	13
14	20	17
15		0
16		0
17		0
Sheet1

Cell Formulas
Range	Formula
B3:B17	B3	=IF(AND(ISNUMBER(A1),ISNUMBER(A2),ISNUMBER(A3)),ROUND(AVERAGE(A1:A3),0),0)

Bonus tip. The header "Example" is centered across columns A and B using the Horizontal Alignment "Center across selection". Merge and Center $ucks and really screws up the structure of data. I know of only one instance I've used it, and that was because I needed to, and it was in a Dashboard.

Snakehips solution is the same idea but more succinct, while mine may be clearer and avoids the extra step of dividing. Take your pick!

 

[Jak]

Hi Snakehips and jdellasala

Thank you for the formulas and insights that will help with future postings. I have tried them both methods out and each resolves my problem.