Array formula?

[derek.hughes]

I have this formula:
=EXP(INDEX(LINEST(LN($P$9:$P$14),$C$9:$C$14),1,2))*EXP(1)^(INDEX(LINEST(LN($P$9:$P$14),$C$9:$C$14),1)*C9)

I am essentially calculating the value at specific points on an exponential trendline.

I want it to ONLY calculate this equation when there are values in cells P9:P14 and C9:C14. I am thinking some sort of array where:
=IF(AND(P9:P14<>0,C9:C14<>0)....

...but then I get stuck.

Help!

Thanks in advance.

 

[Andrew Poulsom]

Maybe this will help you:

http://www.mrexcel.com/forum/excel-questions/394619-blank-cells-linest.html

 

[derek.hughes]

I get the concept of that link, but I'm struggling to put the two together successfully...this is a stretch of my excel knowledge as is....


How can I combine the formula at the link with my own?

 

[Andrew Poulsom]

I think:

=EXP(INDEX(LINEST(LN(N(OFFSET($P$9:$P$14,SMALL(IF(ISNUMBER($P$9:$P$14),ROW($P$9:$P$14)-ROW($P$9)),ROW(INDIRECT("1:"&COUNT($P$9:$P$14)))),,1))),N(OFFSET($C$9:$C$14,SMALL(IF(ISNUMBER($C$9:$C$14),ROW($C$9:$C$14)-ROW($C$9)),ROW(INDIRECT("1:"&COUNT($C$9:$C$14)))),,1))),1,2))*EXP(1)^(INDEX(LINEST(LN(N(OFFSET($P$9:$P$14,SMALL(IF(ISNUMBER($P$9:$P$14),ROW($P$9:$P$14)-ROW($P$9)),ROW(INDIRECT("1:"&COUNT($P$9:$P$14)))),,1))),N(OFFSET($C$9:$C$14,SMALL(IF(ISNUMBER($C$9:$C$14),ROW($C$9:$C$14)-ROW($C$9)),ROW(INDIRECT("1:"&COUNT($C$9:$C$14)))),,1))),1)*C9)

confirmed with Ctrl+Shift+Enter.