Excel 2000 averageif

QuestionEdit

Hi there. I have a column of numbers in an2000 spreadsheet. I need to calculate the average of the numbers which are greater than 12 (in context, my clients are contracted for 12 months, but some stay longer. I want to know the average time they stay after their contract expires). Have tried various formulae but keep getting the #value error. Most promising was AVERAGE(IF(A1:A99=">12",A1:A99)), but it won't work!

Please help!

AnswerEdit

Copy and paste the below formula:- =AVERAGE(IF(A1:A99>12,A1:A99))

Place the cursor in the formula cell and press F2 and hit Cntrl+Shift+Enter, since it is an array formula. Array formula's needs Cntrl+Shift+Enter and the general enter won't work. After hitting Cntrl+Shift+Enter the formula will be covered with Curly Braces {} like the below. {=AVERAGE(IF(A1:A99>12,A1:A99))} Don't add the curly braces manually.

Hope it¡¯s clear!