IF String

Thomo4321

New Member
Joined
Apr 18, 2019
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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:
[COLOR=#444444][FONT=Calibri]=IF(AND(ISBLANK([Sold]),ISBLANK([Bought])),"",IF(AND([Bought]>0,ISBLANK([Sold])),ABS(TODAY()-[Bought]),IF(AND([Bought]>0,[Sold]>0),ABS([Sold]-[Bought]),"")))
[/FONT][/COLOR]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Sorry,

Should have placed that in the initial post.

ABCDEFGHIJKLMNO
COYBOUGHTSOLDSHARE QTY$/SHARESTOP LOSSS/L CPSCOST INC FEESELL$SELL TOTALDIFFERENCE%FEEDAYS SINCE BUYINGAVG DAILY GAIN/LOSS
ABC15/06/1920/06/19539.55450.188.49526.1011.50609.5083.4016%19.95516.68

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0

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




Sorry, I didnt know how to do this until now.
 
Upvote 0
This table shows the SOLD column both with data and blank.

"keeps bringing up [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] ! error but will work if the Sold cell is blank."

Where is the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] error you speak of, you implied if the Sold cell is NOT blank you get a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] error but there are no visible errors on that spreadsheet.
 
Upvote 0
For whatever reason the error was coming up for one cell but not the rest of them and since then has fixed itself.

Thanks for your time on this
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top