# complicated nested IF formulas

1. ## complicated nested IF formulas

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

2. ## re: complicated nested IF formulas

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.

3. ## re: complicated nested IF formulas

Originally Posted by 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.
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

4. ## re: complicated nested IF formulas

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.

5. ## Re: complicated nested IF formulas

maybe this addition to DO's formula in N2

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

6. ## Re: complicated nested IF formulas

Originally Posted by royUK
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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1