Assistance

[MarkAndrews]

Hi Guys

I'm after a little help (See below table)

What I am trying to achieve, is an average of cells, B, C & D in column E (ascending)

However, sometimes B,C,D may not have data in, which effect the over all average, how is this best achieved?

TIA

FE Monthly Analysis.xls
	A	B	C	D	E
2	FeeEarner	March	April	May	Average
3	AYasmin	132%	72%	106%	102%
4	BKelly
5	BVickers			170%
6	CCarr	141%	82%	133%	112%
Data - %

 

[onlyadrafter]

Hello,

So for B Vickers the average will be 56.67%?

why not sum then and divide by 3?

 

[SteveO59L]

Average() will ignore blank cells anyway

 

[Andrew Poulsom]

Shouldn't it be?

=IF(COUNT(B3:D3)=0,"",AVERAGE(B3:D3))

Average ignores blank cells.

 

[MarkAndrews]

Shoot, Never thought of that

=COUNTA(B3:D3)/SUM(B3:D3)

But some result in #DIV/0!

 

[onlyadrafter]

Hello,

Try

=IF(ISERROR(COUNTA(B2:D2)/SUM(B2:D2)),"",COUNTA(B2:D2)/SUM(B2:D2))

 

[MarkAndrews]

Shouldn't it be?

=IF(COUNT(B3:D3)=0,"",AVERAGE(B3:D3))

Average ignores blank cells.

Thanks Andrew - I read this after my initial reply

 

[MarkAndrews]

This is what i have now....

FE Monthly Analysis.xls
	B	C	D	E
3	132%	72%	106%	103%
4
5			170%	170%
6	141%	82%	133%	119%
7
8	83%	108%	76%	89%
9	172%	128%	164%	155%
10	147%	139%	124%	137%
11	136%	73%	132%	114%
12	111%	139%	208%	153%
13
14
15	108%	173%	118%	133%
16	108%	88%	75%	90%
17	174%	83%	150%	136%
18
19
20	168%	108%	200%	159%
21	214%	86%	106%	135%
22	113%	100%	76%	96%
23	92%	211%	113%	139%
24	110%	143%	120%	124%
25	132%	64%	100%	99%
26
27
28	134%	112%	128%	126%
Data - %

 

[MarkAndrews]

Will E28 be correct?

 

[Andrew Poulsom]

Will E28 be correct?

It's correct to the extent that blanks are ignored (not treated as zero). What do you want it to be?