• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula works in Excel 2007, but not in Excel 2003

Locusta

New Member
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
 
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
 
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!
 
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),"")
 
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.
 
Back
Top