Ask Your Question
0

How does this TREND() without a range work [closed]

asked 2015-11-06 12:37:11 +0200

I'm looking at a spreadsheet that gets a result in a cell using something like this :

TREND(
    IF({1,0},
        VLOOKUP(SMALL($AW$2:$AW$20,COUNTIF($AW$2:$AW$20,"<"&B23)),$AW$2:$AX$20,2,0),
        VLOOKUP(LARGE($AW$2:$AW$20,COUNTIF($AW$2:$AW$20,">"&B23)),$AW$2:$AX$20,2,0)
    ),
    IF({1,0},
        SMALL($AW$2:$AW$20,COUNTIF($AW$2:$AW$20,"<"&B23)),
        LARGE($AW$2:$AW$20,COUNTIF($AW$2:$AW$20,">"&B23))
    )
    ,B23
)

How does this work, as TREND() requires a range ? Do those IF({} do something special to the VLOOKUP ?

B23 is a simple value, and AW2:AW20 and AX2:AX20 are simple cell ranges of numbers.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-14 23:05:19.041565

1 Answer

Sort by » oldest newest most voted
0

answered 2015-11-06 12:45:40 +0200

Ahh, OK the IF somehow makes subranges for immediately above and below the b23 value.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2015-11-06 12:35:17 +0200

Seen: 21 times

Last updated: Nov 06 '15