yeah thats what I was thinking
Good use of the water cooler, if you ask me. I have an Excel question of my own.
I want to use dates as numbers and I’m not sure how to do it.
Specifically, a particular cell might have a value of 12/31/2014 but it’s being displayed in the custom “yyyy” format as 2014. I would like to use the number 2014 in various calculations elsewhere in the workbook. How can I separate out the 2014 numerical value in the date for use elsewhere?
Thanks.
A1 = 12/31/2014
YEAR(A1) will give you 2014
Chad’s problem is an interesting one. In R, I have a function called Tokenize(), that breaks things up by whatever delimiter you want (though it defaults to space).
For this particular problem, I might have a bunch of nested ifs for the last few characters to find where the first space is from the end. If your text is in cell A1 (and doesn’t have trailing spaces):
LastSpace= MIN( IF(LEFT(RIGHT(A1,2),1)=" ",1,4), IF(LEFT(RIGHT(A1,3),1), 2,4), IF(LEFT(RIGHT(A1,4),1),3,4)))
LastSpace tells you where the closest space to the end is. It’s not an elegant solution, but it works.
Then: Ticker = RIGHT(A1,LastSpace)
You could also do it in one ugly line:
Ticker = RIGHT(A1, MIN( IF(LEFT(RIGHT(A1,2),1)=" ",1,4), IF(LEFT(RIGHT(A1,3),1), 2,4), IF(LEFT(RIGHT(A1,4),1),3,4))))
Not super elegant, but fortunately tickers are short.
a1=12/31/2014
=year(a1)
=2014
edit- ha beat me
Actually I have a question too–
say you have a sheet with 10+ columns and 200+ rows, which are populated by identifier in column A (ticker). The data is pulled from various sheets using index match or sumproduct when appropriate.
Let’s say column B is security name, and C is date. Currently I have it sorted by C (date- oldest to newest), but if I sort by security name A-Z, the index match gets messed up.
Well basically I solved it by converting range into a table so that it looks up [this row] instead of Sheet1!a1, but I’m curious- is there another way to solve this hiccup? I heard vlookup doesn’t get messed up but I don’t like vlookup (though if this is true then maybe there is something vlookup can do that index match can’t…)
and ps I love this excel nerdfest
We toyed with the idea of having a separate forum for these kinds of questions, but concluded that they were infrequent enough that we didn’t want to have a zillion separate forums. I’m not fully sure that was the best decision, since these things are often genuinely valuable, and it would be nice not to have to search for them amidst the discussions of armpit shaving, etc.
If you know it’s going to be an exact match, you can use MATCH(date, range, 0), which should be robust to changing the sort order of your dates. If you have used match without the 0, it will assume it’s ascending order or something. The advantage is that it will give you the last date that is earlier or equal to the date you’re searching for, but it will choke if the order is not ascending.
If you put 0 (exact match), it won’t care about what order the things come in, as long as the range contains values that are 1) unique (though I think it will just give the first match that matches in that case), and 2) match your search exactly.
^the problem is (and I might not have explained properly), at first the index match works perfectly (exact match).
The issue arises when I sort the table by another column. Then, all sorts of calculations gets messed up. For example, the equation in C1 now, instead of index(blah blaha1:g30, match( A1 ,blah blah a:a, etc) doesn’t lookup A1 but some other random cell like A30 (maybe it was the row where C1 used to be before it got sorted? Haven’t checked).
Like I said, it’s an easy fix by converting to a table and looking up [this row] instead of A1, but it’s really annoying. I haven’t come across much yet, but there’s limits to tables too, like I can’t group and move sheets with tables (easy fix–save as, delete unneeded sheets. still a limit is a limit).
OK, I see, so the sort made your match formula parameter follow the new sort order (it’s as if sort moved your column as if it was dragged.
I usually solve that by having some numeric row index on the side of the table that doesn’t get sorted, and then you reference that number by to get the reference to stay constant.
Ok that is a great solution. Thanks!
I think that it would be a great idea. WC is flooded with random stuff and trolls so excel questions kind of get drowned by everything else.
The thing is, people don’t feel like posting serious stuff in the WC, so it just doesn’t really work.
Surely, you are right that a dedicated forum would help for searching and future references.
I can promess you that I would be very active on an excel dedicated forum 
Well at least in the asking question part.
^The only downside I can see is that people won’t check an excel (or perhaps more generic finance work question) area as frequently.
Seems like excel based forums handle this well that af wouldn’t need it. But it would be finance excel mostly, which I don’t know if that exists
I personally was in favor of having a section for excel and other tool-type questions (i.e. it could also include R, Python, Matlab, VBA, “where do I find X data,” “how do I do X calculation, but not in an exam context” questions). At the same time, the number of forums had increased a lot, and we didn’t want to balkanize the conversation too much.
Also, there was the thought that if all the useful stuff (Careers, Investment/Macro, and now Tools) were in separate forums, then WC would have nothing to talk about other than things like nofap and the power of oneness and how much to tip strippers and stuff. To some extent (a large extent?), that has happened. But people who want an excel tip have to come here to get it.
The challenge, as jmh530 brought up, is that most people wouldn’t go to that forum unless they had some issue they wanted to ask about, so the people who answer those kinds of questions would either have to have an issue of their own, or just be fortunate enough to look in at the right moment.
On the other hand, the Investments forum is livlier than it has been in a while, so maybe a tools.
We have a board meeting coming up, it sounds like a topic to discuss.
So put it in the Feedback forum if you want this!
What feedback forum?
There actually is a Feedback Forum. I often forget to visit it, because it’s harder to get to. You have to go to Home, and then scroll down to the last forum on the list.
I love mrexcel, I wish something like Mrexcel existed for things other than excel too (mrcooking would be nice)
There might not be a huge need for excel forum here but it might be helpful as people would use excel for similar purposes here and might be able to get ideas off each other.
Or just as a place to complain about how much you hate getting spreadsheets with MERGED CELLS. Please stop.
(I once got a spreadsheet from a co formatted in MAGENTA. It’s not even their corp color. Which made me feel sorry for everyone who works at tmobile)
^I use merged cells in tables for headers all the time. What’s wrong with that?
Header (one row) might be ok. It’s just when you get randomly merged cells in a table and you can’t copy/paste because you get the paste area is not same error.