AVERAGE and Conditional formula...

Afternoon all!

I am trying to calculate an average case cost based on several case costs... so far so good

The problems I have are as follow:

- The way the spreadsheet is setup means that the various case costs are not in a range but in seperate cells eg. D70, H70, L70, P70, T70, [...], BD70

- As i want to calculate an average I want to AVERAGE() formula to only take into account the cells where cell is not equal to zero... otherwise my average is inacurate as it will take into account all cells...

So there i am. I do not know how to setup an average forumal that will take into account the condition that each of the cells are superior to zero...

I hope that you guys can help!!

Thanks!

Assuming that A1:A5 contains the following values...

10
15
0
8
12

...and that we'd like to average every 2nd value, excluding zero's, using the following formula...

=AVERAGE(IF((MOD(ROW(A1:A5)-CELL("row",A1)+0,2)=0)*(A1:A5<>0),A1:A5))


(MOD(ROW(A1:A5)-CELL("row",A1)+0,2)=0) returns the following ...

TRUE
FALSE
TRUE
FALSE
TRUE

(A1:A5<>0) returns...

TRUE
TRUE
FALSE
TRUE
TRUE

When these two are combined, forming the first arguement of the IF function...

(MOD(ROW(A1:A5)-CELL("row",A1)+0,2)=0)*(A1:A5<>0) returns the following array...

1
0
0
0
1

Note that the numerical equivalent of TRUE/FALSE is 1/0, respectively. Therefore,

=TRUE*TRUE equals 1
=TRUE*FALSE equals 0

IF((MOD(ROW(A1:A5)-CELL("row",A1)+0,2)=0)*(A1:A5<>0),A1:A5) returns...

10
FALSE
FALSE
FALSE
12

Lastly, the AVERAGE function averages these values, ignoring the FALSE values.

Hope this helps!

Originally Posted by Petitboeuf

Thanks Domenic!!

It worked like a dream


... any chance of getting an explanation (detailed ) of the formula? I get it but would be incapable of reproducing it...

Many thanks in advance!!!

Crystal clear! Thanks for that!