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,

• Employee
edited 11/05/20

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

October 8 - 10, Seattle, WA | Register now

• Employee

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

October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!