Average Number of Bids and Average Bid Price per Unique Bid ID

Hi,


I'm trying to get the average number of bids submitted as well as the average bid price per unique bid number. Can someone please help me with this formula?

The bid numbers will sometimes have letters in them so they won't always be only numerical.


Thank you,



Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/05/20

    Hi @Jude deTar

    Both the AVG function and AVERAGEIF function require purely numerical data as the column to average in order to complete their calculations.

    When you note that "the bid numbers" will sometimes have letters with them, are you referring to the numbers to average or the number that is the criteria you're filtering by?

    If you're just referring to the criteria to filter by, then that's no problem!

    Here's an example of the AVERAGEIF function, presuming that your "Total Price" column is the one you want to Average, and that all the data in that column is numerical:


    =AVERAGEIF({Bid Number Range}, [Bid Number]@row, {Total Price to AVG})

    Note that here I've used cross sheet references, presuming that you're building this on a separate metric sheet that has all the different Bid Numbers listed in one column.

    Let me know if you have any additional questions!

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin

    My apologies, I just realized you also asked for the average number of bids per bid-number, as well.

    In this case, you will want to first do a cross-sheet COUNTIF formula to COUNT how many rows have the specific bid number.

    =COUNTIF({Bid Number Range}, [Bid Number]@row)

    Then, once you have each bid's count, you can do a simple AVG of the entire column where the COUNT formula is placed.

    I hope this helps!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!