Results 1 to 8 of 8

Thread: IF String

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF String

    Morning All,

    For some reason i cannot get the below IF string to work, keeps bringing up #Value ! error but will work if the Sold cell is blank.

    Thanks

    Code:
    =IF(AND(ISBLANK([Sold]),ISBLANK([Bought])),"",IF(AND([Bought]>0,ISBLANK([Sold])),ABS(TODAY()-[Bought]),IF(AND([Bought]>0,[Sold]>0),ABS([Sold]-[Bought]),"")))
    

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,963
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: IF String

    I replaced [Bought] and [Sold] with two cell references and the formula works fine. Played around with different values and only way to generate a #VALUE is if
    you have text in Bought or Sold somewhere in your table.

    Check your table columns for Bought or Sold, am sure there is text in there somewhere.

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF String

    Thanks,

    I have tried this, but i forgot to mention in the post that the bought and sold values contain a date, DD/MM/YY

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,963
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: IF String

    You havent said how you checked your table, looking at the data visually is a waste of time, you should run a formula against each date in the table.

    Assuming the table is in column A
    in a blank cell on that row put

    =ISNUMBER(A1)

    (adjust the formula as necessary)

    copy the formula down for each row.
    Then use autofilter to isolate any 0s in the new column.

    0s will indicate the value in the row is NOT a date and wil throw an error in your formula.

    Failing that, the only thing I can think of is to upload the file (you only need the table showing if you have private data) and post a link to the file in this thread so the file can be examined.

  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF String

    Sorry,

    Should have placed that in the initial post.

    A B C D E F G H I J K L M N O
    COY BOUGHT SOLD SHARE QTY $/SHARE STOP LOSS S/L CPS COST INC FEE SELL$ SELL TOTAL DIFFERENCE % FEE DAYS SINCE BUYING AVG DAILY GAIN/LOSS
    ABC 15/06/19 20/06/19 53 9.55 450.18 8.49 526.10 11.50 609.50 83.40 16% 19.95 5 16.68

  6. #6
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,963
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: IF String

    Am not sure why you posted that table since I stated above

    "looking at the data visually is a waste of time"

    In a blank cell put this

    =SUMPRODUCT(--ISNUMBER(B2:O2))

    If it says anything other than 14 then you have text in row whcih could throw up #VALUE errors

    If it says 14 then

    "upload the file (if you have private data then delete it, you only need the table showing so we can check the values), post a link to the file in this thread so the file can be examined.

    Moderators do not like posting links to files but in this instance we are unable to tell if a cell contains text or numbers just by looking at it so failing the above formula it's our only option.

  7. #7
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF String

    ABCDEFGHIJKLMNOP
    1Budget$540 CPS$1.27425Budget Remaining$552.31
    2Coy NameBoughtSoldShare QTY$/ ShareStop LossS/L CPSCost inc feeSell $Sell TotalDifference%FeeDays since buyingAvg Daily gain/lossChangeable
    3ECX3/6/1917/06/19371$1.35$495.76$1.34$520.80$1.355$502.71-$18.10-3%$19.9514-$1.29
    4BOQ18/6/1953$9.55$500.79$9.45$526.10$10.50$556.50$30.406%$19.959$3.38
    5$19.95
    6$19.95
    7$19.95
    8$1,059.21$12.302%

    Sheet1



    Worksheet Formulas
    CellFormula
    E1=ROUNDDOWN(B1/D1,1)
    I1=B1+K8
    F3=IF(ISBLANK([@Bought]),"",(([@[Share QTY]]*[@[$/ Share]])+[Fee])-(([@[Share QTY]]*[@[$/ Share]])*0.05))
    G3=IFERROR([@[Stop Loss]]/[Share QTY],"")
    H3=IFERROR(IF(ISBLANK([@Bought]),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]),)
    F4=IF(ISBLANK([@Bought]),"",(([@[Share QTY]]*[@[$/ Share]])+[Fee])-(([@[Share QTY]]*[@[$/ Share]])*0.05))
    G4=IFERROR([@[Stop Loss]]/[Share QTY],"")
    H4=IFERROR(IF(ISBLANK([@Bought]),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]),)
    F5=IF(ISBLANK([@Bought]),"",(([@[Share QTY]]*[@[$/ Share]])+[Fee])-(([@[Share QTY]]*[@[$/ Share]])*0.05))
    G5=IFERROR([@[Stop Loss]]/[Share QTY],"")
    H5=IFERROR(IF(ISBLANK([@Bought]),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]),)
    F6=IF(ISBLANK([@Bought]),"",(([@[Share QTY]]*[@[$/ Share]])+[Fee])-(([@[Share QTY]]*[@[$/ Share]])*0.05))
    G6=IFERROR([@[Stop Loss]]/[Share QTY],"")
    H6=IFERROR(IF(ISBLANK([@Bought]),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]),)
    F7=IF(ISBLANK([@Bought]),"",(([@[Share QTY]]*[@[$/ Share]])+[Fee])-(([@[Share QTY]]*[@[$/ Share]])*0.05))
    G7=IFERROR([@[Stop Loss]]/[Share QTY],"")
    H7=IFERROR(IF(ISBLANK([@Bought]),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]),)
    J3=IFERROR(IF([Share QTY]*[Sell $]>=17500,([Share QTY]*[@[Sell $]])-(([Share QTY]*[Sell $])*0.11),([Share QTY]*[Sell $])),0)
    K3=IF(ISBLANK([@[Sell Total]]),"",[Sell Total]-[Cost inc fee])
    L3=IFERROR([@Difference]/[@[Cost inc fee]],0)
    M3=IF(AND(ISBLANK([Bought]),[@[Share QTY]]*[@[$/ Share]]>=17500),(([@[Share QTY]]*[@[$/ Share]])*0.11),19.95)
    N3=IF(AND(ISBLANK($C3),ISBLANK($B3)),"",IF(ISBLANK($C3),ABS(TODAY()-$B3),IF(AND($B3>0,$C3>0),ABS($C3-$B3),"")))
    O3=IFERROR([@Difference]/[@[Days since buying]],[@Difference])
    J4=IFERROR(IF([Share QTY]*[Sell $]>=17500,([Share QTY]*[@[Sell $]])-(([Share QTY]*[Sell $])*0.11),([Share QTY]*[Sell $])),0)
    K4=IF(ISBLANK([@[Sell Total]]),"",[Sell Total]-[Cost inc fee])
    L4=IFERROR([@Difference]/[@[Cost inc fee]],0)
    M4=IF(AND(ISBLANK([Bought]),[@[Share QTY]]*[@[$/ Share]]>=17500),(([@[Share QTY]]*[@[$/ Share]])*0.11),19.95)
    N4=IF(AND(ISBLANK($C4),ISBLANK($B4)),"",IF(ISBLANK($C4),ABS(TODAY()-$B4),IF(AND($B4>0,$C4>0),ABS($C4-$B4),"")))
    O4=IFERROR([@Difference]/[@[Days since buying]],[@Difference])
    J5=IFERROR(IF([Share QTY]*[Sell $]>=17500,([Share QTY]*[@[Sell $]])-(([Share QTY]*[Sell $])*0.11),([Share QTY]*[Sell $])),0)
    K5=IF(ISBLANK([@[Sell Total]]),"",[Sell Total]-[Cost inc fee])
    L5=IFERROR([@Difference]/[@[Cost inc fee]],0)
    M5=IF(AND(ISBLANK([Bought]),[@[Share QTY]]*[@[$/ Share]]>=17500),(([@[Share QTY]]*[@[$/ Share]])*0.11),19.95)
    N5=IF(AND(ISBLANK($C5),ISBLANK($B5)),"",IF(ISBLANK($C5),ABS(TODAY()-$B5),IF(AND($B5>0,$C5>0),ABS($C5-$B5),"")))
    O5=IFERROR([@Difference]/[@[Days since buying]],[@Difference])
    J6=IFERROR(IF([Share QTY]*[Sell $]>=17500,([Share QTY]*[@[Sell $]])-(([Share QTY]*[Sell $])*0.11),([Share QTY]*[Sell $])),0)
    K6=IF(ISBLANK([@[Sell Total]]),"",[Sell Total]-[Cost inc fee])
    L6=IFERROR([@Difference]/[@[Cost inc fee]],0)
    M6=IF(AND(ISBLANK([Bought]),[@[Share QTY]]*[@[$/ Share]]>=17500),(([@[Share QTY]]*[@[$/ Share]])*0.11),19.95)
    N6=IF(AND(ISBLANK($C6),ISBLANK($B6)),"",IF(ISBLANK($C6),ABS(TODAY()-$B6),IF(AND($B6>0,$C6>0),ABS($C6-$B6),"")))
    O6=IFERROR([@Difference]/[@[Days since buying]],[@Difference])
    J7=IFERROR(IF([Share QTY]*[Sell $]>=17500,([Share QTY]*[@[Sell $]])-(([Share QTY]*[Sell $])*0.11),([Share QTY]*[Sell $])),0)
    K7=IF(ISBLANK([@[Sell Total]]),"",[Sell Total]-[Cost inc fee])
    L7=IFERROR([@Difference]/[@[Cost inc fee]],0)
    M7=IF(AND(ISBLANK([Bought]),[@[Share QTY]]*[@[$/ Share]]>=17500),(([@[Share QTY]]*[@[$/ Share]])*0.11),19.95)
    N7=IF(AND(ISBLANK($C7),ISBLANK($B7)),"",IF(ISBLANK($C7),ABS(TODAY()-$B7),IF(AND($B7>0,$C7>0),ABS($C7-$B7),"")))
    O7=IFERROR([@Difference]/[@[Days since buying]],[@Difference])
    J8=SUM(J3:J7)
    K8=SUM(K3:K7)
    L8=SUM(L3:L7)





    Sorry, I didnt know how to do this until now.

  8. #8
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,963
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: IF String

    This table shows the SOLD column both with data and blank.

    "keeps bringing up #VALUE ! error but will work if the Sold cell is blank."

    Where is the #VALUE error you speak of, you implied if the Sold cell is NOT blank you get a #VALUE error but there are no visible errors on that spreadsheet.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •