ReDo formula Help

[CharlesH]

Hi,

I thought the problem was solved in my post
http://www.vbaexpress.com/forum/showthread.php?t=36992
I'm attaching the workbook to see if this issue can be fixed.
If you look in column Ad, AE and AF you will see the formula that we are having an issue with. If you open this workbook and check the values in these columns and the open workbook in Excel2011 the data in the columns are different. The data in the "Windows" version is correct.( I had to do a replaceall for the "=" for it to calculate in Windows for some reason the Auto and F key did not do the cal)
They should for column AD have a value range of 3.00 to 5.00
Column AE value range 5.00 to 11.00 and for column AF about the same as AE.
Any help would be appreciated.

[tpoynton]

Excel 2004 appears to calculate correctly, Excel 2011 does not. Problem seems to be that 2011 countif/sumif/averageif do not like having ranges across sheets. When I put the par/score data on the same sheet, averageif works.

try using an array formula, for example:

=IF(ISNUMBER(Score!U3),AVERAGE(IF(Par!C3:U3=3,Score!C3:U3)),"")

instead of hitting return, press command-enter on the mac (ctrl shift enter on PC) to commit the formula; you'll see the curly braces added when you do this.

I did not test extensively, but it seems to work in 2011 and should also work in windows the same. you should be able to adapt to your other columns to suit. good luck!

[CharlesH]

tpoynton,

HI was gone for the past couple of days. Thanks for your suggestion. I sent it to my friend and am waiting on his reply. I tested you suggestion and I receive the see the same value in the Mac and PC. Will wait until I hear from him before I colse the thread. Thanks again.

[CharlesH]

tpoynton,

Thanks for the reply and as of today your suggestion seems to be working.

[tpoynton]

I probably learned more than anyone else did, so thank you!