Ivan
|
| posted on 16/7/10 at 04:49 AM |
|
|
Excel help needed - trendlines
Hi - been using trendlines for years and suddenly it all goes pear shaped:
Data:
200 = 215000.00
315 = 233406.24
500 = 270279.12
630 = 309626.40
Chart - Scatter chart
Trendline - 2nd order polynomial
Formula: y=(0.237*x^2) + (20.10 * x) + 20209
R squared = 0.999
Problem - using formula above to calculate for x = 315 get 50056.83 and not around about 233406 - where have I gone wrong.
By the way for those interested the above is for replacement cost of minisubstations with oil RMU's including installation in ZAR.
[Edited on 16/7/10 by Ivan]
|
|
|
|
|
PSpirine
|
| posted on 16/7/10 at 06:26 AM |
|
|
Given that all I do at work is Excel, let me stick this data in and see if I can come up with an answer!
|
|
|
PSpirine
|
| posted on 16/7/10 at 06:36 AM |
|
|
Think there's a bit of a maths fail going on
I got the exact same result as you when using a normal calculator, but when properly working the formula out, the result you get with it is 232019,
which is correct.
Nothing wrong with the trendline matey! 
|
|
|
v8kid
|
| posted on 16/7/10 at 06:41 AM |
|
|
Interesting I take it you have 200 and 630kva substations in SA. Here we tend to concentrate on 500 and 1mva with pole mounted for the rural stuff
|
|
|
RIE
|
| posted on 16/7/10 at 06:59 AM |
|
|
The trendline equation I got had "...+202099", not 20209. Give that a try, it should get you closer.
[Edited on 16/7/10 by RIE]
|
|
|
PSpirine
|
| posted on 16/7/10 at 07:07 AM |
|
|
Maybe that's why I got the correct answer the second time - the first time I just used your equation! Tried to trick us did ya! 
|
|
|
Ivan
|
| posted on 16/7/10 at 10:54 AM |
|
|
Thanks all - I am so ashamed - yes it's 202099 - thats what happens when you start working at 05:00 0n a cold winter's morning Wasted
1 hour on trying to see what was wrong 
Thanks once more - Locost Builders is top once again Sorry it took so long to reply - was out inspecting services in Langebaan.
v8Kid - our substations range from 10 kVA (normaly rural and pole mounted for one farm) upwards - the majority are in the 200 - 500 range and of all
sorts - pole mounted, ground mounted and minisubs. Seldom see anything bigger than 630 in the towns I work in except fo High Voltage stuff.
[Edited on 16/7/10 by Ivan]
|
|
|
Ivan
|
| posted on 17/7/10 at 04:11 AM |
|
|
Ok - did another calculation and realised that the text box for the trendline formula is set too small by microsoft and cuts off the last number in
the formula - so I had no need to be mad at myself - just Microsoft for not properly configuring the box - solved problem by reformatting the
trendline lable to 4 decimal places.
|
|
|
MikeRJ
|
| posted on 18/7/10 at 04:22 PM |
|
|
quote:
Originally posted by Ivan
Ok - did another calculation and realised that the text box for the trendline formula is set too small by microsoft and cuts off the last number in
the formula - so I had no need to be mad at myself - just Microsoft for not properly configuring the box - solved problem by reformatting the
trendline lable to 4 decimal places.
This has always been the case for trendlines in Excel; the factors are shown to a couple of decimal places by default and you have to expand this if
you actually need to use polynomial factors with any degree of accuracy.
|
|
|
