I have a check box on my sheet that when checked (true), the formula returns zero.
When left unchecked (false), the formula returns intended results.
Formula example looks like this: IF A1,0,b1xc1). (IF checkboxcell,0,cellxcell).
I would like to track the duration of time that has passed from that row with the checkbox.
Let me try to explain, when the category within the “checked row” has not been listed again for 30 days, there would be a color change or a cell with some text that will let me know. If it has been listed, I would see that as well.
Kind of confusing, get what I mean?
Any help would be greatly appreciated!
I use this sheet to journal stock trading.
If a stock is sold at a loss and bought back within 31 days of sale there are tax implications.
The checkbox allows a calculation for tax requirements. When checked, which would only be on a loss, the outcome would be zero.
When left unchecked it calculates tax required and lists that calc in a cell.
—–
Scott
So if I understand you right, let's say A holds the dates, B holds the stock symbols, and C holds the checkboxes. The rows are sorted by dates.
A12 is 12/24/21, B12 is AAPL, and C13 is checked.
D13 needs to show the number of days since AAPL was last traded.
If B4 and B8 are both AAPL, then you need to know the days elapsed from A8 to A13.
To find the date last traded, use XLOOKUP.
=XLOOKUP(B13,B$2:B12,A$2:A12,0,0,−1)
So the value AAPL in B13 is the searched-for. It will look from the start of column B (B$2) to the row above (B12). It will return the corresponding date in A$2:A12. The -1 at the end means get the last value (row 8 not row 4 in this case).
The $ next to the 2 means to always start at row 2, but move the rest down. So in row 14 it would be:
=XLOOKUP(B14,B$2:B14,A$2:A14,0,0,−1) when you paste it into all of the cells in column D.
So that's a start. Subtract that from the value of A13 to get a duration.
=A13-XLOOKUP(B13,B$2:B12,A$2:A12,0,0,−1)
Now you have a duration.
Now just use conditional highlighting to make the cell red or whatever if the value is greater than 30 days. Or continue to build on that so you have more calculations based on whether it is > 30d.
Gary, thanks for the quick response. Do you do any one-on-one help sessions!!! I think we are headed in the right direction, although, I wish i did a better job explaining. Maybe I can share the sheet with you.
-Scott
Scott: No, sorry, I don't do one-on-one sessions or anything like that.
Gary, would the formula look the same in this scenario?
The row with the checked box would have a cell with an indication that 31 days has not been met since the checked box. Meaning once the box has been checked and the stock is listed again, in let’s say 10 days later, the indication appears in that new row. Only once the “chain” has been broken for at least 31days does indicating cell disappear. The 31 days works off the last listed in the chain.
Scott: If the days since the last row are 10 instead of 31, then it would show 10 as the value. The conditional highlighting would only make the cell red or whatever if the number was > 30. So you would see 10, no highlighting.
Hi Gary,
Now that we have duration being calculated from the last time traded, how would we use a predefined duration, of let’s say 31 days? This would need to check past and future. What I mean by that is I’m looking for a way yo see if the “chain” has been broken. That would be true if it wasn’t listed again for 31 days before or after. Make sense?
Scott: Not sure what you mean. If you just want to see if the duration is greater than 31 days, just test for exactly that.
To be specific its to track whats called "wash sales".
Let's say i have AAPL listed as a trade on 3/25/2022. I'm trying to determine all AAPL listed within 31 before or after. Specifically any losing trades. i figure that can be determined with a simple True or False value. I think where the hiccup is with the original formula may be the -1 at the end.
Scott: I'm sure you can do what you want, but I'm not fully comprehending your whole situation here, since I can't dedicate the time needed like you can. Just keep learning and keep trying things.