Excel help

[brian]

If anyone is an excel guru a little help would be appreciated

Im trying to get regression on one line of an excel sheet. I have the x and y values on A1:A11 and B1:B11
The regression formula is
n*(sum(ln(y))*sum(x^2))-(sum(x*ln(y))*(sum(x))

Thats just half of it but I can figure the rest out if I get the excel formula. For all the sum, its all the x and y values.

The best Ive got was
=(SUMPRODUCT(H1:H11)*SUMPRODUCT(F1:F11))-(SUMPRODUCT(I1:I11)*SUMPRODUCT(A1:A11))
where H1:H11 was the ln(y) for all the valuse and F1:F11 was x*ln(y) Im looking to simplify that without using F and H.

TIA

 

[Trizoy]

I don't stick to far on the math side of Excel, but if you can break it apart into multiple steps and throw it into a final answer, a user defined function might be the key for you. Ever played with Excel macros? VBA? (If on PC)

I could help you write something that outputs your answer..

Also.. When I have o make long statements I usually break them into many cells, then reference those other cells, just to understand whats going on. Then at the end copy the function back into itself, and have a single cell with the whole function inside it..



TFT

 

[brian]

I don't stick to far on the math side of Excel, but if you can break it apart into multiple steps and throw it into a final answer, a user defined function might be the key for you. Ever played with Excel macros? VBA? (If on PC)

I could help you write something that outputs your answer..

Also.. When I have o make long statements I usually break them into many cells, then reference those other cells, just to understand whats going on. Then at the end copy the function back into itself, and have a single cell with the whole function inside it..



TFT

Thanks, that is what I ended up doing. I think I may look into the macros. Will help later if not now in college.

Thanks for the advice