1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXCEL : Conditional Output

Discussion in 'Business Applications' started by rachelrleonardo, Dec 3, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. rachelrleonardo

    rachelrleonardo Thread Starter

    Joined:
    Dec 3, 2011
    Messages:
    2
    Hi Everyone, I have a question to ask and I am not sure if this is the right Category to post it. :confused:

    I work as a secretary and my boss asked me to do some Excel conditional work and although I have some limited knowledge of basic programming learned in college, I have no idea whatsoever to approach this in Excel. I would really appreciate if you guys would lend me a helping hand :)

    Without further ado, let me explain my dilemma.

    I have an Excel Spreadsheet consisting of a lot of technical data. Now for reasons beyond my understanding, my boss asked me to formulate a unique condition.

    I attached an example of the spreadsheet to hopefully give you an idea of what I mean.

    **[Condition.xls]**
    I created two tables inside the spreadsheet - Original and Example. The Original is the format required ultimately as it is in the orginal spreadsheet and the Example is for explaining the condition required.

    EXAMPLE Table:
    Fields "Total" and "Seniority" are obtained separately.
    "Total x Seniority" is the product of "Total" and "Seniority".
    "Cumulative" is obtained separately.
    "-12.5% of T x S" is 12.5% of "Total x Seniority"
    "25% of T x S" is 25% of "Total x Seniority"

    Now for the Condition,
    If the value of "Cumulative" lies between the lower bound of "-12.5% of T x S" and the upper bound of "25% of T x S", then there are no issues and the CONDITION column says "YES" and the Difference is n/a or 0.

    But if the value of "Cumulative" does not lie between the lower bound of "-12.5% of T x S" and the upper bound of "25% of T x S",
    then the difference between the BOUND that is crossed over and the "Cumulative"
    must be automatically input into the "DIFFERENCE".

    For Example, lets take the third row of the EXAMPLE table.
    Total = 25000 , Seniority = 1; (given)
    Therefore, Total x Seniority = 25000 x 1 = 25000.

    Cumulative = 17000 (given)
    "-12.5% of T x S" = (-0.125) x 25000 = -3125
    "25% of T x S" = (0.25) x 25000 = 6250

    Checking Condition, it is understood that that 17000 does not lie between the lower bound of -3125 and 6250. It has crossed over the upper bound of 6250.
    Hence, the difference is
    Cumulative - (crossed-over Bound)
    = 17000 - 6250
    = 10750

    I have input some more examples into the spreadsheet, hopefully that will make this obscure condition clearer.:confused:

    Now the only fields that are required are the ones in the ORIGINAL table. The rest are just examples. I sincerely hope that it is possible to formulate a solution to calculate and input the DIFFERENCE value automatically. I would really appreciate any help to help me tackle this problem. Thank you for reading this really long post and have a nice day. :)
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,408
    First Name:
    Wayne
    i think this IF statement works

    =IF(D16<(A16*B16*(-0.125)),(D16-(A16*B16*(-0.125))),IF(D16>(A4*B4*0.25),D16-(A4*B4*0.25),0))

    I have attached the spreadsheet and entered the formula in cell F16 , so the output can be compared
    I have used greater than so if the condition = the test than it will be false -
    if you want to say is greater/less or equal to
    then change the
    >
    or
    <
    to
    >=
    <=

    that will apply if it equals the condition and limits
    =IF(D16<=(A16*B16*(-0.125)),(D16-(A16*B16*(-0.125))),IF(D16>=(A4*B4*0.25),D16-(A4*B4*0.25),0))


    so this is doing a nested IF

    IF( test, true , false )
    nest another IF where its false

    IF ( test, true, (IF( test, true, false)) )

    so the first test is to check if the value is less than the lower limit , if it is then in works out the true part - , but if false - then we add another test to see if the value is greater than the higher limit if it is then we do the next true part - if not then it must lie inbetween and so we do the false part - which is zero

    IF(Test for greater than , True , IF(Test for less than , True, false) )

    does that make sense ?

    note - I used the formulas for working out the upper and lower limit direct from your example - so have not checked the arithmetic used to calculate the -12.5% or the +25% -
     

    Attached Files:

  3. rachelrleonardo

    rachelrleonardo Thread Starter

    Joined:
    Dec 3, 2011
    Messages:
    2
    Thank you for your elaborate reply, Etaf :)

    I tested out some examples with my test-file and they seem to work beautifully.
    I had some dumb questions regarding, but I managed to get it right. ;)

    Thanks again and warmest regards.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,408
    First Name:
    Wayne
    your welcome, no such thing as a dumb question ......
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1029457

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice