My company give it's employees monthly mobile allowance but if differ depends on the position so our mobile deduction sheet looks like that

Bill 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.

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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Example:

Position Bill Amount Limit Deduction

Engineer 200 150 50 (200-150)

Engineer 150 150 0

Engineer 100 150 0

Accountant 150 103.5 46.5 (150-103.5)

Manager 2000 (no limit) 0

and so on...That's what I want to do automatic formula calculate the deductions based on the positions.

message edited by marlo.wagdy

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 positions

but 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

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 lineat 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 positions

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History