Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Passing Variable to LINEST
I am using the following formula:
=SUM(LINEST(A$3:A$6, B$3:B$6)*{61590,1}) I need to use a value from a cell rather than typing the value (61590 in the above equation) into the formula each time. I have tried using quotes, parentheses, brackets, etc. without any luck. I am using Excel 2002. How is this done? Thanks |
#2
|
|||
|
|||
Perhaps try, array-entered
(i.e. press CTRL+SHIFT+ENTER): =SUM(LINEST(A$3:A$6,B$3:B$6)*D2:E2) where D2 will house: 61590 (say) and E2 will contain: 1 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "RW" wrote in message ... I am using the following formula: =SUM(LINEST(A$3:A$6, B$3:B$6)*{61590,1}) I need to use a value from a cell rather than typing the value (61590 in the above equation) into the formula each time. I have tried using quotes, parentheses, brackets, etc. without any luck. I am using Excel 2002. How is this done? Thanks |
#3
|
|||
|
|||
Maybe better to make the cell refs (D2:E2) absolute,
in case you're copying across, so try instead, array-entered: =SUM(LINEST(A$3:A$6,B$3:B$6)*$D$2:$E$2) Adapt to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
Thanks!!
"Max" wrote: Maybe better to make the cell refs (D2:E2) absolute, in case you're copying across, so try instead, array-entered: =SUM(LINEST(A$3:A$6,B$3:B$6)*$D$2:$E$2) Adapt to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
You're welcome !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "RW" wrote in message ... Thanks!! |
#6
|
|||
|
|||
Max wrote...
Perhaps try, array-entered (i.e. press CTRL+SHIFT+ENTER): =SUM(LINEST(A$3:A$6,B$3:B$6)*D2:E2) .... Better still would be using SUMPRODUCT. =SUMPRODUCT(LINEST(A$3:A$6,B$3:B$6),D2:E2) However, if the E2 value were always 1 (for the constant term from the regression coefficients), it'd make more sense to use TREND or FORECAST. =TREND(A$3:A$6,B$3:B$6,D2) =FORECAST(D2,A$3:A$6,B$3:B$6) You can drive screws with hammers and pound nails with screwdrivers, but should you? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use linest with variably sized data arrays? | Excel Worksheet Functions | |||
LINEST bug with cubic polynomials in Excel 2003 | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
I Need VBA Assistance for global variable question | Excel Discussion (Misc queries) | |||
change function variable prompts?? | Excel Worksheet Functions |