SOLVED

A problem to solve

%3CLINGO-SUB%20id%3D%22lingo-sub-1361152%22%20slang%3D%22en-US%22%3EA%20problem%20to%20solve%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1361152%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI%20use%20EXCEL%20for%20some%20day-to-day%20jobs%2C%20and%20in%20most%20cases%2C%20I%20can%20solve%20problems%20that%20arise%2C%20but%20finding%20a%20solution%20to%20the%20following%20problem%20is%20beyond%20my%20humble%20abilities%3A%20%3CSTRONG%3Ecombining%20in%20a%20single%20formula%20the%20two%20cases%20described%20below.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20workers%2C%20due%20to%20sick%20leave%2C%20have%20their%20wages%20reduced%20to%20two%20thirds%20that%20will%20be%20paid%20by%20the%20Social%20Security%20system.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIn%20the%20first%20case%3C%2FSTRONG%3E%2C%20whether%20working%2030%20or%2015%20days%2C%20workers%20will%20receive%20exactly%20one%20third%20of%20their%20salary%20without%20changes%2C%20because%20the%20value%20of%20two%20thirds%20of%20their%20total%20salary%20is%20over%20the%20635%20wage%20(minimum%20wage)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2228%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%3CP%3ETotal%20Salary%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%3CP%3EDays%20worked%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2294%22%3E%3CP%3E2%2F3%20Salary%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22246%22%3E%3CP%3EUsed%20Formula%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2228%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EA%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EB%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2294%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EC%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2228%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%3CP%3E1500%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%3CP%3E30%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2294%22%3E%3CP%3E1000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22246%22%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3EC1%3D(A1%2F3*2)%2F30*B1%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2228%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E2%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%3CP%3E1500%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%3CP%3E15%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2294%22%3E%3CP%3E500%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22246%22%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3EC2%3D(A2%2F3*2)%2F30*B2%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIn%20the%20second%20case%3C%2FSTRONG%3E%2C%20whether%20working%2030%20or%2015%20days%2C%20workers%20will%20receive%20a%20635%20wage%2C%20because%20the%20value%20of%20%26nbsp%3Btwo%20thirds%20of%20their%20total%20salary%20is%20less%20than%20the%20635%20wage%20(minimum%20wage)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22598px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22103px%22%3E%3CP%3ETotal%20Salary%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3EDays%20worked%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E2%2F3%20Salary%3C%2FTD%3E%3CTD%20width%3D%22246px%22%3E%3CBR%20%2F%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22246%22%3E%3CP%3EUsed%20Formula%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22103px%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EA%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3EB%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3EC%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22246px%22%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSPAN%3EC4%3DIF(((A4%2F3*2)%2F30*B4)%26lt%3B%3D635%2C635)%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E4%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22103px%22%3E%3CP%3E750%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E30%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E(500)%3C%2FFONT%3E%20635%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22246px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E5%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22103px%22%3E%3CP%3E750%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E15%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E(250)%3C%2FFONT%3E%20635%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22246px%22%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3EC5%3DIF(((A5%2F3*2)%2F30*B5)%26lt%3B%3D635%2C635)%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20a%20solution%20that%20can%20help%20me%20to%20solve%20this%20problem%2C%20it%20will%20be%20extremely%20and%20thankfully%20appreciated.%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1361152%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377173%22%20slang%3D%22en-US%22%3ERe%3A%20A%20problem%20to%20solve%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377173%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F656696%22%20target%3D%22_blank%22%3E%40Joao2295%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20-%20This%20may%20fit%20your%20requirement%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ROUND(A3*0.66666%2C0)%26lt%3B635%2C635%2CROUND(A3*0.66666%2C0)%2F(30%2FB3))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Charla74_1-1589066954506.png%22%20style%3D%22width%3A%20562px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190407i39D3DC0BDD4EC2E0%2Fimage-dimensions%2F562x246%3Fv%3D1.0%22%20width%3D%22562%22%20height%3D%22246%22%20title%3D%22Charla74_1-1589066954506.png%22%20alt%3D%22Charla74_1-1589066954506.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377354%22%20slang%3D%22en-US%22%3ERe%3A%20A%20problem%20to%20solve%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%20color%3D%22%23000000%22%3EAmazing%20solution.%20%3CEM%3EElegant%20and%20effective%3C%2FEM%3E.%26nbsp%3B%3C%2FFONT%3E%3CFONT%20size%3D%224%22%20color%3D%22%23000000%22%3E%3CSPAN%3EI%20would%20never%20think%20of%20that.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%20color%3D%22%23000000%22%3EThank%20you%20very%20much.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%20color%3D%22%23000000%22%3EJoao%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377564%22%20slang%3D%22en-US%22%3ERe%3A%20A%20problem%20to%20solve%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377564%22%20slang%3D%22en-US%22%3EThank%20you%20-%20happy%20to%20help.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello everyone,

I use EXCEL for some day-to-day jobs, and in most cases, I can solve problems that arise, but finding a solution to the following problem is beyond my humble abilities: combining in a single formula the two cases described below.

 

Some workers, due to sick leave, have their wages reduced to two thirds that will be paid by the Social Security system.

 

In the first case, whether working 30 or 15 days, workers will receive exactly one third of their salary without changes, because the value of two thirds of their total salary is over the 635 wage (minimum wage)

 

 

Total Salary

Days worked

2/3 Salary

Used Formula

 

A

B

C

1

1500

30

1000

C1=(A1/3*2)/30*B1

2

1500

15

500

C2=(A2/3*2)/30*B2

 

In the second case, whether working 30 or 15 days, workers will receive a 635 wage, because the value of  two thirds of their total salary is less than the 635 wage (minimum wage)

 

 

Total Salary

Days worked2/3 Salary

Used Formula

 

A

BC

C4=IF(((A4/3*2)/30*B4)<=635,635)

4

750

30

(500) 635

 

5

750

15

(250) 635

C5=IF(((A5/3*2)/30*B5)<=635,635)

 

If you have a solution that can help me to solve this problem, it will be extremely and thankfully appreciated.

Thank you.

3 Replies
Highlighted
Best Response confirmed by Joao2295 (New Contributor)
Solution

@Joao2295 

 

Hi - This may fit your requirement:

 

=IF(ROUND(A3*0.66666,0)<635,635,ROUND(A3*0.66666,0)/(30/B3))

 

Charla74_1-1589066954506.png

 

 

 

 

Highlighted

@Charla74 

Amazing solution. Elegant and effectiveI would never think of that.

Thank you very much.

Joao

Highlighted
Thank you - happy to help.