vLookup and AVG

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!

Best Answers

Answers

  • styless
    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
    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.
    Genevieve P. Employee Admin

    ⬆️ What Paul said! :)

  • styless
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    Paul Newcome ✭✭✭✭✭✭

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

  • Paul Newcome
    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
    styless ✭✭✭✭
    edited 01/17/20

    @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.

  • Paul Newcome
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!