I have a user who is receiving #Value! as the result when viewing the spreadsheet on SharePoint, but when opening the file in excel the result displays as expected.
A somewhat simplified version of the formula is:
=MATCH(TRUE,INDEX(SUMIF(OFFSET($D1,1,,ROW($D1:$D2)-ROW($D1)+1,1),"<>0")>=F1,0),0)
where the D colomn contains an array of integers and the F column is an upper limit integer.
D1 - 25
D2 - 50
D3 - 75
F1 - 75
The values in the D column are added up until their sum becomes greater than or equal to F. The result that is wanted then is how many integers needed to be added togather when the upper limit was reached.
I hardcoded some integers so that I know the match will occur once the first two integers are added up, and the result will be 2.
In excel this works fine but on SharePoint viewing with excel services the result is #Value!
I tried another version of the formula to achieve the same result:
=MATCH(75,INDEX(SUMIF(OFFSET($D1,1,,ROW($D1:$D2)-ROW($D1)+1,1),"<>0"),0),0)
This is identical except that instead of matching true or false against F1, I just Match directly against 75, knowing I will hit that exact match in my simplified example. This works as well in excel, but in SharePoint Excel Services the result is #N/A
Can't find anything documented on this being an issue, so I'm hoping someone might have some experience with this or something similar.
I use:
- SharePoint 2010 14.0.6105.5000
- Excel 2010
sharepoint
