#VALUE! Error in Excel opened online/in Microsoft Teams

0
votes

I have created the following Excel function for summing up monthly working hours:

=SUMPRODUCT((YEAR('FY 2020'!$B$3:$B$1282)=2019)*(MONTH('FY 2020'!$B$3:$B$1282)=11)*'Research hours'!$G$3:$G$1282)

However, there seems to be compatibility problems with the Excel desktop app VS. Excel online versions VS. Excel version in Microsoft Teams: An #VALUE! Error occurs when I open the Excel file in Microsoft Teams or online, while in the Excel Desktop App the formula works perfectly.

  • When I download the Excel file from Microsoft Teams and open it on my desktop app, no formula error occurs. However, when my colleagues download the Excel file from Microsoft Teams and open it offline in the Excel app, the value error still occurs.
  • When I open the Excel file via the "Open in Excel App"-Function in Microsoft Teams (file still saved in Microsoft Teams cloud), no error occurs. (However, for some of my colleagues, the error occurs.)
  • But when I watch the Excel in Microsoft Teams or Excel online, the #VALUE! Error occurs.

Any ideas and help appreciated!

  • Same language Settings in Excel Desktop App & Microsoft Teams: German
  • Microsoft Teams Version 1.2.00.34161 (64-Bit)
  • Microsoft Office 365 ProPlus
1
excelexcel-formulamicrosoft-teams
If you Evaluate Formula (possibly with fewer rows), at which point is the error introduced? – Chronocidal
Also, when using Boolean as Binary like this, I would recommend adding -- to convert from TRUE/FALSE to 1/0 (i.e. --(YEAR('FY 2020'!$B$3:$B$1282)=2019)*--(MONTH('FY 2020'!$B$3:$B$1282)=11)) – Chronocidal
In the Microsofot Teams or Excel Online version I do not have the option "Evaluate Formula", while in my Desktop App the error does not occur why an evaluation is no option. – funkfux
The evaluation should be done by one of your colleagues that is getting the error after downloading. – EEM

1 Answers

-1
votes

Had the same issue and figured it's purely related to date formatting.

I had the dates I was referring to in YYYY-MM format and Excel online shown #Value error. e.g. this formula

=SUMPRODUCT((MONTH('TL source'!$A$2:$A$37)=COLUMN(A2))*('TL source'!$B$2:$B$37=$B27),'TL source'!$C$2:$C$37)/'Customers flights'!AF$20*1000

was calculated fine in Excel desktop but shown error in ExcelOnline.

Changing the celly type to General or date format with an asterisk didn't work as well.

What I did was adding day by adding &"-01" to the formula picking the date so its in YY-MM-DD format and it works fine now. Jakub