somma se totale maggiore di 100

[diego68]

ciao
ho un intervallo B42. B72 celle formattate con orario.
devo contare il totale da b42 a b72 se il totale viene maggiore di 100 ore e mi deve dare il totale sopra le 100 ore.
esempio totale 160 ore mi deve riportare 60
se il totale e' sotto le 100 ore mi deve riportare ovviamente 0
come si fa?
grazie anticipatamente

[gioh66]

Ciao se il file è sempre lo stesso, in una cella formattata come [HH]:MM prova così

Codice: Seleziona tutto

=SE(SOMMA(B42:B72)>100/24;RESTO(SOMMA(B42:B72);100/24);0)

[diego68]

ciao
si e' sempre lo stesso pensavo di avere quasi finito ma mi mancava questa formula perché' avevo fatto un errore
pero' c'e' qualcosa che non funziona.
le ore totali se metto per esempio 10 ore al giorno per 31 giorni fa 299 ore, se ne tolgo 100 sarebbero 160 ore extra giusto?
il risultato con la tua formula mi da 99
come mai? dovrebbero essere 199

[gioh66]

Posta il file. Io usando il tuo, aggiungendo ore, ottengo il risultato.

[diego68]

no ho modificato la risposta scusami mentre mi rispondevi io la modificavo, il risultato mi da 99 e non 199
se metto totali ore fatte 299 cioè' 10 per giorni per 31 giorni e tolgo 100 mi dovrebbe dare 199 per esempio con la formula mi calcola solo 99 extra.
sto lavorando nella colonna Q42

[gioh66]

Ah ok, allora prova così

Codice: Seleziona tutto

=SE(SOMMA(Q42:Q72)>100/24;SOMMA(Q42:Q72)-100/24;0)

io ho messo la formula in Q78.

[diego68]

grazie funziona poi ho questa formula che mi avevi dato
=SOMMA.SE($S$42:$S$72;"C";$Q$42:$Q$72)
ora la vorrei adattare come quella prima ovvero fare il conto delle ore in questa caselle per sede "C" o "S" pero' solo sopra le 100 ore. si può' fare?
grazie

[gioh66]

Buondì ti basta cambiare in quella di prima il SOMMA con la formula con il SOMMA.SE.

[diego68]

ciao
scusa non ho capito

[gioh66]

Così: sostituisci la parte in rosso
=SE(SOMMA(Q42:Q72)>100/24;SOMMA(Q42:Q72)-100/24;0)
con
SOMMA.SE($S$42:$S$72;"C";$Q$42:$Q$72)
e diventa così
=SE(SOMMA.SE($S$42:$S$72;"C";$Q$42:$Q$72)>100/24;SOMMA.SE($S$42:$S$72;"C";$Q$42:$Q$72)-100/24;0)

[diego68]

la formula pero' non e' giusta, nel senso che va benissimo ma non mi fa il calcolo che intendevo io
=SE(SOMMA.SE($S$42:$S$72;"C";$Q$42:$Q$72)>100/24;SOMMA.SE($S$42:$S$72;"C";$Q$42:$Q$72)-100/24;0)
non e' quello che intendevo perché' mi sono spiegato male.
Faccio un esempio migliore.
di base ci sono 100 ore da busta paga.
ne ho fatte totale 248 ad esempio
di cui pero' nelle ore totali ne ho fatte 104 nella Sede "C" e 144 nella sede "S"
come faccio a calcolare le 148 ore extra oltre le 100 ore quindi pero' ripartite per le due sedi dato che in una ne ho fatte 104 e nell'altra 144? i valori chiaramente possono cambiare pero' la base e' sempre 100
perche' la formula che mi hai dato mi fa un conto che mi calcola 144 nella sede "S" e solo 4 nella sede "C"

[diego68]

una domanda
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;D$42:D$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)<6;RESTO(D$42:D$72;1/3)*(D$42:D$72>1/3)*NON(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))*(GIORNO.SETTIMANA($A$42:$A$72;2)<6);D$42:D$72)
calcola sopra le 8 ore gli straordinari e sabato e domenica.
Se voglio gli straordinari sopra le 5 ore può' andare bene mettere al posto di 1/3 il numero 1/4,8?
io ho fatto cosi' ma credo ci sia qualcosa che non va bene
in teoria il conteggio giusto sarebbe considerare 24 ore la settimana quindi a seconda dei mesi le ore mensili lavorative possono essere leggermente differenti.
Io ho messo 25 ore la settimana calcolando 5 ore al giorno perché' mi risulta più' facile ma poi mi sballa di qualche ora sul conteggio totale

[gioh66]

Si va bene. Potresti mettere anche 5/24. Per quanto riguarda invece il discorso delle ore settimanali non ti seguo. Io so fare qualche formula ma non me ne intendo di contabilità

[diego68]

ok non ti preoccupare la uso cosi' che può' andare bene pero' ho una domanda sulla formula precedente che avevo scritto che andava bene ma mi sono sbagliato
la formula pero' non e' giusta, nel senso che va benissimo ma non mi fa il calcolo che intendevo io
=SE(SOMMA.SE($S$42:$S$72;"C";$Q$42:$Q$72)>100/24;SOMMA.SE($S$42:$S$72;"C";$Q$42:$Q$72)-100/24;0)
non e' quello che intendevo perché' mi sono spiegato male.
Faccio un esempio migliore.
di base ci sono 100 ore da busta paga.
ne ho fatte totale 248 ad esempio
di cui pero' nelle ore totali ne ho fatte 104 nella Sede "C" e 144 nella sede "S"
come faccio a calcolare le 148 ore extra oltre le 100 ore quindi pero' ripartite per le due sedi dato che in una ne ho fatte 104 e nell'altra 144? i valori chiaramente possono cambiare pero' la base e' sempre 100
perche' la formula che mi hai dato mi fa un conto che mi calcola 144 nella sede "S" e solo 4 nella sede "C"

[gioh66]

Scusa così mi è difficile capire cosa vorresti puoi allegare il file con un pò di dati e il risultato che vorresti messo a mano?

[diego68]

allora guarda i risultati nelle caselle q93 e q94 li ho scritti a mano in pratica il totale straordinari e' 148 che lo vedi nella casella q88 ma se calcoliamo che il 48% lo ha fatto nella sede "C" ed il 52% nella sede "S" il risultato dovrebbe essere che il 48% di 148 in q93 dovrebbe essere 70 o un numero circa uguale mentre il 52% degli straordinari fatti nella sede "S2 che vedi nella casella q94 dovrebbe essere 78 o un numero circa questo.quindi in sostanza mi deve calcolare degli straordinari fatti ovvero 148 quanti sono stati fatti nella sede S e quanti in C considerando che gli straordinari partono sopra le 100 ore

[gioh66]

Dopo una strizzata di meningi sono arrivato a queste due formule: per gli straordinari della sede C

Codice: Seleziona tutto

=SOMMA.SE(S42:S72;"C";Q42:Q72)-100/24*CONTA.SE(S42:S72;"C")/CONTA.VALORI(S42:S72)

che da 71.36
e per la sede S

Codice: Seleziona tutto

=SOMMA.SE(S42:S72;"S";Q42:Q72)-100/24*CONTA.SE(S42:S72;"S")/CONTA.VALORI(S42:S72)

il cui risultato è
76.23

[diego68]

perche' viene il totale con la virgola?

[gioh66]

perchè ho fatto un calcolo percentuale...ma non va bene vero?
Ps.: comunque non è un totale con la virgola. Sono rispettivamente 71 ore e 36 minuti e 76 ore e 23 minuti.
Questo perchè ho pensato (male??) che le cento ore ordinarie andassero spartite tra le due sedi.

[diego68]

non e' che hai sbagliato e' che nemmeno io so bene come ripartirle perché' per esempio può' capitare che le 100 ore vengano fatte tutte in una sede oppure divise a meta' non c'e' una regola.
In realtà' il conteggio non saprei nemmeno io come farlo.
Diciamo che con la tua formula probabilmente si ottiene un risultato più' o meno corretto, non preciso al 100% secondo me ma almeno da una idea.
In realtà' le ore dovrebbero essere senza virgola quindi se parto da questo presupposto forse c'e' qualcosa che non va.
100 ore ma fatte totale 150 divise 75 in una sede e 75 in una altra il risultato dovrebbe essere 25 fatte da una parte e 25 dall'altra giusto?
se applico la tua formula non viene proprio questo risultato giusto?
in teoria dovrebbe fare cosi'.

[gioh66]

Infatti, sto provando altre strade ma questa è quella che più si avvicina al risultato.
Ps.: toglimi una curiosità: le ore ordinarie oltre le quali si conta lo straordinario sono quelle in azzurro della riga 77? E se si perchè Paolo ha 100 ore invece di 105?

[diego68]

dipende dai contratti c'e' chi ha un contratto a 100 ore chi a 105, ovvero per esempio 5 ore al giorno o 6 ore al giorno etc...le ore sono la riga 77 come indichi tu

[gioh66]

Be se si potesse usare le ore ordinarie, che sono 105 per Paolo, si potrebbero fare le formule così: per la sede "C"

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((Q42:Q72>5/24)*Q42:Q72*(S42:S72="C"))-MATR.SOMMA.PRODOTTO((GIORNO.SETTIMANA(A42:A72;2)<6)*5/24*(S42:S72="C")*NON(VAL.NUMERO(CONFRONTA(A42:A72;AH7:AH16;0))))

e per la sede "S"

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((Q42:Q72>5/24)*Q42:Q72*(S42:S72="S"))-MATR.SOMMA.PRODOTTO((GIORNO.SETTIMANA(A42:A72;2)<6)*5/24*(S42:S72="S")*NON(VAL.NUMERO(CONFRONTA(A42:A72;AH7:AH16;0))))

Ti allego il file dove in V108 e dintorni ho fatto le prove.

[diego68]

sembra funzionare unica cosa e' come mai non si può' usare per 100 ore? ci sono problemi col calcolo giusto?
comunque sembra dividerle bene.

[gioh66]

Diciamo di si. Per arrivare a un risultato verosimile ho fatto contare l'orario ordinario della due sedi (50 per C e 55 per S...vado a memoria) e l'ho sottratto ai totali delle ore, per ottenere lo straordinario.

[diego68]

ok va bene me lo faccio andare bene tanto per avere una idea praticamente e' quasi perfetta.
Volevo fare una formula ma non si se e' fattibile
in L8 ho un totale dato dalla formula I8-F8-E8-H8-AN8-AR8 sarebbe I8 meno tutte le altre celle.
le tiro giu fino a I38 per tutti i giorni del mese in modo che ogni giorno mi calcola questi totali.
Pero' per esempio in O8 ed in O9 una volta al mese devo mettere un importo che vorrei che in automatico me lo calcolasse sulla Casella corrispondente al totale di I...ma solo una tantum
perche' se gia sono arrivato ad I12 per esempio ed ho compilato 3 giorni da I8 a I12 poi compilo in O8 metto 200 vorrei che me lo calcolasse in I13 dato che nel caselle prima erano gia' con importi e non me lo calcolasse poi in I14.
Non so se mi sono spiegato
IO ho provato ma quando lo inserisco la somma me la mette in tutte le caselle da I8 a I38

[gioh66]

Fai una cosa visto che si tratta di un problema diverso apri una nuova discussione e allega un file con un po di dati...anche perchè non ci ho capito una cippa...