complicated nested IF formulas

[nasser]

hi all ...
I need help in the attached Excel sheet .
we give employees sim cards to use at work
and the company give each employee a limit of calls and SMS
if the user exceeds the limit of the sms or the calls ,the company deduct from him this extra usage .

P.S :
the company always pay the registration charges that is 2 k.d
the company NEVER pay the 3rd party Sms , so the user pay it's full amount even if it's under the limit << and that's what i don't know how to do

thank u all

[DonkeyOte]

You mention 3rd Party SMS Limit - there is no limit as I see it - it seems the value is stand alone - ie if the value does not affect the limit as being applied to H I'm not sure I see the problem.

To me it seems that what you have would work though you could perhaps shorten such that:

M2: =IF(ISNUMBER(C2),SUM(F2,K2,MIN(D2,I2),MIN(E2,H2)),"")

N2: =IF(ISNUMBER(C2),C2-M2,"")

If the above is all missing the point I would suggest you post the expected results for the sample data.

[nasser]

You mention 3rd Party SMS Limit - there is no limit as I see it - it seems the value is stand alone - ie if the value does not affect the limit as being applied to H I'm not sure I see the problem.

To me it seems that what you have would work though you could perhaps shorten such that:

M2: =IF(ISNUMBER(C2),SUM(F2,K2,MIN(D2,I2),MIN(E2,H2)),"")

N2: =IF(ISNUMBER(C2),C2-M2,"")

If the above is all missing the point I would suggest you post the expected results for the sample data.

thank u for ur fast reply
u r so right ... it's working if the calls amount is more than the limit .. But if the calls amount is less than the limit , OR if the usage of the user is just 3rd party ,, that means it will not going to be deduct

and there is no 3rd party sms Limit , because the user have to pay it all

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[royUK]

maybe this addition to DO's formula in N2

=IF((AND(ISNUMBER(C2),M2>0)),C2-M2,C2)

[nasser]

maybe this addition to DO's formula in N2

=IF((AND(ISNUMBER(C2),M2>0)),C2-M2,C2)

thank you Roy , i tried it with some cases and it works well , i'll try it with more cases so if there is any changes i'll let you know ... thank you so much