User Profile
mathetes
Gold Contributor
Joined Oct 14, 2019
User Widgets
Recent Discussions
Re: Time sheet for hours worked
Could I just give you a few words of encouragement, based on (believe it or not) 50 years of working with arrays of data, most of that time with spreadsheets as the primary tool. You don't ever need to "hope" that there's a formula to get from A to B. Quite seriously. A rule of thumb that I developed many years ago, first for myself, then for others who were still learning: IF you find yourself thinking along the lines "Excel should be able to ________" THEN you can be confident that the programmers have indeed made it possible. The challenge is to find the function or functions that make it possible. Related to that rule of thumb is the reality that you do need to find a comprehensive and clear way to describe the situation, thinking of these three stages: input, process, output. Rather than kicking yourself for "not sure how to word everything" think of it as describing, especially describing fully the process aspect. And focus on describing in plain English (no need to try to translate into "computerese" or "Excel functions"; that comes later). Usually, once you've described the input, process, and output--your original description here had the first and last parts of that trio; it just left vague the process itself--it becomes relatively easy to find an Excel solution because the names of the functions themselves are close enough to English descriptions of what they do that you can go down a comprehensive list of functions and start playing with them to see if they get you from A to B. Let me also underscore my use of the word "play" in that last sentence. Give yourself permission to go through trial and error. Play. You won't break anything and you will learn.Re: Time sheet for hours worked
You wrote: Sorry - wasn't sure how to word everything. <snip, snip, snip> I am hoping there is a formula for excel that will give me that exact answer. One of the things I've learned through decades of using Excel (and, going back to the 1970s, many of the other spreadsheet programs that are strewn along the roadside of the computer age) is that "If I think [Excel} should be able to do [X] , it can. I just need to figure out how the wizards behind it made it possible." In other words, you don't ever need to "hope" that there is a formula to do whatever it is that you want it to. There is such a formula; probably two or three. The challenge is to play around with the various tools until you find one of what will undoubtedly be several different ways to get from A to B. I use the word "play" very deliberately. Give yourself permission to dabble, to experience trial and error, to learn from mistakes. That is truly the best way to learn Excel (as it is many other skills). And then there's the aspect of wording--let's say "describing" instead. One key step in the process is to come up with a clear and comprehensive description in English (not computerese) of the task at hand. Generally, once we have a complete and clear description of the task (input, what to do with the input, output desired) the rest falls into place, maybe, as noted above, with a bit of playing around to find which route from A to B is the most efficient. So have at it, my friend. Here's a website I still use frequently to help me find new Excel toys to play with.Re: Time sheet for hours worked
Llamaface06 Let me add to the question Lorenzo asked: Your example gives 7.65 hours and you seem to want 7.6 ==> That could be a round Down to the 1st digit And why round down in a borderline case, as opposed to rounding up? Are there potential employee relations implications, given that pay is affected, even if minimally?Re: Time sheet for hours worked
It may be clear to you what "we do things in 6 minute increments" means in this context. I know what the phrase means--and am assuming it is convenient because it refers to tenths of an hour--but you don't make clear how you're using it (or hoping to use it) in tracking time. Here are the possibilities that occur to me. Does it mean you: round each entry to the nearest tenth of an hour round each segment of clocked in to clocked out to the nearest tenth of an hour only round the final total "Hours Worked" to the nearest tenth of an hour something else?Re: Need 1 worksheet to add totals from multiple worksheets in the same file.
In addition to the suggestions by m_tarler , there's also the possibility that you could redesign and consolidate the multiple sheets--depending on the reason for their existence--into a single sheet. It's not uncommon for people to design a workbook with a separate sheet for (say) bank statements for each month of the year, and then want a front sheet (or dashboard) to consolidate all income and expenses for the year. It's actually a better use of Excel to have a single worksheet with all transactions for the year (or even multiple years) and use Excel's marvelous abilities to pull out, when needed, the transactions for a single month, or the transactions involving a single payee (your car payments), or the transactions for a category of expenses (utilities). If something like that could make sense in your situation, why don't you share a bit more of what your workbook is about, why you have multiple sheets, etc.Re: Excel sheets that populate a drop down menu
Without seeing your workbook, it's hard to be certain about this, but the way you describe it leads me to underscore what m_tarler says in his first paragraph: you should seriously consider redesigning your workbook so that it takes the form of a single large database. Then you can take advantage of Excel's many tools to parse, filter, select from that single database. As it is, you've slightly computerized what would have been the sensible approach back in the days of each machine being tracked on its own large paper sheet. It would be better if you rethink how you approach it, looking for a way to take more full advantage of Excel.Re: excel themes
By way of adding to my suggestion that you tread carefully: I asked AI (ClaudeAI, specifically) what Excel pros have to say about the benefits and drawbacks of Excel themes. Here's the response. Notice that there are more concerns listed than positive, especially if this is a workbook that you'll be sending to other people. The Positive Case Consistency & Branding: Excel MVPs like Mynda Treacy and other professionals praise themes for maintaining corporate branding with one click. For dashboards and reports, themes can ensure uniform colors, fonts, and effects across entire workbooks, which is valuable for professional presentations. Efficiency: Professionals appreciate that themes can transform a workbook's appearance quickly rather than manually formatting elements one by one. Custom themes can be saved and reused across all Office applications. The Major Concerns Portability Problems: This is the big one. As one commenter corrected on Mynda Treacy's site: when you send a file with a custom theme to someone outside your organization, the colors will change to "something completely off" and Excel substitutes default fonts even if the recipient has the fonts installed. The theme file itself doesn't travel with the document for external recipients. Style Proliferation: Mynda Treacy warns that "Styles can breed like mice when copied from workbook to workbook and can quickly multiply to plague levels" — a colorful caution about how styles can create maintenance nightmares. Version Compatibility: Users report frustration when Excel updates change default themes unexpectedly, breaking color-dependent macros and requiring manual theme resets on each new file. Limited Adoption: Multiple sources note that few people actually use themes despite their availability. As one tutorial states, "few people use themes to enhance their worksheets" because Excel users focus on data rather than aesthetics.Re: excel themes
I've never used these, but I queried Excel Help and was pointed to the Page Layout tool bar, where I see themes in the immediate left. And when you click on it, this opens up I'm doing this on a Mac, with Excel 16.107 If your issue is specifically finding the "Basis" theme and it's not there, THAT is a different issue. Beyond that, I'm curious what you're wanting to do with a theme in the first place. I've used Excel a lot (over several decades) and have never come across these; and I'd be wary of using them anyway. If you want to create a good looking output sheet at the end of a bunch of analysis, that's one thing, but be careful about prettifying background database and processing sheets. Too much attention to looks can seriously interfere with reliable functionality.Re: Trying to fill a field in excel with 3 different wordfs based on another field result
You need to become acquainted with the IFS function. It should be very straight forward. Depending, though, on the degree of precision, you may need to do some finagling to get 32.5% (for example) to display the right words and color. Or go at it backwards: IFS stops at the first successful match, so you could write it: =IFS(G7>.75,"Good Deal",G7>.33,"Fair Deal",G7>0,"Bad Deal")Re: EOMONTH
Lomburus : you may be wondering why Lorenzo can state with such absolute certainty that you didn't do anything wrong. What he didn't explain is how dates work in Excel. Here's an excerpt from Exceljet.net, a helpful website for understanding many aspects of Excel. "In Excel's date system, dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on. More recent dates are much larger numbers. For example, January 1, 1999 is 36161, and January 1, 2010 is 40179." It's this underlying feature that makes date math possible, which is the why behind the way Excel stores dates.Re: Everchanging spreadsheet
Your description still leaves a lot to the imagination, making it next to impossible to respond with any specific suggestions. You'd help us help you by making available a copy of the workbook as it currently exists. If you're not able to link it to your posting here on this forum, then put it on OneDrive or GoogleDrive or the like and paste a link here. Also be aware that taking optimum advantage of Excel might lead some of us here to recommend minor (or, potentially, major) design changes to the basic design as you've envisioned it.Re: Paste values-only Excel
"Paste Values Only" doesn't convert text to numeric values; it pastes content (aka value) "as is" But assuming that source is in fact text, and therefore what's in your column C is also text, you can convert to value by entering in column D, =VALUE(C1) and copying that down. No need to search and replace the dollar sign and space.Re: Excel message: There's a problem with this formula
Well, one possibility is that, just as you've done with posting your question here, you've given the normally very cooperative VLOOKUP function utterly insufficient information to proceed. That's my smart alec way of saying, "You need to give us a lot more information if you expect a meaningful answer." For example, and just for starters, what's the VLOOKUP formula in this case? Second: is the error message, "There's a problem with this formula"? or is that your interpretation of the situation? Do you see what I mean? In the ideal world--although many of us have had problems doing this--you could help even more by attaching a copy of the workbook(s) in question. That can be a challenge, of course, if they are--or contain materials that are--confidential or proprietary. The point, though, is that you will need to give more information if you want meaningful help.Re: Excel
djclements agree with your disappointment/frustration with this forum as well as with it having been a favorite but one I now regularly am considering dropping. The frustrations with including a file are one thing, but it also continues to seem that there are FAR fewer users coming here with questions. Has it somehow become harder to find?Re: Multiple Inventory entries on the same item
I find myself wondering how the whole spreadsheet is arranged, how you're walking around checking inventory and making entries on how many different items... It sounds as if you've got one row per item, and are entering numbers for Item A each time you see ItemA on a shelf, then going to the row with ItemB and entering numbers for it, then back to ItemA and now needing to jump ahead to column whatever.... All of which might make sense if you've only got three or four items and no more than three or four of each in stock. But it only kinda makes sense. Another approach would be something along the lines of this hastily created workbook. One sheet (Catalog) contains a list of current products, used to provide the drop down for column A of the inventory sheet itself. You just walk around creating a new row for each new entry. If there are more entries needed than the current table provides, copy the last empty row before filling it, adding enough to meet the need. The Pivot Table takes care of totalling up the numbers of each. This could be made more sophisticated, as needed. For example, if you want to track what room or shelf where each item is located, that could be another column, also managed by drop down so spelling is always consistent, etc. See the attached spreadsheet.
