1

I am trying to create an annotated timeline with the following data, which exists on another sheet:

My sample data

I am using an ARRAYFORMULA to retrieve the text. I want to display these values as an annotated timeline chart, but I keep getting this error:

Each values column may be followed by one or two annotation columns. column number 1 is of type string

The error I am getting

I have tried

  • (Select Column) Format->Number->Date and

  • (Select Column)Data->Data Validation->Must be valid date

It is driving me nuts. Is it because it is an ARRAYFORMULA? If I hand type it out again (which is simply untenable) the chart generates fine. Is there a fix, or is this just broken?

Google Sheets link:

https://docs.google.com/spreadsheets/d/1sIEgWVPLrNWYImCIlWS8gvj8JQIe-cB4P2grlHW0ysk/edit?usp=sharing

On "Sheet20", you will see the failing version, and if you scroll right you will see the identical hand typed data on a working version.

  • 1
    your issue is in column B arrayformula formating, not in array of A column – user0 Jan 16 at 5:41
1

Yes they can, but the whole array needs to be converted back into VALUE()

B1: =ARRAYFORMULA(IF(LEN(A1:A),VALUE(SUBSTITUTE(Patches!B1:B1000, ".", "", 2)), ))

3

  • Can you explain what was causing this? I don't understand what you mean by back into __VALUE ()__. Did I accidentally convert something or was it a byproduct of ARRAYFORMULA ()? – Tyler Gubala Jan 16 at 12:32
  • @TylerGubala your formal formula in Sheet20!B1 was =ARRAYFORMULA(SUBSTITUTE(Patches!B1:B1000, ".", "", 2)) – user0 Jan 16 at 12:47
  • @TylerGubala if you want to create annotated timeline chart the 2nd column needs to have number (numeric) values. array you imported from Patches!B1:B was in text format so timeline chart resulted in error you mentioned. you can test it with =isnumber(Patches!b1) and =isnumber(Sheet20!b1). first is FALSE 2nd is TRUE (because array was re-formated from text into values). so to answer your question: its not byproduct of arrayformula. and it was caused by not converting B column (imported data due array) into values – user0 Jan 16 at 12:48
  • 1
    @TylerGubala I wouldn't say "wrong"... just in this case you forgot the middle step to convert array into array with values – user0 Jan 16 at 13:48
  • 1
    That makes much more sense now. Thank you for the clarification. – Tyler Gubala Jan 16 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.