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))