CHOOSE function with LINEST in Excel

Image of software function sign fx
Purpose
  • The LINEST function cannot handle a cell with mixed data type (e.g. Est. 2.563).
  • The CHOOSE function can be used to create an array of values, using the RIGHT function to extract the numeric value from the mixed data.
Syntax

=LINEST(CHOOSE({1,2,3,4,5,6,7},B13,C13,D13,E13,F13,G13,(RIGHT(H13,5))+0))

Description
  • The LINEST function takes an array of values and calculates the trend direction of the array.
  • =LINEST(CHOOSE({1,2,3,4,5,6,7},B13,C13,D13,E13,F13,G13,(RIGHT(H13,5))+0)) – this portion of the formula, takes the right-hand 5 characters from the cell H13 and the ‘+0’ forces the extracted characters to be treated as a numeric data type.
Method
  1. Create the array of values to be used in the LINEST calculation.
  2. Type the formula in a blank cell, referring to the array of cells, and using the RIGHT function in the relevant position in the array.