Hi,
You have error because you are trying to convert Number to Number. VALUE requires String.
Btw, “The outputs for {Debug_Main_toString} are identical to {RR Time to Complete}.” - not exactly,  strings are left aligned.
I hope this helps you. 
Check Average of the whole  {RR Time to Complete} column in the bottom totals line.
Your rollup should work, check if you are have correct field in Rollup settings. Maybe you put wrong column there.
 

Note you can put 0 or you can put nothing, both works, but nothing ignored when count average
I’ve shorten formula.

 

Hi, ​@Alexey_Gusev - thank you so much. This is very helpful. 

For context, my ultimate goal is to be able to display the average days it takes to complete an activity on an interface.

I tried replicating the method you suggested, but in my output column, I only ever got a value of 1.

So, Here’s where I’ve ended up, and I’m hoping you have a solid suggestion for how I can improve this.

On the Master Data Set -

First, I added a “helper” column to my base called RR Completed. This is checking that RR Actual Start Date and RR Complete Date are both populated.

I placed this formula in RR Time to Complete: IF({RR Completed}=0, "", IF(DATETIME_DIFF({RR Complete Date}, {RR Actual Start Date}, 'days')<1, 1, DATETIME_DIFF({RR Complete Date}, {RR Actual Start Date}, 'days')))

This solves two problems for me. First, it got rid of the NaN values when either RR Actual Start Date or RR Complete Date are blank. Second, it gave me a value of 1 whenever RR Actual Start Date and RR Complete Date are the same date (otherwise, it was left blank, because the result is technically less than a day). 

However, your tip about the left-aligned result was HUGE, because - viola - the result is definitely a string and not a number. So, I added another “helper” column called RRTC Conversion with the formula: VALUE({RR Time to Complete}), and I get a beautiful column of right-aligned, error-free numbers that I can average at the bottom:

One important thing to note, though, is that the RRTTC Conversion interprets the blanks in RR Time to Complete as zeroes, which is artificially depressing the average. The true average is 29 days.

Now … the rollup.

I added a Lookup column called RRTTC Rollup that links to a separate base called Summary

On the Summary base, I created a Rollup field, allowed linking to multiple records and added a filter to exclude any rows where RR Completed was not checked:

So my last question is this: do I have to manually add new rows to the RRTTC Rollup as their values become >0, or can that be done by a workflow?
 

You can certainly just create an automation triggered by your checkbox that adds the RRTTC Rollup link to each record when the box is checked (or get rid of the checkbox entirely and just trigger it when both RRActual Start Date and RR Complete Date are not empty).

All told though it seems like you have a lot of formula fields going on when you could just datetime diff this and then create a view that is “Only record where Formula > 0”. That filters out any NaN or 0 records and shows you the proper average on the bottom. 

​@DisraeliGears01 - fair enough, but I need to be able to display the average on an interface in a number widget. I realize now, though, that I probably do not need the roll-up for that. Once I was able to get to a column of error-free numeric values, I was able to create the widget and apply needed filters to it there. 😀

Hi,

RRTTC Conversion interprets the blanks in RR Time to Complete as zeroes, which is artificially depressing the average. The true average is 29 days.

That’s exactly the reason why I described how to output empty values, which will be ignored when count Average. In previous comment, after
“THIS IS ANOTHER WAY, PUT NOTHING IN OUTPUT where date(s) absent”


This solves two problems for me. First, it got rid of the NaN values when either RR Actual Start Date or RR Complete Date are blank. Second, it gave me a value of 1 whenever RR Actual Start Date and RR Complete Date are the same date (otherwise, it was left blank, because the result is technically less than a day). 

You don’t need all this ‘over-engineering’ with helpers and conversions. Considering 2 problems above (and third with depressed average), your formula should be very simple and include only 2 fields: Start_Date and End_Date
In Airtable, you don’t need all those Excel stuff “If Field=BLANK() dispay “”, else display Output”. it’s just IF(Field,Output). 
“IF x<1, display 1, else display x” is equal to MAX(x,1)

Version to copy-paste:

IF(AND({RR Actual Start Date}, {RR Complete Date}),
  MAX(1,DATETIME_DIFF({RR Complete Date}, {RR Actual Start Date}, 'days')))

Note - my AVERAGE is lower because it doesn’t include data not seen on your screenshot but it’s true according to screenshot values. Depressed value (with ‘0’) is 17. In your table it will be 29.

About your last question - just include all without any filters and automations. Remove checkbox field and extra stuff. If it works in bottom line average, it will work in rollup, as planned.

​@Alexey_Gusev - THANK YOU AGAIN! The cut/paste worked perfect this time, the dashboard widget works, and this hugely reduces the complexity of my base. Much appreciated (and thank you for your patience and detail).