vLookup and AVG

styless

Hello, I am trying to do a vlookup to count the average days to fill for each Org Level, by Exempt VS Non-Exempt.

This is the source sheet: https://app.smartsheet.com/b/publish?EQBCT=2b4a116ae47c4d17a13c6fea0d751347

Sheet I wanted the Vlookup results- for:

Exempt: https://app.smartsheet.com/b/publish?EQBCT=1fea33b561284fdb8a7778e8bd3b8f13

Non-Exempt: https://app.smartsheet.com/b/publish?EQBCT=2b15bb85accd46b89892a5b3dcaf64e3

Thanks in advance, appreciate the help!

Genevieve P.

Hi @styless

This would actually be an AVG(COLLECT formula with cross-sheet references (instead of Vlookup). An AVG(COLLECT works like this:

=AVG(COLLECT({Range to Average}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2, ...) etc

Based on your sheets, this would be one example formula:

=AVG(COLLECT({Testing Sheet - TA Range to be Averaged}, {Orgl Lvl Column}, =[Org Lvl]@row, {FSLA Column}, ="Exempt"))

This would average the "Days to Fill" column in your source sheet, but only if the Org Lvl Column in your source sheet matches the same content that's in your Primary column of this current sheet, and only if it's "Exempt" in the FSLA Column. To update this for your second sheet, you would just need to change "Exempt" to "Non-Exempt" and create the cross-sheet references.

Here are some Help Center articles to guide you through the different elements:

• Cross-Sheet References
• COLLECT function
• @row function

Hope this helps! Cheers,

Genevieve

Genevieve P.

Hi @styless,

The ROUND function needs to wrap around the Number portion of the formula (so after the "IFERROR" but before the "AVG(COLLECT" portion).

Try this:

=IFERROR(ROUND(AVG(COLLECT({2020 ENGIE Impact Hiring Dashboard Olvl3}, {2020 ENGIE Impact Hiring Dashboard Org lvl}, =[Org Lvl]@row, {2020 ENGIE Impact Hiring Dashboard FSLA}, ="Exempt"))), "No Data")

Two things to note about adding this in:

• Don't forget to close off the open parenthesis ) after the "Exempt" and before the "No Data" text.
• If you wanted to round to a specific number of decimals, this would be added before the end of that parenthesis, like so: )), 2), "No Data")

You can read more about ROUND in the Help Center (click here). Paul, please feel free to correct me if I missed something!!

:)

Genevieve

Genevieve P.

Hi @styless

This would actually be an AVG(COLLECT formula with cross-sheet references (instead of Vlookup). An AVG(COLLECT works like this:

=AVG(COLLECT({Range to Average}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2, ...) etc

Based on your sheets, this would be one example formula:

=AVG(COLLECT({Testing Sheet - TA Range to be Averaged}, {Orgl Lvl Column}, =[Org Lvl]@row, {FSLA Column}, ="Exempt"))

This would average the "Days to Fill" column in your source sheet, but only if the Org Lvl Column in your source sheet matches the same content that's in your Primary column of this current sheet, and only if it's "Exempt" in the FSLA Column. To update this for your second sheet, you would just need to change "Exempt" to "Non-Exempt" and create the cross-sheet references.

Here are some Help Center articles to guide you through the different elements:

• Cross-Sheet References
• COLLECT function
• @row function

Hope this helps! Cheers,

Genevieve

styless

Hi Genevieve,

Thank you for the prompt response. Not sure what went wrong, but my result came up as #INCORRECT ARGUMENT SET

=AVG(COLLECT({2020 ENGIE Impact Hiring Dashboard OLvl}, [Org Lvl]:[Org Lvl], =[Org Lvl]@, {2020 ENGIE Impact Hiring Dashboard FSLA}, ="Exempt"))

I used Cross-Sheet reference to look for the column in another sheet.

2020 ENGIE Impact Hiring Dashboard OLvl = This is referring another sheet column

[Org Lvl]:[Org Lvl] = I used this column from the sheet I wanted the results to be in

2020 ENGIE Impact Hiring Dashboard FSLA = Referenced from other sheet on the FSLA column

Please help. Thanks in advance!

Paul Newcome

@styless Try changing your [Org Lvl]:[Org Lvl] portion to a cross sheet reference looking at that column on the source sheet. You will also need to finish out the @row reference in the criteria immediately following that range.

=[Org Lvl]@row

Genevieve P.

⬆️ What Paul said! :)

styless

Hi Paul,

=AVG(COLLECT({2020 ENGIE Impact Hiring Dashboard Olvl3}, {2020 ENGIE Impact Hiring Dashboard Org lvl}, =[Org Lvl]@row, {2020 ENGIE Impact Hiring Dashboard FSLA}, ="Exempt"))

it came up "Divide by Zero" - Scratching my head lol..

2020 ENGIE Impact Hiring Dashboard Olvl3 and 2020 ENGIE Impact Hiring Dashboard Org lvl are referencing the same range of column from the same sheet, is that correct? They are both referencing the "Org Lvl" column.

Genevieve P.

Hi @styless

The very first range should be what you want to Average - in this case, the "Days to Fill" column (not the Org lvl column).

Once you've updated the range, let us know if it works!

Thanks,

Genevieve

styless

Hi @Genevieve P.

It works like a charm :D Thank you so much guys for the support!

One lat thing, how do I incorporate IFERROR into that formula. For the one that has no sufficient data yet they are showing as "Divide by ZERO".

Thanks :)

Genevieve P.

So great to hear that!

IFERRORs are wonderfully simple - you just have to add it around your entire formula, like so:

=IFERROR(copy/paste your exact formula), "No Data")

So in your case:

=IFERROR(AVG(COLLECT({2020 ENGIE Impact Hiring Dashboard Olvl3}, {2020 ENGIE Impact Hiring Dashboard Org lvl}, =[Org Lvl]@row, {2020 ENGIE Impact Hiring Dashboard FSLA}, ="Exempt")), "No Data")

You can have the words in the quotation marks be anything you'd like, including just blank. Blank is indicated with two quotation marks without anything in-between, like this "".

You can read more about IFERROR in the Help Center (click here).

Let me know if you have any other questions!

Cheers,

Genevieve

Paul Newcome

The first range should contain the actual numbers you want to average.

Paul Newcome

@Genevieve P. I must have forgotten to refresh the thread before posting. I had opened it earlier and then got side tracked, so I didn't see the rest of the exchange until after I posted my last response. Haha.

@styless Happy to help! Glad you were able to get it working. 👍️

styless

@Genevieve P. or @Paul Newcome - a quick follow up question, how can I incorporate ROUND formula in here? Currently the numbers are showing decimals and when I pulled it to my powerBI dashboard, it is kinda messy. I used the "Decreased Decimal" feature and it hides the decimals in the SmartSheet itself but not on my dashboard.

=IFERROR(AVG(COLLECT({2020 ENGIE Impact Hiring Dashboard Olvl3}, {2020 ENGIE Impact Hiring Dashboard Org lvl}, =[Org Lvl]@row, {2020 ENGIE Impact Hiring Dashboard FSLA}, ="Exempt")), "No Data")

Many Thanks.

Genevieve P.

Hi @styless,

The ROUND function needs to wrap around the Number portion of the formula (so after the "IFERROR" but before the "AVG(COLLECT" portion).

Try this:

=IFERROR(ROUND(AVG(COLLECT({2020 ENGIE Impact Hiring Dashboard Olvl3}, {2020 ENGIE Impact Hiring Dashboard Org lvl}, =[Org Lvl]@row, {2020 ENGIE Impact Hiring Dashboard FSLA}, ="Exempt"))), "No Data")

Two things to note about adding this in:

• Don't forget to close off the open parenthesis ) after the "Exempt" and before the "No Data" text.
• If you wanted to round to a specific number of decimals, this would be added before the end of that parenthesis, like so: )), 2), "No Data")

You can read more about ROUND in the Help Center (click here). Paul, please feel free to correct me if I missed something!!

:)

Genevieve

Paul Newcome

@Genevieve P. and @styless Looks good to me. 👌

The only thing I would add is that there additional rounding functions depending on your exact needs such as...

ROUNDUP

ROUNDDOWN

MROUND

FLOOR

CEILING

But all of these would be incorporated in the same manner/location/syntax as Genevieve's solution.