Multiple Condition syntax needed

  • Ok, I'm making a spreadsheet which does simple stuff.


    One of the boxes displays a word based on other conditions.
    Here is the formula so far:


    =IF(AND(D5="B",F5>E5),"Profit","Loss")


    Which says If Cell D5 has the letter "B" in it AND the number in cell F5 is greater than E5, then print "Profit", otherwise print Loss.


    In the same box, I'd like to make another statement which says


    If cell D5 = the letter "S" and F5 is less than E5, then Print "Profit", otherwise print "Loss".


    So the 1st statement will be connected to the 2nd statement with an OR operator or something like that.
    Therefore only one of these conditions is true.


    Any idea how I can code it?

  • That's great. Works fine. Just to take it one step further, How do I display Nothing in the formula box if the D5 cell contains anything other than "B" or "S"?


    At the moment, it will display Loss if anything other than B or S is in cell D5


    Appreciate the help

  • Similar problem,


    I have a formula in a cell:


    =IF(C7="Euro",G7*100000,"")


    How do I change it so that if C7="GBP" then the result would be different and if the result of C7="JPY" or C7="CHF", then again, there would be a different calculation for each condition.


    How do i include the ORs and ANDS in this formula?


    Thanks

  • ok you don't really want to do this with IFs and ORs as your formula will become too cumbersome.


    You're best bet would be to create a little table somewhere (separate sheet perhaps) that lists the currency codes in Col A and the multiplier in Col B


    then use VLOOKUP to do what you want


    so let's say your little list is in Sheet CURRENCY, Curr Code is in A, Multiplier is in B


    instead of


    =IF(C7="Euro",G7*100000,"")


    you would use


    =G7*VLOOKUP(C7,CURRENCY!A:B,2,0)


    so if you change G7 the result will change automatically.

  • the syntax for IFs etc is this


    IF(your test,result if test is true, result if test is false)


    to embed IF's (you can embed up to 7)


    =IF(test1,result if true,IF(test2,result if true,IF(test3,result if true,IF(test4, resultiftrue,IF(test5,result if true,If(test6,result if true,IF(test7,result if true, result if false))))))


    An IF will execute from Left to Right - and STOP as soon as it finds a match... hence you don't need a "result if false" until the very last IF - as the IF will just continue merrily to test 2 if it doesn't find a result for test1 and so on.... if there's no true for result7 then NONE of the IFs test can have been met and so you put your "if false" in there.

  • LASW10,


    That is great. I was wondering how the If statements actually worked.
    Thanks for the heads up.


    As for the formula, I entered it with the new table and it works fine.


    Could you just explain what each part actually means. I have a vague idea but need something more concrete for my understanding.
    Thanks again

  • ok a VLOOKUP is a Vertical Lookup (there is also HLookup which is horizontal look up)


    a VLOOKUP works like


    =VLOOKUP(Value to Lookup,Range to look in,Column in Range to return,exact match / non exact - this is 0 or FALSE (for exact) - 1 or TRUE (for non exact))


    The key with a VLOOKUP is that your criteria must appear in the leftmost column of your range - if it does not the formula will not work.


    To get around this limitation you can instead use the INDEX function with the MATCH function


    So say I had 2 rows of data with 2 columns


    Col A
    row 1 = 1
    row 2 = 2


    Col B
    row 1 = "One"
    row 2 = "Two"


    If I wanted to return the number for "One" I couldn't use a vlookup as my criteria appears in Column B not Column A so I would use INDEX & MATCH


    INDEX syntax...


    =INDEX(range,row,column)


    so


    I want


    =INDEX(A1:B2,don't know row, want Column 1)


    To get the row for the index I would insert a MATCH formula


    MATCH syntax....


    =MATCH(criteria,range,first match (0)/ last match(1))


    this will return the row number of the match in the range.


    so


    =MATCH("One",B1:B2,0) - would return 1 as "One" appears in the first row of the range.


    so my full INDEX / MATCH formula would be


    =INDEX(A1:B2,MATCH("One",B1:B2,0),1)


    this would return 1
    Hope that helps.

  • ok, another question regarding the above.


    Since I created a table in a separate sheet for the benefit of VLOOKUP,


    I now need to enter a formula in this 'new separate table' which refers to cells in the original sheet. It needs figures from certain cells to do its multiplying.


    How do I reference those specific cells in the original sheet?
    I know I can't use VLOOKUP as the leftmost column is the "DATE" field and that would be useless.


    From the above, I understand that it would HAVE TO be Index and Match. Is this correct? If so, How do I reference the first sheet with the Index and Match?


    Is there any other way to do this without using Index and Match as I'm not too comfortable using it yet.


    Thanks

  • well if the cells are SPECIFIC then you can just reference them can't you?


    ie


    On your currency table sheet


    Col A
    Euro
    Col B
    =sheetname!specificell ....


    No?

  • ok well for future ref - the easiest way to do this is to enter


    =


    in the cell you want the formula to be... then go to the location of the cell you want and click.


    the formula will update with the correct reference automatically.

  • OK, this formula works in the main sheet:


    =((G7/F7)*VLOOKUP(C7,Currency!A:B,2,0)*F7)


    But when I copy it down the column, there is a VALUE# in the rows where nothing has been entered yet. How do I modify it so that nothing shows until there is data in the relevant fields as defined by the formula?

  • take a look at IS functions in XL Help


    ie


    ISNA


    ISERROR(your formula,0,your formula)


    etc...


    this will handle errors for you but are 'expensive' formulas in that they use up space...


    it would be quicker I think to simply test your variables first using the MAGIC IF...


    ie


    =IF(OR(ISBLANK(G7),ISBLANK(F7)),0,((G7/F7)*VLOOKUP(C7,Currency!A:B,2,0)*F7))


    making sense???

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!