How to only incld cells that meet set condition in an Excel calc?

[Guest]

I'm looking for instructions on how to create a calculation that only
includes cells that meet a specified condition in a subsequent calculation.
For example, visualize a sheet with three columns...account name, pricing
incentive offered to each account, and the sales result (sale / no sale).
Each account would have it's own row. I want to calculate the average
pricing incentive offered to only those accounts that resulted in a sale.

 

[Pete_UK]

Suppose your data occupies A1:C100. Try this array* formula:

=AVERAGE(IF(C1:C100="sale",B1:B100))

*As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) rather than
just ENTER. If you do this correctly then Excel will wrap curly braces
{ } around the formula when viewed in the formula bar - you must not
type these yourself.

Hope this helps.

Pete