User Profile
mathetes
Gold Contributor
Joined Oct 14, 2019
User Widgets
Recent Discussions
Re: Formula Help for Sorting
You are looking for a single formula that will do all that--both sort and then assign? You don't really give enough info for a full understanding here, with just three examples. Are you creating (not just assigning) those departments, such that what you're ending up with is a 1:1 ratio between sorted cells and departments? That would simply be a sort. Anyway, my own approach would be to create "a helper column" which would extract the value of those last two digits from the texts ("00-14" is in fact a text value), doing so with this formula: =VALUE(RIGHT(A4,2)), and then sort on the basis of those extracted values. See the attached. I chose to sort in descending order just to demonstrate that it changed it. If you have something more extensive--e.g., multiple and random lines assigned to individual apartments--then come back with a more complete description.Re: FIXED FUNTION
First, please stop shouting (i.e., don't use ALL CAPS, which is widely regarded as the visual equivalent of shouting); it doesn't make things any more urgent, Second, you are making a very sweeping claim: "All my formulas", although you only show one formula, and your title refers very specifically to the FIXED function, which is pretty non-universal. So maybe slow down, lower your voice just a bit, and be more precise in describing what you're experiencing, as well as providing more examples if indeed it is "all" of your formulas. Third: here's a link to a resource that may point you in the right direction for resolving the error on your own. Fourth: if it's only formulas that involve the FIXED function, it may be that you're using it thinking it returns a numeric value, but in fact it returns a number as text.Re: Question with this forum itself
I could only make the "Start a Discussion" button disappear from its prominent spot toward the top of the screen by shrinking the window of my browser quite significantly. Otherwise, as noted, it is prominently displayed above all the current and recent postings. And you'll notice that the various "Share" buttons obscure nothing when the browser window is larger...... So does that explanation sufficiently plausible for your circumstances?Re: Unable to use group the pivot table with date
You leave so VERY much unstated here that it's unlikely you'll get much in the way of an explanation. In fact, you don't even ask for an explanation; just offer a vague sentence describing the inability to accomplish a task. Could you help us help you by describing more fully the nature of the grouping you desire. It's clear you expect some kind of Pivot Table grouping around a date value, but not precisely what: group by month, by quarter, by year? How extensive is the table of data being summarized in the Pivot Table? I've never encountered difficulty in grouping by month, but I'm always dealing with many transactions (usually financial in nature) in each month. What's the nature of your data table in the first place? Perhaps most helpful to those who might wish to help you: make available a copy of your workbook itself (use OneDrive or GoogleDrive if unable to attach a file here). If the actual file is confidential, then create a meaningful mockup that conceals private/confidential info.Re: Calculating hours using pivot table
Your inquiry has been up for several days without a reply. I can't speak for others with certainty, but from my perspective I suspect that the reason for the lack of replies is that the sample data you do give is far too simple. Far too simple to create a Pivot Table from; so in effect you're asking anybody who might want to offer assistance to create their own more complete set of data. You would help us help you by posting a more representative set of data (still no real names) with multiple teachers, multiple classes, etc. If you're not able to attach an actual Excel workbook to your reply in this forum, put it onto Google Sheets or the equivalent and paste a link here.Re: Simplifying cost calculation using array instead of IF statement
Unless I'm mistaken, after spending a bit of time looking at your formulae, I think you could take good advantage of INDEX and MATCH to zero in on the relevant cells in this table These hyperlinks will point you to a good reference site to learn how INDEX and MATCH work. And again, unless I'm mistaken, you would be able to eliminate the many IFS conditions comparing J4 with the top row to directly zero in on the appropriate column, and then again to identify the appropriate row based on the items down the left of this table. Given how well you've mastered IFS and XLOOKUP in combination I have no doubt that you'll be able to use INDEX and MATCH equally well. One other recommendation, assuming you are working in Excel rather than Google Sheets, you could use (on a Mac) a combination of the Option key and "Return" to format your extensive formulas as I've done with the formula in Q12, below; they're much more readable this way.Re: Conditional Formatting
Let me first say that Conditional Formatting is one of Excel's features where I often find myself doing trial and error through several iterations to get the result I'm looking for. (I just went through it about 45 minutes ago on a spreadsheet of my own.) Conditional Formatting is, I think it's safe to say, one of the less intuitive of Excel's features. That said, I find it hard to even understand what result you're looking for. Your description itself is confusing. It would help a LOT to be able to see the spreadsheet itself, but also to have a clearer description of the desired result. Are you able to attach that file or include a link to it. If the actual contains confidential info, then create a mockup that can be shared.Re: My Percentage of total sales is not 100 - Why?
Metts518 : there could be several different causes. You give no indication of what kind of data you're dealing with, whether one of more of the columns involves some rounding to nearest whole digit or two decimal places or whatever. Rounding, which most of us think of as "rounding up" could be the cause of your issue. I just recently (in another thread in this forum) learned of what's called "Bankers' Rounding," a method to minimize what you may be experiencing.Re: Having Trouble With Macros
It sounds like you're using macros for cosmetic purposes ("changing font colors, adding border, etc"). lf so, may I ask if they're really necessary? It's fun to use macros, fun to have that as part of your Excel toolkit--I'll grant you that--but there are lots of us who avoid them unless totally necessary, believing that such things as colors and borders can be done with "Conditional Formatting" where it helps in highlighting spots that warrant it. And, for that matter, even most advanced calculations and data parsing tasks can be accomplished with Excel functions....
