Formula works in Excel 2007, but not in Excel 2003

[Locusta]

A friend asked me to create a spreadsheet that he could use for our golf league. Our averages are based on the last 3 scores that you shot. I found an array formula on this site somewhere that works wonderfully in Excel 2007.

=IFERROR(ROUND(AVERAGE(AVERAGEIFS($E3:$AA3,$E$1:$AA$1,LARGE(IF($E3:$AA3>0,$E$1:$AA$1),{1,2,3}))),0),"") ctrl+shift+enter

Unfortunately, my friend is using Excel 2003 and this is throwing an error and filling the cell with #NAME? for him.

How should this formula be modified so that it will work with Excel 2003?

Thank you in advance,
Locusta

 

[Nebu]

Hi:

In Excel 2003 you will have to use "IF(ISERROR(" instead of "IFERROR". Also I am not sure whether 2003 has AVERAGEIFS as a native function.

Thanks

 

[Locusta]

Thank you Nebu! I've done more research and the ISERROR did fix part of the issue.

Unfortunately, you are also correct that AVERAGEIFS doesn't seem to be part of Excel 2003.

Does anyone have any ideas how to modify the formula so it will work with the older version?

Thank you!

 

[Nebu]

Hi:
Try using array formula "=Average(If(" Instead of "AverageIfs"

Thanks

 

[Faseeh]

Hi Locusta,

Please try this with CSE

=IFERROR(ROUND(AVERAGE(AVERAGE(IF($E$1:$G$1=TRANSPOSE(LARGE(IF($E3:$G3>0,$E$1:$G$1),{1,2,3})),$E$3:$G$3))),0),"")

 

[Locusta]

I will try both options when I get home from work.

Thanks,
Locusta

 

[Locusta]

Thanks for the help Nebu and Faseeh, I think I've got it going now. Sent it to my friend who has 2003. Hopefully it works for him now.