how to find cells that have a certain text in open office's calc (excel might work also)

Question

I need to calculate averages on my expenses spreadsheet, so I figured out I could do something like this:
- get all cells that have the text "electric bill"
- get the cell at the right of that one, get that value
- average all the values

How can I script this in OO calc? excel tips may work also.

Answer 1

In Excel 2007/2010 you can use AvereageIf. Assuming bill in column A and amount in column B:

=AVERAGEIF(A2:A10,"electric bill",B2:B10)

If OpenOffice doesn't support that, try SumIf divided by Countif:

=SUMIF(A2:A10,"electric bill",B2:B10)/COUNTIF(A2:A10,"electric bill")