XL 2016 Cumul et comparaison par lignes

[chakib24]

Bonjour à tous,

Je souhaite remplir un fichier mois par mois avec un investissement.
Chaque mois peut donner droit à un complément, suivant cette règle :
Sur les premiers 100€ investis sur l'année, on récupère 100%
Sur les deuxièmes 100€ investis, on récupère 70%
Sur les 3èmes 100€ investis, on récupère 30%
0% au delà de 300€ investis sur l'année

En fait chaque mois doit être comparé aux mois suivant (depuis le début de l'année), et tant qu'on a pas dépassé le seuil sur l'année, la règle s'applique, sinon c'est 0 de plus

Merci d'avance de m'aider à trouver l'équitation qui va bien en colonne C du fichier joint.
Pas de VBA svp.
Si proposition en TCD ou autre je suis preneur également.

Merci d'avance pour vos retours

 

[job75]

Bonjour chakib24,

Formule en C2 à copier vers le bas :

=100*(SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2)>=100)+70*(SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2)>=200)+30*(SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2)>=300)-SOMMEPROD((ANNEE(A$1:A1)=ANNEE(A2))*C$1:C1)

Valeur zéro et format nombre personnalisé en A1 et C1.

A+

 

[job75]

Je préfère ce fichier (1) avec la formule en C2 :

=100*(SOMMEPROD((ANNEE(A$1:A2)=ANNEE(A2))*B$1:B2)>=100)+70*(SOMMEPROD((ANNEE(A$1:A2)=ANNEE(A2))*B$1:B2)>=200)+30*(SOMMEPROD((ANNEE(A$1:A2)=ANNEE(A2))*B$1:B2)>=300)-SOMMEPROD((ANNEE(A$1:A1)=ANNEE(A2))*C$1:C1)

Toutes les plages commencent en ligne 1.

 

[chakib24]

Je préfère ce fichier (1) avec la formule en C2 :

=100*(SOMMEPROD((ANNEE(A$1:A2)=ANNEE(A2))*B$1:B2)>=100)+70*(SOMMEPROD((ANNEE(A$1:A2)=ANNEE(A2))*B$1:B2)>=200)+30*(SOMMEPROD((ANNEE(A$1:A2)=ANNEE(A2))*B$1:B2)>=300)-SOMMEPROD((ANNEE(A$1:A1)=ANNEE(A2))*C$1:C1)

Toutes les plages commencent en ligne 1.

Bonjour job75,

Merci pour votre retour.
Cependant, désolé je pense que j'ai mal exprimé le besoin
En fait, jusqu'au 100 premiers, on récupère 100% de la somme
Jusqu'à 200, on récupère 100% sur les premiers 100, et 70% sur la somme comprise entre 100 et 200
Puis 30% sur ce qui est au dessus des 200 et jusqu'au 300.
Et enfin, 0% sur tout ce qui dépasse les 300.

Ci joint un autre expl.

Merci d'avance,
Bonne soirée,

 

[job75]

En effet les solutions précédentes n'étaient pas correctes.

Ce fichier (2) va bien avec la formule en >C2 :

=MIN(100;SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2))+MIN(70;MAX(0;70%*(SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2)-100)))+MIN(30;MAX(0;30%*(SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2)-200)))-SOMMEPROD((ANNEE(A$1:A1)=ANNEE(A2))*C$1:C1)

Bonne nuit.

 

[job75]

Avant d'aller dormir voyez ce fichier (3) avec un tableau structuré.

On ne peut plus utiliser des formats personnalisés dans la ligne des en-têtes.

Il faut donc utiliser une ligne 2 auxiliaire qui doit être masquée.

Nouvelle formule en C2 :

=SI(A2="";0;MIN(100;SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2))+MIN(70;MAX(0;70%*(SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2)-100)))+MIN(30;MAX(0;30%*(SOMMEPROD((ANNEE(A$2:A2)=ANNEE(A2))*B$2:B2)-200)))-SOMMEPROD((ANNEE(A1:A$2)=ANNEE(A2))*C1:C$2))