Works on windows but not on MAC 2008 Excel

[aworfolk]

I created a Logbook program at my work but when it got sent out one individual had a issue were he was getting #Value he is using Microsoft Excel 2008 for Mac. But when I examined the formula on his machine nothing seemed to be wrong so I added the iferror and the error went away but then it gave a value of 0 instead of the correct value. Does anybody have any idea I attached a small version of the file the issues are on the total pages. Thank you in advanced.

[mcmahobt]

Where exactly are the errors? Saying which tab they are on does not help, since we do not know if the value should truly return a zero, or if it is an "Error" as you state.

[aworfolk]

The Error is on all of the tabs that total information. the totals on those pages should have a number appear but as I said if you use windows 2010 excel it works like it is suppose to it just when moved to mac 2008 excel that I have seen the issues.

[mcmahobt]

Looking at it from a general view, I believe that your use of IFERROR may be incorrect. It will skip entries that return as errors, yes, but only for data that is not formatted as a date. If I'm interpreting this correctly, this is not what you want - rather, for the IFFERROR to "run" after the IF statement within your formula returns a value.

I'm working with your data now and will try to find a solution.

[mcmahobt]

I believe I have worked out the problem(s), but I have only applied it to one tab, so if they persist let me know.

First, the main problem I found was that your formula calculations are set to manual. This means that Excel will not autocalculate the formulas for you when you drag them or copy/paste them into a cell (they'll simply return zero) - you would have to double click the cell, and hit enter to return a value. To fix this (on Windows), navigate to File --> Options --> Formulas and make sure Workbook Calculations are set to Automatic.

Second, you can condense your formula and help out your processor as well by utilizing the following SUMPRODUCT formula with the "--" operator. I entered it into cell A9 on the Total Calendar Year tab, dragged through F9, then pasted the formula into cell A5 and adjusted the column range as necessary, then dragged back through. It seemed to work:

A9:  =IFERROR(SUMPRODUCT(--(YEAR($B$1)=YEAR('Breakdown Flights'!$B$3:$B$765)),'Breakdown Flights'!C3:C765),"")

And A5:

A5:  =IFERROR(SUMPRODUCT(--(YEAR($B$1)=YEAR('Breakdown Flights'!$B$3:$B$765)),'Breakdown Flights'!J3:J765),"")

I tested this by changing the date in the range to 1/1/2004, and the values looked correct, but I'll let you confirm that.

P.S.
I'm not sure, but I believe SUMPRODUCT should work on Mac 2008.

Hope this helps!

[aworfolk]

Ok thank you it works on windows right and as for the calculation set to manual I did that on purpose because there is over 270000 formulas in the Program.