[Risolto] Range Mensile formula PERCENTILE

[And18]

Ciao a tutti,
iniziando ad usare LibreOffice ho un problema che non riesco minimamente a risolvere.
Vorrei calcolare il PERCENTILE 0,5 di ogni mese, che ha numero di giorni sempre diverso.
Quindi ci vorrebbe una formula che leggesse il range corretto per ogni mese di ogni anno e lo inserisse nella formula del PERCENTILE.
Come fare?
Allego file.

[gioh66]

Ciao. Con una colonna d'appoggio che mi serve per trovare la fine del mese per inserire il dato, costruita con

Codice: Seleziona tutto

=CONFRONTA(FINE.MESE(A5;0);$A$5:$A$1111;-1)

calcolo il percentile con

Codice: Seleziona tutto

=SE(L5>L4;PERCENTILE(SCARTO($A$4;CONFRONTA(FINE.MESE(A5;0);$A$5:$A$1111;-1);1;MATR.SOMMA.PRODOTTO((ANNO($A$5:$A$1111)=ANNO(A5))*(MESE($A$5:$A$1111)=MESE(A5))));0,5);"")

Ti allego file d'esempio con colnna d'appoggio da L5 in giù e formula da J5 a scendere.

[gioh66]

Ti posto un'altra soluzione per non dover fare la colonna d'appoggio. Inserisci una riga sotto le intestazioni della tabella (in giallo nell'allegato) e in A5 metti la formula

Codice: Seleziona tutto

=A6+1

Poi la formula per il percentile, da J6 in giù, diventa così,

Codice: Seleziona tutto

=SE(E(A6<A5;MESE(A5)<>MESE(A6));PERCENTILE(SCARTO($A$5;CONFRONTA(FINE.MESE(A6;0);$A$6:$A$1112;-1);1;MATR.SOMMA.PRODOTTO((ANNO($A$6:$A$1112)=ANNO(A6))*(MESE($A$6:$A$1112)=MESE(A6))));0,5);"")

Ps.: la riga aggiunta si può nascondere.

[And18]

Grazie moltissimo per la tua risposta.
Ho fatto solo una piccola modifica, lasciando la riga vuota come hai giustamente ideato, senza bisogno di aggiungere una data fittizia.

Codice: Seleziona tutto

=SE(MESE(A6)=MESE(A5);"";PERCENTILE(SCARTO($A$5;CONFRONTA(FINE.MESE(A6;0);$A$6:$A$1112;-1);1;MATR.SOMMA.PRODOTTO((ANNO($A$6:$A$1112)=ANNO(A6))*(MESE($A$6:$A$1112)=MESE(A6))));0,5))

Però c'è un piccolo problema: ho fatto una verifica manuale nella colonna I, ma non capisco perché a settembre 2023 i due valori non coincidono. (Evidenziato in giallo)
Lascio allegato.
Nella formula del Confronta non dovrebbe esserci 0, invece di -1?

[gioh66]

Si c'erano incongruenze laddove la data di fine mese non è quella canonica. Per ovviare ho modificato la formula così

Codice: Seleziona tutto

=SE(MESE(A6)=MESE(A5);"";PERCENTILE(SCARTO($A$5;SE.ERRORE(CONFRONTA(FINE.MESE(A6;0);$A$6:$A$1112;0);CONFRONTA(FINE.MESE(A6;0);$A$6:$A$1112;-1)+1);1;MATR.SOMMA.PRODOTTO((ANNO($A$6:$A$1112)=ANNO(A6))*(MESE($A$6:$A$1112)=MESE(A6))));0,5))

Per quanto riguarda l'opzione 0 del CONFRONTA, si usa quando si cerca una corrispondenza esatta. Se come nel tuo caso alcuni mesi finiscono in una data antecedente alla fine del mese si usa l'opzione 1 con un elenco ordinato crescente o -1 con uno decrescente, per trovare il valore più prossimo per difetto.
Come vedi nella formula ho corretto l'errore facendo in modo che lavori il primo confronta se c'è una corrispondenza esatta, altrimenti lavora il secondo, quello con l'opzione -1, con l'aggiunta di 1 per correggere quanto ti dicevo sopra.

[And18]

Ti ringrazio veramente moltissimo per il tuo incredibile aiuto!

Posso chiederti di spiegare la formula, perché non riesco ad arrivarci.
La funzione PERCENTILE ha bisogno di un range, ma non ho capito come questa tua formula riesca a creare il range voluto.
La funzione SCARTO restituisce il valore di una cella, non la sua posizione. Non capisco come venga usata.

[gioh66]

Buondì. La funzione scarto si compone di cinque argomenti: riferimento, righe, colonne, altezza e larghezza.
Grazie a questi due ultimi argomenti (facoltativi ma fondamentali) puoi indicare un range di quante righe e/o colonne deve essere composto.
Con questa formula

Codice: Seleziona tutto

CONFRONTA(FINE.MESE(A6;0);$A$6:$A$1112;0) 

trova la riga da cui iniziare a contare il range che viene determinato dalla formula

Codice: Seleziona tutto

MATR.SOMMA.PRODOTTO((ANNO($A$6:$A$1112)=ANNO(A6))*(MESE($A$6:$A$1112)=MESE(A6)))

che indica l'altezza del range contando quante celle corrispondono ai criteri della formula.

[And18]

Fantastico!
Ti ringrazio moltissimo per la tua spiegazione così accurata, sono sicuro che servirà a molti utenti.

Ancora grazie!

[gioh66]

Di nulla. Grazie a te del riscontro.