Somma fino a 600 secondi e media

Buonasera,
sto realizzando un foglio excel per l'analisi di vari valori di un veicolo.
Allego il foglio excel di riferimento.

Le colonne che voglio realizzare sono le seguenti:
M: conteggio dei secondi fino a che non si arriva a 600 secondi. I secondi sono contati attraverso la colonna C riferita al tempo.
N: calcolo media della velocità fra i diversi intervalli di tempo: 0-600; 600-600; 600-600....

ESEMPIO:

Velocità 2,40 10,30 10,30 10,30 .. 15,80

Secondi 00 34 66 90 .. 600

Media velocità - - - - - MEDIA: (2.40+10.30+10.30+10.30+15.80)/5

arrivati a 600 secondi la somma dei secondi riparte dalla cella successiva

Velocità 12.80 1.70 2.40 .. 10.30

Secondi 00 34 66 .. 600

Media velocità - - - - MEDIA: (12.80+1.70+2.40+7.30+10.30)/4

e così via...
Chi mi aiuta please?

ciao
ho atteso nel rispondere, credendo che qualcun altro avesse capito il quesito, ma l'assenza di risposte mi confermano che il quesito è poco chiaro.

la formula attualmente presente in M è corretta??
non sembra che faccia una somma progressiva.

il valore 600, non si raggiunge in modo esatto, come ci si deve comportare??
dove vuoi riportare le varie medie???

tu scrivi:
"N: calcolo media della velocità fra i diversi intervalli di tempo: 0-600; 600-600; 600-600...."

non capisco!!

infine è una media aritmetica o ponderata?? non ho ben chiaro l'obiettivo.

Magari, qualcun altro ha già chiaro il quesito, io no

Ciao Ninai,
innanzitutto ti ringrazio per la risposta, mi rendo conto che la domanda possa essere poco chiara e per tanto proverò a spiegarmi meglio.

I secondi ora nel file alla colonna M non vengono contati correttamente e in maniera progressiva, quello è solo un risultato errato che sono riuscito ad ottenere io.
In realtà la colonna M deve contare i secondi e una volta arrivati a 600 secondi riprendere il conteggio dalla cella seguente.

ESEMPIO
COLONNA M
riga 57: 600 secondi
riga 58: 0 secondi
riga 58: 1 secondi
riga 59: 2 secondi
....
riga n: 600 secondi
riga n+1: 0 secondi
riga n+2: 1 secondi
..

Questo per quanto riguarda la colonna M.

Per la colonna N invece vorrei che accanto alla cella 600 secondi della colonna vista in precendenza, vi sia la media aritmetica delle velocità.

ESEMPIO
COLONNA M - COLONNA N
riga 57: 600 secondi - media velocità dalla riga 0 alla riga 57
riga 58: 0 secondi
riga 58: 1 secondi
riga 59: 2 secondi
....
riga n: 600 secondi - media velocità dalla riga 58 alla riga n
riga n+1: 0 secondi
riga n+2: 1 secondi
..

ciao
uno dei problemi che ti dicevo è appunto che la somma progressiva di M, non arriva esattamente a 600,
esempio:
se in M4 metti: =SOMMA($C$4:C4) e ricopi in basso
avrai, in M14 : 551 in M15: 668. In questo caso che succede????
devo fare la media dei valori E4:E15 ??? e poi ricominciare la somma da M16????

oppure cosa????

Ciao ninai, sempre che abbia capito qualcosa...
Oltre al riferimento circolare in M1, fare la somma della colonna C mi sembra una cosa errata.
Partendo dal 0:00:05 alla 39 riga c'è 0:10:05 che equivale ad 600 secondi...

Casomai sarà solo la differenza tra le varie righe (partenza la cella C4 e la cella col valore di C4 + 00:10:00)
Pero noto pure le righe 7/8 ed 10/11 ed 19/20 ed 27/28 che sono uguali, che io penso non dovrebbero essere doppie.Se metti a posto la tabella potrei fare un qualcosa

Ho notato che ci sono dati che si riferiscono ad una sola giornata ed ad un solo veicolo.
Per contare sino a 600 secondi si potrebbe usare un somma.se+confronta(usando il rif.riga() moltiplicato per 0:10:00). Il risultato apparirebbe in colonna P nelle celle P1,P2;P3 ecc ecc

Ok in tal caso vi sono 2 osservazioni:
M14 dovrebbe essere 101 perchè siamo ad 1 minuto (60 secondi) e 41 secondi, quindi 551 sarebbe errato.
Nel caso in cui M15 fosse => 600 devo fare la media dei valori E4:E15.

Raffaele hai centrato in pieno..i valori della colonna M che sono uguali vanno trascurati ;)

>>>Raffaele hai centrato in pieno..i valori della colonna M che sono uguali vanno trascurati

Facendo la differenza sui 600 secondi non influiscono, però i dati presenti in colonna "E" sballeranno le medie.
Io credo che devi togliere le righe doppie.

raffaele1953, 15/07/2014 15:58:

---

>>>Raffaele hai centrato in pieno..i valori della colonna M che sono uguali vanno trascurati

Facendo la differenza sui 600 secondi non influiscono, però i dati presenti in colonna "E" sballeranno le medie.
Io credo che devi togliere le righe doppie.

---

Ma se invece il calcolo dei secondi avvenisse in modo naturale non ci sarebbe questo problema.
ESEMPIO:

COLONNA C - COLONNA M
00:00:05 - 05
00:00:05 - 05
00:00:29 - 29
00:00:37 - 37
00:00:37 - 37
..:..:.. - ..
00:10:00 - 600

come vedi i duplicati non incidono il valore della colonna M

Come Ti ho scritto, calcolare i 600 secondi non ci sono problemi
00:00:05
--------
00:10:05 = 600

Il problema che una volta determinato il range (che non si conosce a priori) si dovrà calcolare la media.
Ora se determino che l'area/range da calcolare sarà dalla cella C4 alla cella C39
Tramite un somma ( si somma tutti i valori che ci sono in colonna E dalla riga 4 alla 39) e come sai sommando due volte la stessa velocita "tutte le celle doppie", avremmo i valori delle medie sballati.
I casi sono due, togli le righe doppie oppure bisogna usare un codice VBA

Ps. Io ho interpretato la colonna C come l'orario esatto quando prelevi la velocita del mezzo. Anche con un codice non va tanto bene
Ex riga 35 = 00:09:41, velocità 13,50
Riga 36 = 00:09:41, velocità 25,70
Non capisco la differenza di velocita allo stesso orario.

ciao
Raffaele, io ancora non ho capito bene tutto l'insieme ma se il problema è quello di fare la media considerando il conteggio dei tempi in modo univoco, ti posso suggerire, alla riga 39:
=(C39-$C$4)/MATR.SOMMA.PRODOTTO(1/CONTA.SE($C$4:C39;$C$4:C39))

nella colonna M, per azzerare la somma ogni volta che si arriva a 600, si potrebbe usare in M4:
=C4-$C$4-600/86400*INT((C4-$C$4)*86400/600)*((C4-$C$4)>0)
da trascinare in basso.


Non so se ti è di qualche utilità, a me, ripeto, manca il quadro d'insiene del problema

Ciao ninai, difficile che riesca a spiegarmi in modo corretto.

Forse mi sono perso, "io sicuramente errando" ho inteso il post in un'altro modo
I dati presenti in colonna A rappresentano un giorno intero.
Che io sappia vorrebbe suddividere tutti questi dati per ogni 600 secondi (sarebbe dividere 1 giorno in 144 aree e calcolargli la media nelle varie Aree)
Nb. 144*600 secondi = 86400 secondi = 1 giorno intero.

Ora facendo un piccolo calcolo (00:00:00 e 00:10:00), a parte la riga 4 senza nessun valore. La prima Area sarà dalla riga 4 alla riga 38/39 (sempre che la riga 4 sia inferiore a =00:00:05, perche attualmente i 600 secondi scadono alle 00:10.05 e si dovrebbe calcolare pure questa riga (dopo si ricomincia dalla riga successiva per altri 600 secondi ecc ecc.
Ps. la seconda formula che hai scritto mette in "M5" = 00:00:00 (dovrebbe essere 00:00:05),però va quasi bene. Se eliminava i doppioni avrei trovato un'altro modo* meno elegante della Tua. Adesso credo che possiamo anche determinare (dove sia iniziata l'area di calcolo e dove sia finita. Ora che abbiamo il range, possiamo sommare i valori della colonna E per calcolare la media. Non credo si possa fare perche all'interno delle righe ci sono doppioni.
* Per decifrare quante righe si debba usare, si potrebbe moltiplicare il valore di 00:10:00 in Q1 * rif.riga() e tramite un confronta possiamo dedurre dove inizia e finisce l'Area da calcolare.
Tutto questo può essere fattibile con indicazioni migliori.

Tramite codice l'ho fatto, però nella prima Area, ho trovato una differenza di valori nelle celle E36:E37
Indagando ho scoperto l'arcano. Il codice, quando arrivava alla riga C36 verifica se la cella C37 era uguale.
Siccome è uguale, saltava la riga 36 per eseguire solo la riga 37. Qui la differenza nella media nei calcoli da me fatti manualmente.
Pps. Mi spieghi la prima formula?

L'utente non risponde più, forse avrà trovato una risposta in altri siti?

Ciao Raffaele
sono molto confuso pure io.

riguardo alla mia prima formula, questa, permette di fare una media senza conteggiare i doppioni. pertanto:

=(C39-$C$4)/MATR.SOMMA.PRODOTTO(1/CONTA.SE($C$4:C39;$C$4:C39))

(C39-$C$4)
è la somma dei valori di C
(tale range , è quello che si dovrebbe fare diventare dinamico in base a dove si raggiungono i multipli dei 600 secondi)


MATR.SOMMA.PRODOTTO(1/CONTA.SE($C$4:C39;$C$4:C39))
é un modo per conteggiare i valori univoci in un range.
In pratica, il conta.se() conta ogni numero del range quante volte è presente.
un numero presente una volta, darà 1/1=1
un numero presente 2 volte darà, 1/2+1/2=1
un numero presente 3 volte darà, 1/3+1/3+1/3=1
ecc. ecc.
Perciò ogni valore, nella somma, peserà sempre 1.
la somma di tali rapporti darà appunto quanti numeri univoci ci sono.
anche questi range, si dovrebbero rendere dinamici in base alla logica dei 600 secondi.

Intuisco la soluzione, con lo spunto che mi hai dato col rif.riga() ecc. ecc., ma al momento sono poco efficiente.

Ciao
volendola "sparare" quasi al buio, ho creato una tabella funzionale, nella quale, in una colonna (Q) scagliono i tempi di 600 secondi in 600 secondi (10:05, 20:05;30:05 ecc.)
nell'altra colonna (R) calcolo la media delle velocità, considerando, sia la somma che il conteggio delle velocità in modo univoco, la formula in R è:


Poi, nella colonna N, pesco tali risultati con una ricerca in tale tabella:
in N5:


allego file

raffaele1953, 16/07/2014 18:46:

---

L'utente non risponde più, forse avrà trovato una risposta in altri siti?

---

No no..io sono qui..partecipo passivamente perchè a dire la verità non so usare così bene come voi excel e queste formule per me sono quasi incomprensibili :P

anjx, 17/07/2014 18:12:

---

No no..io sono qui..partecipo passivamente perchè a dire la verità non so usare così bene come voi excel e queste formule per me sono quasi incomprensibili :P

---

d'accordo, però ci devi in qualche modo guidare sugli obiettivi e sull'esattezza o meno delle nostre proposte, se no, sembriamo dei pazzi che spariamo ai fantasmi.

>>>No no..io sono qui..partecipo passivamente perchè a dire la verità non so usare così bene come voi excel
Qualche spiegazione in più potresti darla?

1) perchè non puoi cancellare righe (forse i dati arrivano cosi da qualche macchinetta)?
2)>Ex riga 35 = 00:09:41, velocità 13,50
>>>Riga 36 = 00:09:41, velocità 25,70
>>>Non capisco la differenza di velocita allo stesso orario.

@ninai
>>>infine è una media aritmetica o ponderata??
Onestamente non capisco la frase, comunque belle formule, forse un pò pesanti...
NB. Ho notato che la colonna G e il totale del percorso.
Pertanto nella cella G39=0,89....allora in cella N39 dovrebbe esserci 0,89
Nella cella G60 =1,64...allora in cella N60 dovrebbe esserci 1,64-0,89 =0,75


Nell'allegato foglio1, ho fatto un qualcosina che però differisce dai tuoi dati (gli dai un'occhiata?)
Ho copiato i miei risultati in colonna O del Tuo per visualizzare le differenze

Ciao
Raffaele
questo topic, per me, è nato male, ci capisco sempre meno, adesso mi sono proprio perso, non capisco perchè lavori con le distanze (G) e non con le velocità (anche se le due cose sono fisicamente collegate).
Il concetto di aritmetica o ponderata si riferisce al fatto che se tu esempio: hai una velocità di 10 m/s per 2 secondi ed una velocità 20 m/s per 3 m/s, la velocità media non sarà (10+20)/2=15 m/s ma (10*2+20*3)/(2+3)=18 m/s.
Nel nostro caso, non so se questi centri. ma ormai ho perso il filo

Cerco di spiegare meglio:
le informazioni e il foglio excel vengono generati in automatico, domani cercherò di capire come mai ci sono questi valori duplicati (effettivamente è un problema).

Per quanto riguarda la velocità pensavo che dovesse essere aritmetica ma adesso che mi ci avete fatto pensare deve essere ponderata per avere una stima più veritiera.