Condividi:        

Media ponderata sui giorni

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Media ponderata sui giorni

Postdi xilofono » 26/12/22 16:28

Carissimi

io sono riuscito a farlo solo con formule "sporchissime" data la mia cattiva tecnica, lunghe e macchinose, che vi posto in spoiler ma vi avviso che è solo per stomaci forti :D

quello che vorrei ottenere di per sè è semplice...

avendo io in colonna A una serie di anni (2021 ; 2022 ; 2023 e così via) e in colonna B associato ad ogni anno un indice (5% ; -0,5% ; 8,7% e così via)

in colonna C e D ho una serie di date, che per ogni riga corrispondono a l'inizio (colonna C) e la fine (colonna D) di un intervallo da prendere in considerazione, che può variare da pochi giorni all'interno di uno stesso anno, a date distanti tra loro per diversi anni

essenzialmente, in colonna E per ogni intervallo esce una media ponderata basata sugli indici annui

per fare alcuni esempi

02/02/2021 - 25/12/2021 e l'indice dell'anno 2021 tenendo conto degli esempi dati sopra è il 5% , avremo come risultato 4,47% (326/365 di 5%)

05/10/2021 - 04/04/2023 bisognerà ponderare in base ai giorni effettivi di ogni anno (87*5%+365*-0,5%+93*8,7%)/545 = 1,95%


io ho fatto questo obbrobio ma è chiaro che è roba da cestinare, peraltro non funziona se le due date non hanno un anno intero in mezzo (le colonne sono diverse dall'esempio di sopra: anni e indici sono da AB3 a AC21, le date inizio e fine da C4 a D14)

https://we.tl/t-s46zfxAQwe

=((((MEDIA.PIÙ.SE(AC$3:AC$21;AB$3:AB$21;">"&ANNO(C4);AB$3:AB$21;"<"&ANNO(D4)))*((((ANNO(D4)-ANNO(C4)-1)*365+DATA(ANNO(C4);12;31)-(C4)+(D4)-DATA(ANNO(D4);1;1))-(((D4)-DATA(ANNO(D4);1;1))+((DATA(ANNO(C4);12;31)-(C4))))))+(DATA(ANNO(C4);12;31)-(C4))*(CERCA.VERT(ANNO(C4);AB$3:AC$21;2;FALSO))+((D4)-DATA(ANNO(D4);1;1))*(CERCA.VERT(ANNO(D4);AB$3:AC$21;2;FALSO)))/((ANNO(D4)-ANNO(C4)-1)*365+DATA(ANNO(C4);12;31)-(C4)+(D4)-DATA(ANNO(D4);1;1))))
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Sponsor
 

Re: Media ponderata sui giorni

Postdi Anthony47 » 26/12/22 19:42

Mah, non so se restituisce quel che cerchi, prova in G3:
Codice: Seleziona tutto
=SOMMA(CERCA.VERT(ANNO(RIF.RIGA(INDIRETTO(E3&":"&(F3-1))));$B$3:$C$15;2))/(F3-E3)

Fai sapere...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Media ponderata sui giorni

Postdi xilofono » 26/12/22 21:47

purtroppo no :cry:

laddove non c'era errore, il risultato sperato è effettivamente corretto nel foglio che ho allegato, seppure con delle concatenazioni che sicuramente sono inutili... e che purtroppo danno errore se le due date non comprendono almeno tre anni solari differenti, quindi escludendo i brevi intervalli

ma forse non sono stato in grado di spiegare il calcolo

essenzialmente se ho un intervallo di date, la fomula mira semplicemente a calcolare i "ratei" in base alle frazioni di anno

posti come punti di riferimento ad esempio

2021 5%
2022 -0,5%
2023 8,7%


se io ho un intervallo di date 05/10/2021 - 04/04/2023

sono 87 giorni del 2021 (indice 5%), 365 del 2022 (indice -0,5%) e 93 del 2023 (indice 8,7%), per un tot di 545 giorni
rispettivamente 0,238 d'anno, 1 anno intero e 0,255 d'anno, per un totale di 1,493 anni

quindi (0,238*5%+1*-0,5%+0,258*8,7%)/1,493
risultato atteso 1,9479%


se avessi invece un intervallo 10/12/2021 - 15/04/2022

sono 21 giorni 2021 (indice 55) e 104 del 2022 (indice -0,5%), per un totale di 125
rispettivamente 0,057 e 0,285 d'anno, per un totale di 0,342 d'anno

quindi (0,057*5%+0,285*-0,5%)/0,342
risultato atteso 0,4167%

in caso di intervallo 20/03/2023 - 30/06/2023
abbiamo 102 giorni del 2023 (indice 8,7%)
pari a 0,279 d'anno

dunque 0,279*8,7%
risultato atteso 2,4312%
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Media ponderata sui giorni

Postdi Anthony47 » 26/12/22 23:43

Ho dimenticato di scrivere che la formula va confermata con la combinazione Contr-Maiusc-Enter:
-si copia la formula nella barra della formula, si conferma con la combinazione dei tasti Contr-Maiusc-Enter, non il solo Enter

:oops:
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Media ponderata sui giorni

Postdi xilofono » 27/12/22 06:43

:D
sì avevo provato anche a metterla come matriarcale

i risultati che ottengo sono nella colonna H a fianco all'originaria formula :(

https://we.tl/t-nQ7E201NHH
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Media ponderata sui giorni

Postdi Anthony47 » 27/12/22 15:53

Niente, non sono riuscito a far funzionare la formula sulle versioni piu' vecchie della mia.
Questa sembra invece funzionare:
Codice: Seleziona tutto
=(CERCA.VERT(ANNO(E3);$B$3:$C$15;2;0)*(MIN(DATA(ANNO(E3);12;31);F3)-E3+1)+SE(ANNO(F3)>ANNO(E3);CERCA.VERT(ANNO(F3);$B$3:$C$15;2;0)*(F3+1-DATA(ANNO(F3);1;1));0)+SE(ANNO(F3)-ANNO(E3)>1;SOMMA(SCARTO($B$3;CONFRONTA(ANNO(E3)+1;$B$3:$B$15;0)-1;1;ANNO(F3)-ANNO(E3)-1;1));0)*365)/(F3-E3+1)
Da inserire normalmente in G3 e poi copiare verso il basso

Ri-ri-prova...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Media ponderata sui giorni

Postdi xilofono » 27/12/22 21:13

Anthony47 ha scritto:Niente, non sono riuscito a far funzionare la formula sulle versioni piu' vecchie della mia.
Questa sembra invece funzionare:
Codice: Seleziona tutto
==(CERCA.VERT(ANNO(E3);$B$3:$C$15;2;0)*(MIN(DATA(ANNO(E3);12;31);F3)-E3+1)+SE(ANNO(F3)>ANNO(E3);CERCA.VERT(ANNO(F3);$B$3:$C$15;2;0)*(F3+1-DATA(ANNO(F3);1;1));0)+SE(ANNO(F3)-ANNO(E3)>1;SOMMA(SCARTO($B$3;CONFRONTA(ANNO(E3)+1;$B$3:$B$15;0)-1;1;ANNO(F3)-ANNO(E3)-1;1));0)*365)/(F3-E3+1)
Da inserire normalmente in G3 e poi copiare verso il basso

Ri-ri-prova...

ecco dove stava l'inghippo, la mia versione di excel
ovviamente funziona alla perfezione, era imposssibile che Anthony potesse toppare
ti ringrazio :oops:
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Media ponderata sui giorni

Postdi xilofono » 27/12/22 21:53

mi permetto anzi di aggiungere alla formula una condizionale iniziale, per ridurre l'indice qualora l'intervallo sia compreso in uno stesso anno, viceversa veniva preso in considerazione l'indice intero

=SE(ANNO(E3)=ANNO(F3);((F3-E3+1)/(DATA(ANNO(E3);12;31)-DATA(ANNO(F3);1;1)+1))*CERCA.VERT(ANNO(E3);B$3:C$15;2;FALSO);(CERCA.VERT(ANNO(E3);$B$3:$C$15;2;0)*(MIN(DATA(ANNO(E3);12;31);F3)-E3+1)+SE(ANNO(F3)>ANNO(E3);CERCA.VERT(ANNO(F3);$B$3:$C$15;2;0)*(F3+1-DATA(ANNO(F3);1;1));0)+SE(ANNO(F3)-ANNO(E3)>1;SOMMA(SCARTO($B$3;CONFRONTA(ANNO(E3)+1;$B$3:$B$15;0)-1;1;ANNO(F3)-ANNO(E3)-1;1));0)*365)/(F3-E3+1))
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Media ponderata sui giorni

Postdi Anthony47 » 28/12/22 00:43

Non capisco il perché non dovrebbe, in quel caso, dare come risultato il tasso di quell'anno...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Media ponderata sui giorni

Postdi xilofono » 29/12/22 22:00

il tasso è riferito a un anno solare intero, quindi ogni frazione di anno comporta che si debba calcolare il rateo di quell'anno

immagina di tenere in mano una obbligazione che rende un tasso del 5% annuo, con cedola staccata il 10 febbraio

il tasso si riproporziona alla frazione di anno in cui l'obligazione è in tuo possesso, se vendi l'obbligazione il 28 febbraio, l'acquirente ti paga il valore di mercato dell'obbligazione più il rateo della cedola per 18 giorni... 18/365simi del 5%

questo è il motivo (questo è un esempio, nel caso specifico che intendo prendere in considerazione i tassi possono variare, ma eventuali variazioni decorrono solo dal 1 gennaio successivo, quindi il tasso, pur variabile, è stabile durante l'anno solare)

infatti ho visto che la condizionale aggiunto risolve, rispetto a questa logica, il problema dove data inizio e data fine sono all'interno di uno stesso anno solare, ma non dove, pur essendo l'intervallo inferiore a 365, data inizio e data fine sono in due anni diversi

ho risolto così, aggiungendo una seconda condizione per prendere in considerazione e ridurre il tasso anche per gli inervalli che pur essendo inferiori all'anno, hanno un inizio e una fine in due anni differenti
in caso di bisestili c'è un leggero errore

=SE(ANNO(E19)=ANNO(F19);((F19-E19+1)/(DATA(ANNO(E19);12;31)-DATA(ANNO(F19);1;1)+1))*CERCA.VERT(ANNO(E19);AA$3:AB$21;2;FALSO);SE(E(ANNO(E19)+1=ANNO(F19);(F19-E19+1)<=366);(((DATA(ANNO(E19);12;31)-E19+1)/365)*(CERCA.VERT(ANNO(E19);AA$3:AB$21;2;FALSO))+((F19-DATA(ANNO(F19);1;1)+1)/365)*(CERCA.VERT(ANNO(F19);AA$3:AB$21;2;FALSO)));(CERCA.VERT(ANNO(E19);$AA$3:$AB$21;2;0)*(MIN(DATA(ANNO(E19);12;31);F19)-E19+1)+SE(ANNO(F19)>ANNO(E19);CERCA.VERT(ANNO(F19);$AA$3:$AB$21;2;0)*(F19+1-DATA(ANNO(F19);1;1));0)+SE(ANNO(F19)-ANNO(E19)>1;SOMMA(SCARTO($AA$3;CONFRONTA(ANNO(E19)+1;$AA$3:$AA$21;0)-1;1;ANNO(F19)-ANNO(E19)-1;1));0)*365)/(F19-E19+1)))
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Media ponderata sui giorni

Postdi Anthony47 » 29/12/22 22:56

Ma allora forse a te serve, in G3
Codice: Seleziona tutto
=(1+CERCA.VERT(ANNO(E3);$B$3:$C$15;2;0)*(MIN(DATA(ANNO(E3);12;31);F3)-E3+1)/365)*(1+SE(ANNO(F3)>ANNO(E3);CERCA.VERT(ANNO(F3);$B$3:$C$15;2;0)*(F3+1-DATA(ANNO(F3);1;1))/365;0))*(1+SE(ANNO(F3)-ANNO(E3)>1;SOMMA(SCARTO($B$3;CONFRONTA(ANNO(E3)+1;$B$3:$B$15;0)-1;1;ANNO(F3)-ANNO(E3)-1;1));0))

Che calcola il tasso di rivalutazione
-del primo anno
-dell'ultimo anno (se diverso dal primo)
-degli anni intermedi (se diversi dal primo e dall'ultimo)
(sono i tre fattori della formula che vengono poi moltiplicati tra loro)

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "Media ponderata sui giorni":


Chi c’è in linea

Visitano il forum: Nessuno e 68 ospiti