Define array for LOGEST formula

[finny388]

I have a series of numbers I'm trying to use LOGEST against:

A1:C1
A1=1, B1=2, C1=3

I can use =LOGEST(A1:C1) successfully
I can use =LOGEST({1,2,3}) successfully

but I want to put a condition in to replace zeroes with 1's because zeroes cause #NUM! (I don't know why) (e.g. A1=0, B1=1, 1=3)

so what I want is =LOGEST({(IF(A1=0,1,A1)),(IF(B1=0,1,B1)),(IF(C1=0,1,C1))})

but it returns #VALUE!

even =LOGEST({A1,B1,C1]) fails

what am I missing about defining an array for a formula like this?

[quekbc]

LOGEST uses LOG's to determine the exponential line of best fit. That's why 0s causes #NUM! errors as LOG(0) does not exist.

To make your formula work, try

Please Login or Register  to view this content.

This is an array formula, so you need to press CTRL+SHIFT+ENTER once you type in the formula, rather than just ENTER.

For best practice though, I'd recommend creating another array of numbers that changes 0s to 1s (e.g. IF(A1=0,1,A1) and use LOGEST on it.

[finny388]

Sweet! Thank you!

by the way, do you know how to mark a thread solved?

[quekbc]

No worries. Here you go.

If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

[finny388]

thanks
my problem is solved