How to perform calculations on individual cells of a range - within a Collect function

twarner

Hi folks!

I'm trying to do a calculation with ranges inside a Collect function. I'm not getting the correct results. The goal is to calculate a % variance between a forecast amount and an actual amount. This needs to happen 12 times, as this is a monthly forecast/actual combo.

The goal is to Count how many projects, run by Epic Leads in our Practice, that have forecast/actual variances that are < or = 10%.

I don't want to add all those helper columns in my source data (Financial Tracker) Sheet, so I'm doing the calculations in a Metric Sheet and bringing in the data via cross-sheet references.

Here is where I'm at right now:

=COUNT(COLLECT({Financial Tracker: Epic ID}, {Financial Tracker: PMP Epic Lead}, true, (({Financial Tracker: January Forecast} - {Financial Tracker: January Actuals}) / {Financial Tracker: January Actuals}), <=0.1))

The bolded range is where I believe my problem is. I need to run that calculation on each row in the Epic Financial Tracker and compare it to the criteria of "< = 0.1". I am clearly not doing it correctly. Thinking this may be a place to use the @cell argument, but not sure about this syntax for that, in this case.

By the way, this currently produces no errors, but always results in '1', no matter what I change the last criteria (0.1) to.

Any help is appreciated.

Thanks,

Tony

jmyzk_cloudsmart_jp

Hi @twarner

Unfortunately, you can’t do row-by-row math across two ranges inside a single COLLECT/COUNTIFS criterion. Make a helper that computes the variance per row, then filter on that helper.

For example, add a variance helper column in your source sheet:

[Var Jan]
=IFERROR(ABS(([January Forecast]@row - [January Actuals]@row) / [January Actuals]@row), 1)

Then in your metric sheet you can count Epics within tolerance like this:

=COUNT(

  COLLECT(

    {Financial Tracker: Epic ID},

    {Financial Tracker: PMP Epic Lead}, TRUE,

    {Financial Tracker: Var Jan}, <= 0.10

  )

)

Or, if you just want the row count, a simpler version works with COUNTIFS:

=COUNTIFS(

  {Financial Tracker: PMP Epic Lead}, TRUE,

  {Financial Tracker: Var Jan}, <= 0.10

)