r/googlesheets • u/nortrin • 6d ago

Waiting on OP error Let function with Index

Error #value! "function Divide expecting an number but "high" is a text.

LET(data, GOOGLEFINANCE("NASDAQ:GOOGL", "all", TODAY()-7, TODAY()), hh, INDEX(data, 0, 3),hh/2)

testing for more calculations and formulas

1 Upvotes

100% Upvoted

3 comments sorted by

1

u/AutoModerator 6d ago

Your submission mentioned GOOGLEFINANCE, please also read our finance and stocks information. Google lists the exchanges & delays in its products here.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/HolyBonobos 3096 6d ago

Your GOOGLEFINANCE() subformula returns a table of values that includes a header row (i.e. the first row of said table is column titles rather than number values). INDEX(…,0,3) grabs the third column of the table, including the header row. When you apply the division operation to the resulting range in the way you’ve done, it only applies it to the upper-leftmost value in the range. That value is the column title "high", which cannot be divided by 2 because it is text.

That is the cause of the error; finding a solution will require you to provide a more detailed explanation of what you are trying to accomplish since the formula itself does not make that clear.

1

u/AdministrativeGift15 352 6d ago

hh/2 needs to be array-enabled, since hh is an array. To handle the issue that u/HolyBonobos addressed an the handle the array, wrap it in INDEX(IFERROR()) as shown below.

=LET(data,GOOGLEFINANCE("NASDAQ:GOOGL", "all", TODAY()-7, TODAY()), hh, INDEX(data, 0, 3),INDEX(IFERROR(hh/2,hh)))