+ Reply to Thread
Results 1 to 6 of 6

complicated nested IF formulas

  1. #1
    Forum Contributor
    Join Date
    12-29-2006
    Location
    Kuwait
    MS-Off Ver
    2010-2013
    Posts
    210

    Red face 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
    Attached Files Attached Files
    Last edited by nasser; 04-12-2010 at 01:13 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #3
    Forum Contributor
    Join Date
    12-29-2006
    Location
    Kuwait
    MS-Off Ver
    2010-2013
    Posts
    210

    re: complicated nested IF formulas

    Quote Originally Posted by DonkeyOte View Post
    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. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    re: complicated nested IF formulas

    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.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: complicated nested IF formulas

    maybe this addition to DO's formula in N2

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

  6. #6
    Forum Contributor
    Join Date
    12-29-2006
    Location
    Kuwait
    MS-Off Ver
    2010-2013
    Posts
    210

    Re: complicated nested IF formulas

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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