I will say that a more general use case that include pivots has some issues in vba automation. I have another worksheet that has multiple connections, which a macro cycles thru to save different versions. It was developed in excel 2016. The move.to office 365 was a problem, since they made changes to pivot tables and pivot caches, so macro didn’t work. Tried to use macro recorder in 365 to capture new vba equivalents to manual pivot actions and nothing showed up (literally no code in recorded macro). Made some guesses, but 365 seems to try to do stuff in the background, and i need that stuff to complete before going to next step in vba. Best advice i had on that problem is “excel is moving to queries and away from connections” this is just a long example highlighting the lack of control and increase of hidden complexity that you trade for ease of use in pivots.
Another suggestion in thread above was to change data layout in source. I do have control of source, but the native format is the three column table, so it would require manipulation in the source similar to what i am doing in excel, and the source environment is generally less flexible in developing the desired format. I’d have to look to make sure.
Alternatively, in the smaller use cases i can restructure subsequent calcs to be strictly columnar instead of a matrix. The final answers are scen by scen, but collecting them from rows that are separated isn’t too difficult. For the larger ones I don’t have that option.
I’ll use a pivot table to play around with a format I’ll like to display data with.
Then I’ll doctor it around to be more robust and useful (like adding in known values for a variable that just happens to not show up in the current data).
But agree that Excel pivot tables are a poor (long-term) solution.
To me you’re juggling 4 priorities. Readability, ease of coding, reliability, and performance.
I’d put performance last, unless you think it will perform poorly. Usually sumifs are just fine. The problem comes when you have a 500k formulas each hitting 500k cells or whatever. And even then it’s probably nbd.
Sumifs – rank high in terms of readability, because you know right away you’re working with a multiple column key. Like you say, there’s some confusing-- since you’re not actually adding anything.
Sumproduct – Similar to but worse than sumifs, imo. One good thing is that the = signs are more clear than the commas, but I don’t think it’s easy to read or debug. Sumproduct can do a lot of things too-- it can hit rows, columns, and whole matrices, And it can do multiplication. Which is awesome, but also confusing for a reader.
Pivot tables – They are very easy to make. Quite fast. Much less reliable. People forget to refresh. The data changes format. People don’t always know how to rebuild them. Minimally I would make sure to include formulas that warn the user if it breaks. (Really this is always a good idea).
Index(match()) can be difficult for some actuaries to read. I like it because:
the user knows right away that it’s pointing towards to just one cell. (this is also why it’s faster)
it can generate an error when it points to nothing. (useful info)
if you are good at excel, you know you can use the formula to see the referent.
But yes, the concatenation is kind of dumb and ugly.
Just yesterday I found that a catastrophe event in 2011 had a homeowners loss from 1988 coded to that event, resulting from an audit of a thing I legally can’t disclose yet. Our current catalog of historical catastrophe events didn’t even start until 6 months after the loss in question. So, yeah. I stand by my comment.
I’m trying to find the month of the quarter – 1, 2, or 3. (E.g., for 11/30, I should get 2.) To do so I’m using the MOD fn. It returns 0, 1, & 2. Instead of a zero, I want a three when the valuation date is at quarter end. (E.g., for 12/31, I want 3.) I could easily come up with some IF THEN statement, but that’s so BASIC. I’d rather have it in just one equation because I think that makes me look smarter.
The internal Excel calculation for MOD is =N - D * INT(N/D) where N is the number you’re dividing & D is the divisor.
There must be a way to manipulate this equation to return N instead of zero when N is evenly divisible by D.
edit: for the time being I’m just going to go with =IF(VALMO/3=INT(VALMO/3),3,MOD(VALMO,3))
So I’m using Excel to make a master calendar of the crap-ton of tasks that Mean Girl used to do.
A lot of it is like “due the 15th of the following month unless that’s a weekend or holiday, then it’s the next business day”.
So for such a task I’ve set up the tasks and I’m using the “Weekday” function to return the day of the week and manually checking to see if there are any Saturdays or Sundays.
The weekday function just returns a number 1-7 and you can format that ddd or dddd to return either the three-digit or full word of the day of week. (I picked dddd for the full word.)
But when I filter it wants to filter on January 1, 1900; January 2, 1900; etc.
How can I change the filter to say “Sunday” instead of “January 1, 1900”?
I don’t understand. If I put dates in one column (starting in A2) and then in B2 I put TEXT(WEEKDAY(A2,1),“dddd”), the filter at the top just shows the day names. What are you doing that’s different?
