L' equazione della linea di tendenza "polinomiale di 5° grado" dichiarata da Excel, per i dati inseriti nel file che hai condiviso, e'
y = 1E-10x^5 - 5E-07x^4 + 0,0005x^3 - 0,2554x^2 + 49,591x + 1522,5
Avendo inserito la "serie crescente 1, 2, 3, 4, ... in D2 e sottostanti la formula da inserire in C2 e'
- Codice: Seleziona tutto
= 1E-10*(D2^5) - 5E-07*(D2^4) + 0,0005*(D2^3) - 0,2554*(D2^2) + 49,591*D2 + 1522,5
Inserendo la colonna C nel grafico il risultato squallido e' stato quello di figura:
Uploaded with
ImageShack.usIn blu la linea di tendenza disegnata da Excel, in rosso la serie calcolata con la formula di sopra.
Ho immaginato che quella clamorosa deviazione fosse dovuta alle approssimazioni di calcolo di excel (i calcoli sono fatti con 15 cifre di precisione, e X^5 produce alla lunga numeri grossi, poi da moltiplicare con numeri piccolissimi, 1^(-10)); ho quindi modificato la formula di calcolo di C2 e sottostanti, con l' obiettivo di minimizzare gli errori di calcolo, in
- Codice: Seleziona tutto
=((D2^3)/(10000000000))*(D2^2) - (5*(D2^2)/(10000000))*(D2^2) + (5*(D2^2)/(10000))*D2 - 0,2554*(D2^2) + 49,591*D2 + 1522,5
Ma la modifica non ha smosso praticamente niente.
Dopo un ulteriore scervellamento rimasto senza esiti ho provato la polinomiale di 4° grado:
l' equazione restituita e' stata:
y = -1E-07x^4 + 0,0002x^3 - 0,128x^2 + 31,16x + 2147,3
Trasformata in formula e in grafico ho ottenuto:
Uploaded with
ImageShack.usMolto buono, e ulteriormente migliorabile con piccole variazioni sui termini di 3° e 2° grado piu' il valore costante, come da formula:
- Codice: Seleziona tutto
= -0,0000001*(D2^4) + 0,0002012*(D2^3) - 0,1299*(D2^2) + 31*D2 + 2247,3
In questo caso la trend line indicata e quella calcolata praticamente corrispondono.
Ho esaminato anche il comportamento su una polinomiale di 6° grado, ma anche qui ho ottenuto differenze clamorose:
Uploaded with
ImageShack.usTasto dx, scegli Visualizza immagine per vedere tutta l' immagine.
A QUESTO PUNTO mi sono ricordato che gia' in un altro quesito mi ero trovato nello stesso labirinto, da cui ero uscito usando la funzione REGR.LIN proprio per ottenere i parametri di una linea di tendenza.
Per una polinomiale di 5° grado la formula da usare e'
- Codice: Seleziona tutto
=REGR.LIN(B2:B1010;D2:D1010^{1;2;3;4;5};;1)
(vedi *Nota, oltre)
Va inserita come formula a matrice in 6 celle adiacenti; es:
-selezionare O2:T2
-inserire la formula nella barra della formula
-premere Contr-Maiusc-Enter
Nel caso dei tuoi dati otterrai:
1,48343E-10 -4,7095E-07 0,000532083 -0,255444597 49,59139671 1522,478531
che sono le costanti moltiplicativi dei fattori D2^5, D2^4, D2^3, ...., piu' il valore della costante.
La formula per D2 sara' quindi
- Codice: Seleziona tutto
= (1,48343*(D2^3)/(100000))*(D2^2/100000) -4,7095*(D2^4)/(10000000)+ 0,000532083*(D2^3)-0,255444597*(D2^2) + 49,59139671*D2 +1522,478531
Restituira' una serie esattamente coincidente con la linea di tendenza disegnata sul grafico.
La formula e' modifcata rispetto a quella teorica [vedasi ad esemoio l' uso di
(1,48343*(D2^3)/(100000))*(D2^2/100000) al posto di
1,48343E-10*(D2^5)] per minimizzare gli errori di calcolo dovuti ai calcoli con 15 cifre.
Notare come quanto restituito nel grafico sia stato grossolanamente arrotondato:
1E-10 ; -5E-07; 0,0005; 0,2554; 1522,5
*Nota: nella formula REGR.LIN l' uso di valori superiori a 1010 provoca errore; non ho trovato la spiegazione. Questo limite potrebbe essere un problema, alla luce del riferimento a "una grossa mole di dati" fatta nel tuo ultimo messaggio.
Ciao
PS: avevi chiesto quale e' il significato della numerazione crescente (1, 2, 3, 4, ...) che ti avevo fatto creare; quelli sono i valori dell' asse X del tuo grafico.