September 9, 2013 at 04:28:26
Specs: Windows 7
 My company give it's employees monthly mobile allowance but if differ depends on the position so our mobile deduction sheet looks like thatBill Amount limit Deduction what I want to do to let the formula calculate automatically the deduction based on the position for example if the monthly allowance to Engineer position is 150 I want to deduct from the total amount if it comes to 200 only 50 in the deductions columns and if the monthly allowance to another position is 103.5 if it comes to for example 104 shows in the deduction column 0.5 and if it less than the monthly allowance don't deduct anything.

See More: Question about if and

#1
September 9, 2013 at 06:21:04
 May I suggest that you toss in a few punctuation marks in your posts to make them easier to read? Your post is essentially one long sentence and it's difficult to extract the information required to offer a suggestion. It's hard to tell from your post where the "monthly allowance" for the various positions comes from. In addition, I don't see where the "Position" of the employee is determined.Based on how many different postions/allowances there are, the solution might be an IF function or it might be more efficient to include a VLOOKUP table.Please clarify how the postion/allowance values are determined. If you want to post some example data, please click on the following line and read the instructions found via that link.

Report •

#2
September 10, 2013 at 03:50:47
 Example:Position Bill Amount Limit DeductionEngineer 200 150 50 (200-150)Engineer 150 150 0Engineer 100 150 0Accountant 150 103.5 46.5 (150-103.5)Manager 2000 (no limit) 0and so on...That's what I want to do automatic formula calculate the deductions based on the positions.message edited by marlo.wagdy

Report •

#3
September 10, 2013 at 04:28:43
 By the way I tried this formula:=IF(AND(D3=1,F3=103.5),F3,IF(AND(D3=2,F3=150),F3,IF(AND(D3=4,F3=500),F3,IF(AND(D3=1,F3>103.5),F3-103.5,IF(AND(D3=1,F3<103.5),F3,IF(AND(D3=2,F3>150),150-F3))))))Note: 1,2 and 4 symbols of the positionsbut when I add new another IF(AND() condition like that:=IF(AND(D3=1,F3=103.5),F3,IF(AND(D3=2,F3=150),F3,IF(AND(D3=4,F3=500),F3,IF(AND(D3=1,F3>103.5),F3-103.5,IF(AND(D3=1,F3<103.5),F3,IF(AND(D3=2,F3>150),150-F3),IF(AND(D3=2,F3<150),F3)))))It give me error "You have entered too many arguments for this function.message edited by marlo.wagdy

Report •

Related Solutions

#4
September 10, 2013 at 06:04:59
 Since you did not follow the instructions on how to post example data in this forum, it's hard for me to decipher your formula. As I suggested, if you click on the blue line at the bottom of this post, you will find instructions on how to post data in a format that we can understand. By including column letters and row numbers, we can relate your formula to your data.I don't know what you are doing with "D3". Since I don't see any 1's or 2's in your example data, I don't know what you are doing with your AND functions, e.g. AND(D3=2,F3=150).I also don't understand what you mean by Note: 1,2 and 4 symbols of the positionsmessage edited by DerbyDad03

Report •