IF String

[Thomo4321]

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

[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]

 

[Special-K99]

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.

 

[Thomo4321]

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

 

[Special-K99]

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.

 

[Thomo4321]

Sorry,

Should have placed that in the initial post.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]COY[/TD]
[TD]BOUGHT[/TD]
[TD]SOLD[/TD]
[TD]SHARE QTY[/TD]
[TD]$/SHARE[/TD]
[TD]STOP LOSS[/TD]
[TD]S/L CPS[/TD]
[TD]COST INC FEE[/TD]
[TD]SELL$[/TD]
[TD]SELL TOTAL[/TD]
[TD]DIFFERENCE[/TD]
[TD]%[/TD]
[TD]FEE[/TD]
[TD]DAYS SINCE BUYING[/TD]
[TD]AVG DAILY GAIN/LOSS[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]15/06/19[/TD]
[TD]20/06/19[/TD]
[TD]53[/TD]
[TD]9.55[/TD]
[TD]450.18[/TD]
[TD]8.49[/TD]
[TD]526.10[/TD]
[TD]11.50[/TD]
[TD]609.50[/TD]
[TD]83.40[/TD]
[TD]16%[/TD]
[TD]19.95[/TD]
[TD]5[/TD]
[TD]16.68[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

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.

 

[Thomo4321]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Budget	$540	CPS	$1.27	425		Budget Remaining		$552.31
2	Coy Name	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	Changeable
3	ECX	3/6/19	17/06/19	371	$1.35	$495.76	$1.34	$520.80	$1.355	$502.71	-$18.10	-3%	$19.95	14	-$1.29
4	BOQ	18/6/19		53	$9.55	$500.79	$9.45	$526.10	$10.50	$556.50	$30.40	6%	$19.95	9	$3.38
5													$19.95
6													$19.95
7													$19.95
8										$1,059.21	$12.30	2%
Sheet1

Cell Formulas
Range	Formula
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.

 

[Special-K99]

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.

 

[Thomo4321]

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