Converting AverageA from Excel to Avg in SmartSheet

Tari Armenta

I'm having a bit of an issue. I formula in excel that works great. However, when I pulled the project into SmartSheet, I lost all of my formulas. Most I have been able to work around and fix to accommodate SS, but I can't seem to get the one for AverageA to convert.

Original Formula from Excel:

=AVERAGEA(X3,O3,J3,F3,J3,F3)

When I try the same formula with AVG in SmartSheet, I receive the #Divide by Zero error.

Any assistance would be appreciated!

Paul Newcome

You'll need to convert everything to numerical values to average them. One way to do this is within the AVG function itself.

=AVG(VALUE([Column1]@row), VALUE([Column2]@row, VALUE([Column3]@row), ...........................................)

L_123

The same formula will work, just click in the cell you want to output the average, then type in

=avg(

then click the cells you want to average, then hit enter. you shouldn't have to do anything else.

Paul Newcome

You'll need to convert everything to numerical values to average them. One way to do this is within the AVG function itself.

=AVG(VALUE([Column1]@row), VALUE([Column2]@row, VALUE([Column3]@row), ...........................................)

Tari Armenta

Thanks Paul. That was my issue and you fixed it.

Paul Newcome

Happy to help! 👍️

The AVERAGEA function in Excel does that automatically and can even be used to grab true/false values. Smartsheet doesn't currently have an averaging function where that is built in like that, so you would need to use the VALUE function on each individual reference.

If you did need to include true/false values, you would need to nest an IF statement in there as well as the other VALUE funcitons.

=AVG(VALUE([Column1]@row), VALUE([Column2]@row), IF([Column3]@row = TRUE, 1, 0), ..................................)