Posts by opeyemi1

    Re: Nest Lookup With IF AND Functions


    Thanks AAE!
    It's a good thing you asked that question because I just noticed something. I am not comparing the sale date and the purchase date for each row. But before it says "possible" the sale date for that row must be greater than the purchase date for another row that has a d cell that matches d2 and also has a c cell that is a positive quantity. So, row 2 should match rows 47 to 99 (the highlighted rows). How will this formula change?
    Thanks again!


    Re: Nest Lookup With IF AND Functions


    Hello all,


    I have this formula from below:


    =IF(LEN(B2)=0,"",IF(AND(VLOOKUP(A2,$A3:$D$99,4,FALSE)=D2,VLOOKUP(A2,$A3:$C$99,3,FALSE)>0,B2>A2),"Possible",""))


    But I was wondering how I could change it so that it would only give "possible" when all the above is true but only when C2 is negative and the D cell that matches d2 should have positive cell C amount.


    I tried this:
    =IF(LEN(B2)=0,"",IF(C2>0,"",IF(AND(C2<0,VLOOKUP(A2,$A3:$D$99,4,FALSE)=""&D3&"",VLOOKUP(A2,$A3:$C$99,3,FALSE)>0,B2>A2),"Possible","")))


    but it is giving me "possible" even when D2 does not match any other d cell for example


    Please see attached the spreadsheet I have been working on.


    Thanks!

    Re: Lookup And If Statements


    Hi Brian, Sorry I made a mistake on the previous post about not getting the result. when I copied the formula down from F2 to f99, only F3 shows "possible", but both F2 and F3 should have "possible". I also copied F2 and created a new row 4, so the information in F2 is now duplicated also in F4 and so the formula should show "possible" in F2, F3 &F4, but it's still not working. Do you know if it's a problem with my excel spreadsheet? or is the formula not all inclusive? Thanks!

    Re: Lookup And If Statements


    Hi Brian,


    Thanks. I tried the formula you provided on the sample xls that I attached. It only places "possible" on the first cell, but both F2 and F3 should have possible since they meet the criteria. How can I change the formula? Thank you!

    Hello, I posted thought I posted this earlier, but can't find my posting. I hope you can help this. I am not sure if I need a macro or just formulas will do.


    I have a spreadsheet that is by:
    Purchase Date (column A)
    Sales Date(column B)
    Quantity(column C)
    Name (column D)
    Gain/loss (column E)


    The spreadsheet is sorted by name in ascending order and also by quantity in ascending order.


    Assuming row 1 is the heading. If D2 (name)= the same as another D cell and it's quantity i.e. C2 is negative, while the other D cell with the same name as D2 has a positive quantity i.e. corresponding C cell, and if the date in B2 is greater than date in column A of the other cell where it's D cell matches D2 and has a positive quantity, then in I would like "possible" to display in F2.
    I have included an attachment to better illustrate what I have described above, because I am not sure if I am clear enough.


    Is this do-able? Please help. Thanks!

    Hello all,


    I am not sure if a vlookup can do the following.


    I have a spreadsheet in excel with lots of rows. In column A I have some descriptions such as:


    British Pound
    Danish Krone
    Japanese Yen


    I have created another spreadsheet 2 columns that would have something like:
    column A/columnB
    British/Yes
    Danish/No
    Japananese/Yes


    This 2nd spreadsheet can grow and have more lines.
    In column Z of the 1st spreadsheet, I want a formula to look at the second spreadsheet and if any word in the column A of the 1st spreadsheet matches the word in column A of the second spreadsheet, I want it to the value in column B of the second spreadsheet.


    I am not sure how to do this partial matching...


    Thanks!

    Re: Sum Cells Where Corresponding Cells Meet Condition


    Hi Dave, thanks. I am not sure how to use the SUMIf formula.
    The sumproduct suggested by daddylongleg works for the cells I referenced earlier but I am not sure of how to expand it when I have non consecutive cells.
    I would like the formula to add in 1 sum:


    a29+g29 if g29<>0
    a30+g30 if g30<>0
    a35+g35 if g35<>0
    a36+g36 if g36<>0
    a39+g39 if g39<>0
    a40+g40 if g40<>0


    but if g29 = 0, then a29 should not be added to the sum, so also for the other cells.


    Thanks again.

    Re: Conditional Statements


    Quote from ltzhao

    I am a little confused, are you looking to get 1 sum or 3 seperate sums?


    I am looking to get 1 sum.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from daddylonglegs

    Perhaps try


    =SUMPRODUCT(--(B1:B3<>0),A1:A3+B1:B3)


    Thanks so much, this is what I was looking for![hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from daddylonglegs

    Perhaps try


    =SUMPRODUCT(--(B1:B3<>0),A1:A3+B1:B3)


    Thanks so much, this is what I was looking for![hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from daddylonglegs

    Perhaps try


    =SUMPRODUCT(--(B1:B3<>0),A1:A3+B1:B3)


    Just another related question, what if instead of B1:B3 above, I have g29:g30, g35:g36, g39:g40 and the corresponding A cells, how would I change this formula? thanks again!

    hello,


    I think I need an if statement in excel, but not sure how to go about it.


    I have items in A1, A2, A3 and B1, B2, B3


    I would like to sum A1+B1 if B1<>0, A2+B2 if B2<>0, A3+B3 if B3<>0, all in one statement. If B1 = 0, then a1 should not be added to the equation, also if B2 = 0, a2 should not be added to the equation, if B3 = 0, a3 should not be added to the equation.


    Thanks!

    Hello,


    I am trying to put together a formula in excel, here is what I have so far,
    =IF(OR(W4="G",W4="SG"),(U4-AA4),0)


    But I would like to add to this formula such that when W4="SG" and U4>0, then it should return 0.
    I tried doing this: =IF((AND(W2="sg",U2<0)OR(W2="G",W2="SG")),(U2-AA2),0)


    but it's obviously not correct.


    Thanks!

    Hello all,


    I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet.


    For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11


    Is there a faster way to do this? Thanks for your help!

    Re: Date Value Error


    Hello all,


    I tried the =days360(a1,b1), but still getting the #value! error. I also tried +a2-b2, and I got #value! error except in 2 cells that it gave me the correct days. I also tried the datevalue(a2) to convert the cells and I got teh #value! error. Is there a way out of this at all?


    Thanks!

    Hello,


    I have 2 columns of dates and would like to find the difference in 2 dates in column a and b for example. when I use the formula a2-b2 I get #Value! or #Name? in these cells.
    The cells appear to be in format dd/mm/yyyy. I tried formatting them to mm/dd/yyyy, but the dates are not changing at all. I have quite a few of these spreadsheets so it won't be practical to retype each cell individually.


    Thank!

    Re: Iserror Formula


    Worked Perfect! Thanks so much!


    Quote from sweep

    Try this?


    IF(ISERROR(IF(G15<0,(I15+G15),I15)),"",IF(G15<0,(I15+G15),I15))


    Dave

    Hello all,
    I am having a problem with my iserror formula, I think I am missing something,


    Here is my original formula:


    IF(G15<0,(I15+G15),I15)


    But then I get #Value in some cells, I want to be able to get rid of the #Value and so I used this formula:



    IF(ISERROR(G15<0,(I15+G15),I15)),"",((G15<0,(I15+G15),I15)))


    But excel is saying there is an error with the formula, I have been pondering this for a while now, hope you can help.


    Thanks,