Need formula help to AVG a range if "Y" @row

Stephanie Kiecker

Here is what I attempted (after tying to find help in the community threads):

=AVG(COLLECT([# of Hours to Complete]:[# of Hours to Complete], [Conciere?]:[Conciere?], ="Y"))

This returns a #Divide by Zero" error

Here is what my sheet looks like, and I need to AVG. the # of hours for all rows that have "Y" in "Conciere?" column

Sounds simple enough but I just can't troubleshoot this one - Thanks in advance for any help you can give me with this formula

DKazatsky2

Hi @Stephanie Kiecker,

None of the values in the "# of Hours to Complete" column are numbers, they are all registering as strings. The formula you have should work as is once you change the column values to properly be numberic.

Hope this helps,

Dave

Stephanie Kiecker

not sure I understand as the column is formatted to be "text/number"

The number you see is showing like this as I have a formula on main sheet that calculates between Created and Modified times:

Once completed the row moves to an archive file so the number comes into the Text/Number column with just the value of the original cell

KPH

Hi @Stephanie Kiecker

Not sure if you are still stuck here, but thought I'd jump in just in case. Like @DKazatsky2 says, the average cannot be found because there are not any numbers to do the calculation on.

2:34 is considered text by smartsheet. Numerical values cannot have a colon in them.

It looks like this is time, maybe hours and minutes, or minutes and seconds. To do math on these times they need to be converted into a decimal. For example, if 2:30 is 2 hours and 30 minutes, this would be 2.5 as a decimal, in other words 2 and a half hours.

You can convert your time into hours using this formula:

=VALUE(LEFT([# of hours to complete]@row, FIND(":", [# of hours to complete]@row) - 1)) + (VALUE(RIGHT([# of hours to complete]@row, 2)) / 60)

Here is how that works:

The part in bold here

=VALUE(LEFT([# of hours to complete]@row, FIND(":", [# of hours to complete]@row) - 1)) + (VALUE(RIGHT([# of hours to complete]@row, 2)) / 60)

Looks at the time cell and returns the left hand portion of the string up to and including the character before the colon.

Wrapping this in a VALUE function converts the text into a number.

=VALUE(LEFT([# of hours to complete]@row, FIND(":", [# of hours to complete]@row) - 1))

The part in bold here

=VALUE(LEFT([# of hours to complete]@row, FIND(":", [# of hours to complete]@row) - 1)) + (VALUE(RIGHT([# of hours to complete]@row, 2)) / 60)

Looks at the time cell and returns the last 2 characters from right hand portion of the string.

Wrapping this in a VALUE function converts the text into a number.

=VALUE(RIGHT([# of hours to complete]@row, 2))

Two demonstrate I have put this formula in the hours part column:

=VALUE(LEFT([# of hours to complete]@row, FIND(":", [# of hours to complete]@row) - 1))

and this in the Minutes part column

=VALUE(RIGHT([# of hours to complete]@row, 2))

Then this part in bold

=VALUE(LEFT([# of hours to complete]@row, FIND(":", [# of hours to complete]@row) - 1)) + (VALUE(RIGHT([# of hours to complete]@row, 2)) / 60)

Divides the part that was after the colon by 60. So if this was 2:30 and the 30 was 30 minutes, this would be 30/60 which gives us 0.5 hours.

Adding that to the other value gives you the time as a decimal.

=VALUE(LEFT([# of hours to complete]@row, FIND(":", [# of hours to complete]@row) - 1)) + (VALUE(RIGHT([# of hours to complete]@row, 2)) / 60)

Here it is in the Hours column.

Now you can do your average.

Stephanie Kiecker

Yes!! I am still stick and your solution above is what I was hoping for and then some...GREAT explanation...Thank you SO much for taking time out of your day to resolve dilemma!!

KPH

Wonderful 💃

Glad we could unstick you.