Search code examples
google-sheetslambdagoogle-sheets-formulaarray-formulasgoogle-finance

Multiply Parameter exclude header row


I am trying to multiply the number of owned shares by the current price, however, I am getting the following error:

Function MULTIPLY parameter 2 expects number values. But 'OWNED SHARES' is a text and cannot be coerced to a number.

enter image description here

SYMBOL OWNED SHARES SHARES PRICE
ABML 100 0,5
BABA 100 100

Function is

={"SHARES PRICE";BYROW(A2:A, LAMBDA(r, IF(r="",,GOOGLEFINANCE(r, "price")*B:B)))}

I tried *B2:B but it does not work.

Excel demo: https://docs.google.com/spreadsheets/d/1e9khzIuRrx9kJHyp8CqS22qX98aLg-50cfd9FCks7fc/edit?usp=sharing

Note: I added +100 into the Owned Shares Function just for the demo it loads the data from another sheet in reality.


Solution

  • use:

    ={"SHARES PRICE"; INDEX(IFERROR(BYROW(A2:INDEX(A:A, COUNTA(A:A)), 
     LAMBDA(x, GOOGLEFINANCE(x, "price")))*B2:B))}
    

    enter image description here