Formula calcul program plata - Chandoo

Unul din blogurile de excel la care sunt abonata este http://chandoo.org/wp si aseara am primit newsletter cu noua postare de pe acest blog. In aceasta postare cititorii erau provocati sa creeze formula pentru calcularea programului de plata a unui reprezentant de vanzari. (Calculate Payment Schedule Homework)

Si cum era sa ratez asemenea provocare .... am downloadat fisierul pus la dispozitie de autor si m-am pus pe treaba. M-am chinuit cam 20 de minute pe urma am facut o pauza si l-am dat gata. Rezolvarea mea este una care nu foloseste functii prea complexe, un alt utilizator a postat o rezolvare chiar misto :D. Dar toate la timpul lor si s-o luam ca la scoala:


Datele problemei

Dupa cum am spus mai sus, provocarea este crearea formulei care calculeaza programul de plata pentru un reprezentant de vanzari. Dar cum era de asteptat sunt si conditii pentru a-si primi veniturile:

1. Trebuie sa castige cel putin 200 dolari inainte de a fi platit;
2. Trebuie sa existe o diferenta de 7 zile intre platile succesive.

Pentru aceasta problema am primit un tabel care contine urmatoarele coloane: Data (coloana B), Comisiul castigat in aceea zi (coloana C) si  Valoarea platii (coloana D). In rezolvarea problemei puteai introduce inca o coloana ajutatoare (lucru pe care l-am facut).

 Rezolvarea


 Asa cum am spus si mai sus, eu am ales sa ma folosesc de coloana ajutatoare pusa la dispozitie. Coloana am denumit-o Flag (coloana E) si am folosita ca sa numar de plati care s-au efectuat pana la data curenta:

=COUNTIF($D$6:D6,">0")

 In formula de mai sus se numara celulele de pe coloana D (pana in ziua curenta) in care valorile sunt mai mari ca zero.


Pentru a afla programul de plata a comisionalor in functie de cele doua conditii prezentate mai sus am folosit urmatoarea formula:

=IF(SUM($D$5:D5)=0,IF(AND(SUM($C$5:C6)>=$H$23,(B6-$B$6)>=6), SUM($C$5:C6),""),IF(AND((SUM($C$6:C6)-SUM($D5:D$6))>=$H$23,(B6-INDEX($B$5:B6,MATCH(COUNTIF($D$5:D5,">0"),$E$5:E5,0)-1,1))>=6), SUM($C$6:C6)-SUM($D5:D$6),""))

Am ales sa separ prima plata de celelalte pentru ca am gandit-o ca un caz separat. De aceea primul if chiar asta face, astfel folosim al doilea if IF(AND(SUM($C$5:C6)>=$H$23,(B6-$B$6)>=6),SUM($C$5:C6),"") pentru a verifica cele doua conditii prezentate in datele problemei. Daca ambele sunt indeplinite atunci in aceasta celula se va trece suma comisioanelor de la inceputul tabelului pana la data curenta, daca nu se trece nimic. daca suma platitlor efectuate de la inceputul tabelului pana la data anterioara zilei curente este zero.

Pentru cazul in care au fost efectuate plati anterioare, verificam prin al treilea if  IF(AND((SUM($C$6:C6)-SUM($D5:D$6))>=$H$23,(B6-INDEX($B$5:B6,MATCH(COUNTIF($D$5:D5,">0"),$E$5:E5,0)-1,1))>=6), SUM($C$6:C6)-SUM($D5:D$6),"") daca diferenta dintre suma comisioanelor pana in aceea zi si suma platilor efectuate pana in aceea zi este de cel putin 200 si daca diferenta dintre diferenta dintre data curenta şi data in care s-a efectuat plata anterioara este de cel putin 6. Pentru a gasi data platii anterioare am folosit index si coloana ajutatoare E. La fel ca mai sus cand ambele conditii sunt indeplinite se va trece diferenta dintre suma comisioanelor inclusiv ziua curenta si suma platilor efectuate pana la ziua anterioara celei curente. 

Daca doriti sa intelegi mai bine rezolvarea mea puteti sa downloadati fisierul de la urmatorul link: commision-payment-schedule - solved.xlsx.

Daca doriti sa gasiti o noua metoda de rezolvarea puteti sa downloadati fisierul fara rezolvarea din postul de pe chandoo sau de la urmatorul link: Click here to download the homework problem workbook. Daca veniti cu o rezolvarea noua va rog sa impartiti solutia cu ceilalti prin comentariile de la acest post.