+ Reply to Thread
Results 1 to 9 of 9

Need help with an embedded IF statement

  1. #1
    Lari
    Guest

    Need help with an embedded IF statement

    I need to write an embedded IF statement for a compensation report that would
    do the following:
    Cell A2 will be a value of 1, 2 or 3
    Cell B2 will be a value of N, M or E
    and
    If cell A2 = 1 and cell B2 = N, enter text '0%'
    If cell A2 = 1 and cell B2 = M, enter text 'a-b%'
    If cell A2 = 1 and cell B2 = E, enter text 'c-d%'
    If cell A2 = 2 and cell B2 = N, enter text '0%'
    If cell A2 = 2 and cell B2 = M, enter text 'e-f%'
    If cell A2 = 2 and cell B2 = E, enter text 'g-h%'
    If cell A2 = 3 and cell B2 = N, enter text '0%'
    If cell A2 = 3 and cell B2 = M, enter text 'i-j%'
    If cell A2 = 3 and cell B2 = E, enter text 'k-l%'
    Your help would be much appreciated!

  2. #2
    Forum Contributor
    Join Date
    06-01-2006
    Posts
    324
    =IF(B2="N", "0%", IF(B2="M", IF(A2=1, "a-b%", IF(A2=2, "e-f%", IF(A2=3, "i-j%",""))), IF(B2="E", IF(A2=1, "c-d%", IF(A2=2, "g-h%", IF(A2=3, "k-l%",""))))))
    Google is your best friend!

  3. #3
    Max
    Guest

    Re: Need help with an embedded IF statement

    Try in say, C2, array-entered with CSE*:
    =IF(OR(A2="",B2=""),"",INDEX({"0%";"a-b%";"c-d%";"0%";"e-f%";"g-h%";"0%";"i-j%";"k-l%"},MATCH(1,({1;1;1;2;2;2;3;3;3}=A2)*({"N";"M";"E";"N";"M";"E";"N";"M";"E"}=B2),0)))

    *press CTRL+SHIFT+ENTER to confim the formula
    (instead of just pressing ENTER)
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Lari" wrote:
    > I need to write an embedded IF statement for a compensation report that would
    > do the following:
    > Cell A2 will be a value of 1, 2 or 3
    > Cell B2 will be a value of N, M or E
    > and
    > If cell A2 = 1 and cell B2 = N, enter text '0%'
    > If cell A2 = 1 and cell B2 = M, enter text 'a-b%'
    > If cell A2 = 1 and cell B2 = E, enter text 'c-d%'
    > If cell A2 = 2 and cell B2 = N, enter text '0%'
    > If cell A2 = 2 and cell B2 = M, enter text 'e-f%'
    > If cell A2 = 2 and cell B2 = E, enter text 'g-h%'
    > If cell A2 = 3 and cell B2 = N, enter text '0%'
    > If cell A2 = 3 and cell B2 = M, enter text 'i-j%'
    > If cell A2 = 3 and cell B2 = E, enter text 'k-l%'
    > Your help would be much appreciated!


  4. #4
    Lari
    Guest

    Re: Need help with an embedded IF statement

    Thank you for your reply, although when I tried it on my spreadsheet it gave
    me the correct '0%' for anybody who had a N value in B2 and a blank field for
    all others. Any other advise?

    "Bearacade" wrote:

    >
    > =IF(B2="N", "0%", IF(B2="M", IF(A2=1, "a-b%", IF(A2=2, "e-f%", IF(A2=3,
    > "i-j%",""))), IF(B2="E", IF(A2=1, "c-d%", IF(A2=2, "g-h%", IF(A2=3,
    > "k-l%",""))))))
    >
    >
    > --
    > Bearacade
    >
    >
    > ------------------------------------------------------------------------
    > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
    > View this thread: http://www.excelforum.com/showthread...hreadid=552055
    >
    >


  5. #5
    Lari
    Guest

    Re: Need help with an embedded IF statement

    Thank you for your reply although when I tried it it gave me errors in all
    cells. Any other advise?

    "Max" wrote:

    > Try in say, C2, array-entered with CSE*:
    > =IF(OR(A2="",B2=""),"",INDEX({"0%";"a-b%";"c-d%";"0%";"e-f%";"g-h%";"0%";"i-j%";"k-l%"},MATCH(1,({1;1;1;2;2;2;3;3;3}=A2)*({"N";"M";"E";"N";"M";"E";"N";"M";"E"}=B2),0)))
    >
    > *press CTRL+SHIFT+ENTER to confim the formula
    > (instead of just pressing ENTER)
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Lari" wrote:
    > > I need to write an embedded IF statement for a compensation report that would
    > > do the following:
    > > Cell A2 will be a value of 1, 2 or 3
    > > Cell B2 will be a value of N, M or E
    > > and
    > > If cell A2 = 1 and cell B2 = N, enter text '0%'
    > > If cell A2 = 1 and cell B2 = M, enter text 'a-b%'
    > > If cell A2 = 1 and cell B2 = E, enter text 'c-d%'
    > > If cell A2 = 2 and cell B2 = N, enter text '0%'
    > > If cell A2 = 2 and cell B2 = M, enter text 'e-f%'
    > > If cell A2 = 2 and cell B2 = E, enter text 'g-h%'
    > > If cell A2 = 3 and cell B2 = N, enter text '0%'
    > > If cell A2 = 3 and cell B2 = M, enter text 'i-j%'
    > > If cell A2 = 3 and cell B2 = E, enter text 'k-l%'
    > > Your help would be much appreciated!


  6. #6
    Forum Contributor
    Join Date
    06-01-2006
    Posts
    324
    The formula are checking A2 and B2, is this formula in C2?

    and when you say for all others? where are they in the range?

    A3, B3, C3?

    Quote Originally Posted by Lari
    Thank you for your reply, although when I tried it on my spreadsheet it gave
    me the correct '0%' for anybody who had a N value in B2 and a blank field for
    all others. Any other advise?

    "Bearacade" wrote:

    >
    > =IF(B2="N", "0%", IF(B2="M", IF(A2=1, "a-b%", IF(A2=2, "e-f%", IF(A2=3,
    > "i-j%",""))), IF(B2="E", IF(A2=1, "c-d%", IF(A2=2, "g-h%", IF(A2=3,
    > "k-l%",""))))))
    >
    >
    > --
    > Bearacade
    >
    >
    > ------------------------------------------------------------------------
    > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
    > View this thread: http://www.excelforum.com/showthread...hreadid=552055
    >
    >

  7. #7
    Lari
    Guest

    Re: Need help with an embedded IF statement

    Yes, the formula is in C2. And 'all others' are in cells A3, B3 and C3...and
    on down the spreadsheet of 500 rows.

    "Bearacade" wrote:

    >
    > The formula are checking A2 and B2, is this formula in C2?
    >
    > and when you say for all others? where are they in the range?
    >
    > A3, B3, C3?
    >
    > Lari Wrote:
    > > Thank you for your reply, although when I tried it on my spreadsheet it
    > > gave
    > > me the correct '0%' for anybody who had a N value in B2 and a blank
    > > field for
    > > all others. Any other advise?
    > >
    > > "Bearacade" wrote:
    > >
    > > >
    > > > =IF(B2="N", "0%", IF(B2="M", IF(A2=1, "a-b%", IF(A2=2, "e-f%",

    > > IF(A2=3,
    > > > "i-j%",""))), IF(B2="E", IF(A2=1, "c-d%", IF(A2=2, "g-h%", IF(A2=3,
    > > > "k-l%",""))))))
    > > >
    > > >
    > > > --
    > > > Bearacade
    > > >
    > > >
    > > >

    > > ------------------------------------------------------------------------
    > > > Bearacade's Profile:

    > > http://www.excelforum.com/member.php...o&userid=35016
    > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=552055
    > > >
    > > >

    >
    >
    > --
    > Bearacade
    >
    >
    > ------------------------------------------------------------------------
    > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
    > View this thread: http://www.excelforum.com/showthread...hreadid=552055
    >
    >


  8. #8
    Max
    Guest

    Re: Need help with an embedded IF statement

    "Lari" wrote:
    > Thank you for your reply
    > although when I tried it it gave me errors in all cells.


    Think you might not have confirmed the array formula properly over there ..

    Here's a quick sample implementation to illustrate:
    http://cjoint.com/?grdMGMjkht
    Lari_wks.xls

    Place the formula below into C2's formula bar,
    then *array-enter* the formula
    by pressing CTRL+SHIFT+ENTER [CSE]
    (instead of just pressing ENTER):

    =IF(OR(A2="",B2=""),"",INDEX({"0%";"a-b%";"c-d%";"0%";"e-f%";"g-h%";"0%";"i-j%";"k-l%"},MATCH(1,({1;1;1;2;2;2;3;3;3}=A2)*({"N";"M";"E";"N";"M";"E";"N";"M";"E"}=B2),0)))

    If confirmed correctly, Excel will auto-insert & wrap curly braces: { }
    around the formula. (Do not type these curly braces into the formula itself!)

    Note that array-entering using CSE has to be re-done should the formula be
    edited subsequently.

    With the formula in C2 correctly array-entered, just copy C2 down to return
    correspondingly as required for other pairs of values in A3:B3, A4:B4, etc
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

  9. #9
    Max
    Guest

    Re: Need help with an embedded IF statement

    > Here's a quick sample implementation to illustrate:
    > http://cjoint.com/?grdMGMjkht
    > Lari_wks.xls


    Just detected, sorry ..
    Pl note the above sample was inadvertently saved in manual calc mode.
    Before testing it out, change it back to auto calc mode via clicking:
    Tools > Options > Calculation tab > Check Automatic > OK

    (otherwise nothing will appear to happen <g>)
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

+ 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