Formula for Baseline Variance Breaks in Reports

I have a summary field which uses an INDEX/MATCH pair to pull the timeline variance of a project target:

=-INDEX(Variance:Variance, MATCH(true, [Project Target]:[Project Target]))

The formula works perfectly in when viewed in the sheet, but returns #INVALID OPERATION when pulled into a report. If I open the sheet to view the output, then reload the report, the error disappears and the value is shown. However, this regularly returns an error if I open the report on its own.

My thought is that this is an error with the new baseline functionality, as I am able to use the same formula above to pull from columns other than "Variance" without issue.

Any help on this would be appreciated.

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Ian Ix

If I'm understanding you correctly, you're using a Project Target column to check a box for one specific row, then using this as a criteria in an INDEX(MATCH formula in your Sheet Summary in order to pull values from other columns. Then you're creating a Sheet Summary Report from these fields, and this one formula displays an error in the Report.

It may have something to do with the Variance column being part of the new Baseline feature, however I haven't been able to replicate the error yet. There's a few things I'd like to check:

Can you identify how the Project Target column is being populated with that checkbox?
Is it possible that there's a TODAY function on your sheet that isn't being recognized through the Report, but updates your sheet and formulas once the sheet is opened?
Finally, can you try adding another Sheet Summary field which simply = a cell in the Variance column? I'd like to see if a direct reference returns the same error through a report.

Thanks!

Genevieve

Ian Ix

Hi Genevieve, you understand correctly. The project target column is a star symbol column. I don't believe I have any today functions in use on the sheet, and definitely none in the columns associated with this error. I created a direct reference to the target cell and did not not get an error, which is an interesting find.

As I mentioned though, I am using an identical Index/Match pair to pull in other data. For example:

=INDEX([Task Name]:[Task Name], MATCH(true, [Project Target]:[Project Target]))

=INDEX([Baseline Finish]:[Baseline Finish], MATCH(true, [Project Target]:[Project Target]))

Genevieve P.

Hi @Ian Ix

I was able to replicate what you're seeing! I went back to your original formula and noticed a - sign before the INDEX. Is this intentional? This is the "operation" which is being seen as invalid:

=-INDEX(Variance:Variance, MATCH(true, [Project Target]:[Project Target]))

If this is not intentional, I would suggest removing this. If you are looking to swap around your positive and negative results from the Variance column, try this instead:

=INDEX(Variance:Variance, MATCH(true, [Project Target]:[Project Target])) * -1

Let me know if this works for you!

Cheers,

Genevieve

Ian Ix

Hi Genevieve, I performed additional testing and reached the same conclusion. The negative is intentional. I believe this is a bug the dev team needs to address.

My workaround was to calculate the original value in one field ("Temp") and create a new field set to

=-[Temp]#

I'll try your suggested method.

Thanks for the help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.