IF String

Thomo4321

New Member
Joined
Apr 18, 2019
Messages
16
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]
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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

New Member
Joined
Apr 18, 2019
Messages
16
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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

New Member
Joined
Apr 18, 2019
Messages
16
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>
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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

New Member
Joined
Apr 18, 2019
Messages
16
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Budget</td><td style="text-align: right;;">$540 </td><td style=";">CPS</td><td style="text-align: right;;">$1.27</td><td style="text-align: right;;">425</td><td style="text-align: right;;"></td><td style="text-align: center;;">Budget Remaining</td><td style="text-align: center;;"></td><td style="text-align: right;;">$552.31 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Coy Name</td><td style=";">Bought</td><td style=";">Sold</td><td style=";">Share QTY</td><td style=";">$/ Share</td><td style=";">Stop Loss</td><td style=";">S/L CPS</td><td style=";">Cost inc fee</td><td style=";">Sell $</td><td style=";">Sell Total</td><td style=";">Difference</td><td style=";">%</td><td style=";">Fee</td><td style=";">Days since buying</td><td style=";">Avg Daily gain/loss</td><td style="background-color: #92D050;;">Changeable</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="background-color: #92D050;;">ECX</td><td style="text-align: right;background-color: #92D050;;">3/6/19</td><td style="background-color: #92D050;;">17/06/19</td><td style="text-align: right;background-color: #92D050;;">371</td><td style="text-align: right;background-color: #92D050;;">$1.35</td><td style="text-align: right;;">$495.76</td><td style="text-align: right;;">$1.34</td><td style="text-align: right;;">$520.80</td><td style="text-align: right;background-color: #92D050;;">$1.355</td><td style="text-align: right;;">$502.71</td><td style="text-align: right;;">-$18.10</td><td style="text-align: right;;">-3%</td><td style="text-align: right;;">$19.95</td><td style="text-align: right;;">14</td><td style="text-align: right;;">-$1.29</td><td style="text-align: right;background-color: #0D0D0D;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="background-color: #92D050;;">BOQ</td><td style="text-align: right;background-color: #92D050;;">18/6/19</td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;">53</td><td style="text-align: right;background-color: #92D050;;">$9.55</td><td style="text-align: right;;">$500.79</td><td style="text-align: right;;">$9.45</td><td style="text-align: right;;">$526.10</td><td style="text-align: right;background-color: #92D050;;">$10.50</td><td style="text-align: right;;">$556.50</td><td style="text-align: right;;">$30.40</td><td style="text-align: right;;">6%</td><td style="text-align: right;;">$19.95</td><td style="text-align: right;;">9</td><td style="text-align: right;;">$3.38</td><td style="text-align: right;background-color: #0D0D0D;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$19.95</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #0D0D0D;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$19.95</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #0D0D0D;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$19.95</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #0D0D0D;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;;">$1,059.21</td><td style="text-align: right;;">$12.30</td><td style="text-align: right;;">2%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #0D0D0D;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=ROUNDDOWN(<font color="Blue">B1/D1,1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I1</th><td style="text-align:left">=B1+K8</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@Bought]</font>),"",(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)+[Fee]</font>)-(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.05</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">=IFERROR(<font color="Blue">[@[Stop Loss]]/[Share QTY],""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">ISBLANK(<font color="Green">[@Bought]</font>),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F4</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@Bought]</font>),"",(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)+[Fee]</font>)-(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.05</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G4</th><td style="text-align:left">=IFERROR(<font color="Blue">[@[Stop Loss]]/[Share QTY],""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H4</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">ISBLANK(<font color="Green">[@Bought]</font>),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F5</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@Bought]</font>),"",(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)+[Fee]</font>)-(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.05</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G5</th><td style="text-align:left">=IFERROR(<font color="Blue">[@[Stop Loss]]/[Share QTY],""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H5</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">ISBLANK(<font color="Green">[@Bought]</font>),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F6</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@Bought]</font>),"",(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)+[Fee]</font>)-(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.05</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G6</th><td style="text-align:left">=IFERROR(<font color="Blue">[@[Stop Loss]]/[Share QTY],""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H6</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">ISBLANK(<font color="Green">[@Bought]</font>),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F7</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@Bought]</font>),"",(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)+[Fee]</font>)-(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.05</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G7</th><td style="text-align:left">=IFERROR(<font color="Blue">[@[Stop Loss]]/[Share QTY],""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H7</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">ISBLANK(<font color="Green">[@Bought]</font>),“ ”,[@[Share QTY]]*[@[$/ Share]]+[@Fee]</font>),</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J3</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">[Share QTY]*[Sell $]>=17500,(<font color="Green">[Share QTY]*[@[Sell $]]</font>)-(<font color="Green">(<font color="Purple">[Share QTY]*[Sell $]</font>)*0.11</font>),(<font color="Green">[Share QTY]*[Sell $]</font>)</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K3</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@[Sell Total]]</font>),"",[Sell Total]-[Cost inc fee]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L3</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Cost inc fee]],0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M3</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">[Bought]</font>),[@[Share QTY]]*[@[$/ Share]]>=17500</font>),(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.11</font>),19.95</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N3</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">$C3</font>),ISBLANK(<font color="Green">$B3</font>)</font>),"",IF(<font color="Red">ISBLANK(<font color="Green">$C3</font>),ABS(<font color="Green">TODAY(<font color="Purple"></font>)-$B3</font>),IF(<font color="Green">AND(<font color="Purple">$B3>0,$C3>0</font>),ABS(<font color="Purple">$C3-$B3</font>),""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O3</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Days since buying]],[@Difference]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J4</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">[Share QTY]*[Sell $]>=17500,(<font color="Green">[Share QTY]*[@[Sell $]]</font>)-(<font color="Green">(<font color="Purple">[Share QTY]*[Sell $]</font>)*0.11</font>),(<font color="Green">[Share QTY]*[Sell $]</font>)</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K4</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@[Sell Total]]</font>),"",[Sell Total]-[Cost inc fee]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L4</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Cost inc fee]],0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M4</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">[Bought]</font>),[@[Share QTY]]*[@[$/ Share]]>=17500</font>),(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.11</font>),19.95</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N4</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">$C4</font>),ISBLANK(<font color="Green">$B4</font>)</font>),"",IF(<font color="Red">ISBLANK(<font color="Green">$C4</font>),ABS(<font color="Green">TODAY(<font color="Purple"></font>)-$B4</font>),IF(<font color="Green">AND(<font color="Purple">$B4>0,$C4>0</font>),ABS(<font color="Purple">$C4-$B4</font>),""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O4</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Days since buying]],[@Difference]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J5</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">[Share QTY]*[Sell $]>=17500,(<font color="Green">[Share QTY]*[@[Sell $]]</font>)-(<font color="Green">(<font color="Purple">[Share QTY]*[Sell $]</font>)*0.11</font>),(<font color="Green">[Share QTY]*[Sell $]</font>)</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K5</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@[Sell Total]]</font>),"",[Sell Total]-[Cost inc fee]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L5</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Cost inc fee]],0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M5</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">[Bought]</font>),[@[Share QTY]]*[@[$/ Share]]>=17500</font>),(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.11</font>),19.95</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N5</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">$C5</font>),ISBLANK(<font color="Green">$B5</font>)</font>),"",IF(<font color="Red">ISBLANK(<font color="Green">$C5</font>),ABS(<font color="Green">TODAY(<font color="Purple"></font>)-$B5</font>),IF(<font color="Green">AND(<font color="Purple">$B5>0,$C5>0</font>),ABS(<font color="Purple">$C5-$B5</font>),""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O5</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Days since buying]],[@Difference]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J6</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">[Share QTY]*[Sell $]>=17500,(<font color="Green">[Share QTY]*[@[Sell $]]</font>)-(<font color="Green">(<font color="Purple">[Share QTY]*[Sell $]</font>)*0.11</font>),(<font color="Green">[Share QTY]*[Sell $]</font>)</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K6</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@[Sell Total]]</font>),"",[Sell Total]-[Cost inc fee]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L6</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Cost inc fee]],0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M6</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">[Bought]</font>),[@[Share QTY]]*[@[$/ Share]]>=17500</font>),(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.11</font>),19.95</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N6</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">$C6</font>),ISBLANK(<font color="Green">$B6</font>)</font>),"",IF(<font color="Red">ISBLANK(<font color="Green">$C6</font>),ABS(<font color="Green">TODAY(<font color="Purple"></font>)-$B6</font>),IF(<font color="Green">AND(<font color="Purple">$B6>0,$C6>0</font>),ABS(<font color="Purple">$C6-$B6</font>),""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O6</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Days since buying]],[@Difference]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J7</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">[Share QTY]*[Sell $]>=17500,(<font color="Green">[Share QTY]*[@[Sell $]]</font>)-(<font color="Green">(<font color="Purple">[Share QTY]*[Sell $]</font>)*0.11</font>),(<font color="Green">[Share QTY]*[Sell $]</font>)</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K7</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">[@[Sell Total]]</font>),"",[Sell Total]-[Cost inc fee]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L7</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Cost inc fee]],0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M7</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">[Bought]</font>),[@[Share QTY]]*[@[$/ Share]]>=17500</font>),(<font color="Red">(<font color="Green">[@[Share QTY]]*[@[$/ Share]]</font>)*0.11</font>),19.95</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N7</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">ISBLANK(<font color="Green">$C7</font>),ISBLANK(<font color="Green">$B7</font>)</font>),"",IF(<font color="Red">ISBLANK(<font color="Green">$C7</font>),ABS(<font color="Green">TODAY(<font color="Purple"></font>)-$B7</font>),IF(<font color="Green">AND(<font color="Purple">$B7>0,$C7>0</font>),ABS(<font color="Purple">$C7-$B7</font>),""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O7</th><td style="text-align:left">=IFERROR(<font color="Blue">[@Difference]/[@[Days since buying]],[@Difference]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J8</th><td style="text-align:left">=SUM(<font color="Blue">J3:J7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K8</th><td style="text-align:left">=SUM(<font color="Blue">K3:K7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L8</th><td style="text-align:left">=SUM(<font color="Blue">L3:L7</font>)</td></tr></tbody></table></td></tr></table><br />



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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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

New Member
Joined
Apr 18, 2019
Messages
16
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,250
Messages
5,467,543
Members
406,544
Latest member
semoredhawk

This Week's Hot Topics

Top