AVEDEV with an array formula

Hi - I'm trying to run the AVEDEV function on a column of data which needs to be converted from text to numeric, using a separate reference table. I'm very keen on avoiding a helper column! I was hoping the following as an array function would work, but alas it returns #N/A (and a similar formula with Index/Match also doesn't seem to work)...

{=AVEDEV(VLOOKUP(Datasheet!A:A,Referencesheet!$A$1:$B$10,2,FALSE))}

(average deviation of all values in datasheet column A, converted to numeric values based on a vlookup to the table in the referencesheet)

Please help!

Shashank, thanks for looking into this! Attached is an example file.

BMV, many thanks for this, the second formula works perfectly! It does seem to be sensitive to the data range specified (in this case B4:B12) e.g. returns #N/A if the actual data range is only B4:B9. I'm hoping to use the formula across multiple sheets with varying range sizes (that will also change over time) - any idea if this can be reflected in the formula?

Could you show the example? For me it's not clear what you mind

to use the formula across multiple sheets with varying range sizes

Sure, attached you'll see I've deleted two entries (in red) and now the formula returns an error. Ideally, the formula would be dynamic to whatever the range size was. Is this possible?

Formula:

Please Login or Register  to view this content.

1=0 - it's FALSE
or

Formula:

Please Login or Register  to view this content.

999 - is reservation

The first formula works perfectly (as an array formula). Thanks again, this is great!

Originally Posted by lukethomas

(as an array formula)

it could be changed also. There is non array formula

Formula:

Please Login or Register  to view this content.