Easy formula but not for me

[Harvey Waxman]

3 columns, A-B-C

I want to calculate the average percentage change from all the items in
A to B but only if C is blank

{IF(C1:C100=""), (SUM(A1:A100)/SUM(B1:B100))-1)}

I know this is wrong but you get the idea.

 

[macropod]

Hi Harvey,

Try:
=AVERAGE(IF((C1:C100="")*(B1:B100<>0),1-A1:A100/B1:B100))
as an array formula.

Note: I've added a test to exclude entries where B1:B100=0, otherwise these will cause an error.

 

[Harvey Waxman]

Try:
=AVERAGE(IF((C1:C100="")*(B1:B100<>0),1-A1:A100/B1:B100))
as an array formula.

Well done. The last calculation needed to be (A1:A100/B1:B100)-1 to
avoid a negative number but the rest was a great help.

There seem to so many different ways to do calculations. Do you have a
favorite resource for these formulas?

Thanks

 

[macropod]

Hi Harvey,

There's a number of good sites, some of which are managed by the MS Excel MVPs. There's a bunch of links here:
http://www.mvps.org/links.html#Excel

 

[Harvey Waxman]

There's a number of good sites, some of which are managed by the MS Excel
MVPs. There's a bunch of links here:
http://www.mvps.org/links.html#Excel

Thanks