My formula stopped working when I moved it to Office 2011 for Mac

[Howard Brazee]

I retired at the end of the year and now use Office 2011 for the Mac
instead of my work office 2007? for Windows. (I upgraded my Office
for Mac to be able to use macros a few months ago).

My golf season started, and I pulled out a macro-enabled spread sheet
that used to work fine on Windows.

I have a column that I use to determine my average score on par-3
holes for a round. Here is AD96


=IF(ISNUMBER(Score!U96),SUMIF(Par!C96:U96,"=3",Score!C96:U96)/COUNTIF(Par!C96:U96,"=3"),"")

Looking at my other pages, I see Score!C96:W96 displays:
6 5 6 4 5 4 5 4 4 43 6 10 5 6 3 5 6 6 5 52 95

I see Par!C96:W96 displays:
4 4 5 3 5 3 4 4 4 36 4 4 3 5 3 5 4 4 4 36 72

I used to get a calculated score of (4+4+5+3) / 4

Instead of seeing a 4.00 in AD96, it displays 3.00

Every AD shows 3.00 (AE shows 4.00 & AF show 5.00 looking for par 4
and par 5 scores).

Any idea what I should look for that changed since I was running this
program on Excel for Windows?

 

[Howard Brazee]

I have had my problem confirmed. I e-mailed my spread sheet to
someone who read my post. He opened it on his computer in both OSX &
Windows and got the same two results that I got.

Any recommendations on my next step to get it to working on OSX?

I retired at the end of the year and now use Office 2011 for the Mac
instead of my work office 2007? for Windows. (I upgraded my Office
for Mac to be able to use macros a few months ago).

My golf season started, and I pulled out a macro-enabled spread sheet
that used to work fine on Windows.

I have a column that I use to determine my average score on par-3
holes for a round. Here is AD96


=IF(ISNUMBER(Score!U96),SUMIF(Par!C96:U96,"=3",Score!C96:U96)/COUNTIF(Par!C96:U96,"=3"),"")

Looking at my other pages, I see Score!C96:W96 displays:
6 5 6 4 5 4 5 4 4 43 6 10 5 6 3 5 6 6 5 52 95

I see Par!C96:W96 displays:
4 4 5 3 5 3 4 4 4 36 4 4 3 5 3 5 4 4 4 36 72

I used to get a calculated score of (4+4+5+3) / 4

Instead of seeing a 4.00 in AD96, it displays 3.00

Every AD shows 3.00 (AE shows 4.00 & AF show 5.00 looking for par 4
and par 5 scores).

Any idea what I should look for that changed since I was running this
program on Excel for Windows?

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

 

[Howard Brazee]

Answer found (someone on who follows this newsgroup has a friend who
came up with the solution - but not explained. If someone knows why
the first formula only works with Excel for Windows but the
"corrected" formula works with both versions of Excel, please share
what you know. Thanks.


The fix is bottom posted:

I have had my problem confirmed. I e-mailed my spread sheet to
someone who read my post. He opened it on his computer in both OSX &
Windows and got the same two results that I got.

Any recommendations on my next step to get it to working on OSX?

Ok, I think this may work. It was tested on the Excel 2004 for the mac
and also 2011. and it give the same value as that of the pc..

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 used the above code and it gave the the same value as the in the
windows.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison