# Dumb Excel Questions **URL:** \ **Category:** Software/Technology\ **Created:** [February 3, 2022, 6:03pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148 "2022-02-03T18:03:06Z")\ **Posts on this page:** 20\ **Page:** 6
**Author:** ![SteveGrondin](https://community.goactuary.com/user_avatar/community.goactuary.com/stevegrondin/32/3257_2.png) [@SteveGrondin](https://community.goactuary.com/u/SteveGrondin)\ **Post date:** [December 15, 2022, 1:06pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/102 "2022-12-15T13:06:42Z")
Pivot table definitely has some advantages. 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. ---
**Author:** ![SteveGrondin](https://community.goactuary.com/user_avatar/community.goactuary.com/stevegrondin/32/3257_2.png) [@SteveGrondin](https://community.goactuary.com/u/SteveGrondin)\ **Post date:** [December 15, 2022, 1:15pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/103 "2022-12-15T13:15:26Z")
Oh, just to be clear, I really appreciate the discussions/suggestions above. I don’t want to seem dismissive at all. ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [December 15, 2022, 4:13pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/104 "2022-12-15T16:13:48Z")
Also, the best answer I would consider the nature of the task. Is it something I will run once and be done? Is it something I will run once a week with updated data? Is it something I want to make to hand off to someone else to run as needed? The answers here dictate how much robustness would put into the ability to replicate the calculations. ---
**Author:** ![ALivelySedative](https://community.goactuary.com/user_avatar/community.goactuary.com/alivelysedative/32/1337_2.png) [@ALivelySedative](https://community.goactuary.com/u/ALivelySedative)\ **Post date:** [December 15, 2022, 4:19pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/105 "2022-12-15T16:19:13Z")
> [@DeepPurple](#): > > Is it something I will run once and be done? This is NEVER true ime. ---
**Author:** ![dr\_t\_non-fan](https://community.goactuary.com/letter_avatar_proxy/v4/letter/d/a8b319/32.png) [@dr\_t\_non-fan](https://community.goactuary.com/u/dr_t_non-fan)\ **Post date:** [December 16, 2022, 12:01am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/106 "2022-12-16T00:01:28Z")
Best answer to SG, imo ---
**Author:** ![Vorian\_Atreides](https://community.goactuary.com/user_avatar/community.goactuary.com/vorian_atreides/32/65623_2.png) [@Vorian\_Atreides](https://community.goactuary.com/u/Vorian_Atreides)\ **Post date:** [December 16, 2022, 3:47am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/107 "2022-12-16T03:47:38Z")
> [@1695814](#): > > I despise pivot tables. > They’re too volatile, imo. > jmo. 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. ---
**Author:** ![Vorian\_Atreides](https://community.goactuary.com/user_avatar/community.goactuary.com/vorian_atreides/32/65623_2.png) [@Vorian\_Atreides](https://community.goactuary.com/u/Vorian_Atreides)\ **Post date:** [December 16, 2022, 3:49am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/108 "2022-12-16T03:49:45Z")
> [@DeepPurple](#): > > “I have this nail thing, it’s a long, narrow piece of metal with a pointy end and an end with a flat head. I need to push the pointy end into a piece of wood, but only up to the flat head. What is the best tool for the job?” ![image](https://community.goactuary.com/uploads/default/original/3X/a/3/a342af41604eb9b39e166db6f11dadf7a5b74d64.jpeg) ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [December 16, 2022, 5:15pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/109 "2022-12-16T17:15:10Z")
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: 1. the user knows right away that it’s pointing towards to just one cell. (this is also why it’s faster) 2. it can generate an error when it points to nothing. (useful info) 3. 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. ---
**Author:** ![Gonzo](https://community.goactuary.com/user_avatar/community.goactuary.com/gonzo/32/834_2.png) [@Gonzo](https://community.goactuary.com/u/Gonzo)\ **Post date:** [December 16, 2022, 6:15pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/110 "2022-12-16T18:15:57Z")
forgot to include XLOOKUP, which is superior to index/match for 1 dimension ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [December 21, 2022, 8:23pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/111 "2022-12-21T20:23:51Z")
> [@DeepPurple](#): > > Is it something I will run once and be done? > [@ALivelySedative](#): > > This is NEVER true ime. That’s funny. It’s true most of the time in my work. Most of the excel stuff/data sorts and manipulations are one and done, never to be returned to. ---
**Author:** ![ALivelySedative](https://community.goactuary.com/user_avatar/community.goactuary.com/alivelysedative/32/1337_2.png) [@ALivelySedative](https://community.goactuary.com/u/ALivelySedative)\ **Post date:** [December 21, 2022, 8:43pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/112 "2022-12-21T20:43:53Z")
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. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [December 27, 2022, 4:59pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/113 "2022-12-27T16:59:21Z")
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)) ---
**Author:** ![UltimateAnyone](https://community.goactuary.com/user_avatar/community.goactuary.com/ultimateanyone/32/265_2.png) [@UltimateAnyone](https://community.goactuary.com/u/UltimateAnyone)\ **Post date:** [December 27, 2022, 5:46pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/114 "2022-12-27T17:46:55Z")
I think “Mod(VALMO -1,3) +1” might work ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [December 27, 2022, 6:08pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/115 "2022-12-27T18:08:52Z")
Yes, indeed. TYVM! ---
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [January 13, 2023, 8:52pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/116 "2023-01-13T20:52:10Z")
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”? ---
**Author:** ![BruteForce](https://community.goactuary.com/user_avatar/community.goactuary.com/bruteforce/32/175_2.png) [@BruteForce](https://community.goactuary.com/u/BruteForce)\ **Post date:** [January 13, 2023, 9:10pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/117 "2023-01-13T21:10:16Z")
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? ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [January 13, 2023, 9:21pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/118 "2023-01-13T21:21:59Z")
I _think_ she’s just _formatting_ the cell which is holding an excel date. Your suggestion should work because it converts the date into text. ---
**Author:** ![BruteForce](https://community.goactuary.com/user_avatar/community.goactuary.com/bruteforce/32/175_2.png) [@BruteForce](https://community.goactuary.com/u/BruteForce)\ **Post date:** [January 13, 2023, 9:25pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/119 "2023-01-13T21:25:09Z")
Ah, I see. Yeah, if you’re just changing the formatting of a cell that won’t show up in the filter. ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [January 14, 2023, 12:11am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/120 "2023-01-14T00:11:26Z")
Here’s my brute force method to check to see the next business day after 2/15/2025 '=DATE(2025,2,15)+1\*(WEEKDAY(DATE(2025,2,15))=1)+2\*(WEEKDAY(DATE(2025,2,15))=7) This returns 2/17/25 ---
**Author:** ![knoath](https://community.goactuary.com/user_avatar/community.goactuary.com/knoath/32/65607_2.png) [@knoath](https://community.goactuary.com/u/knoath)\ **Post date:** [January 14, 2023, 12:15am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/121 "2023-01-14T00:15:03Z")
> [@DeepPurple](#): > > Here’s my brute force method That’s quite different from the BruteForce method. [Previous page](https://community.goactuary.com/t/dumb-excel-questions/5148.md?page=5) [Next page](https://community.goactuary.com/t/dumb-excel-questions/5148.md?page=7)