Help needed with IF and COUNTIFS Formulas
Is anyone able to advise the following formula: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & V3,$D5:$D15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & V3,$H5:$H15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & V3,$L5:$L15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & V3,$P5:$P15, ">" & V3) Is there a way to simplify this? Is there a way to make this more accurate? Cells in column G & H, I & J, O & P are using the following format: =IF(C6="","",C6+E6) Cells in U4:CC4 are using the following format: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & U3,$D5:$D15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & U3,$H5:$H15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & U3,$L5:$L15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & U3,$P5:$P15, ">" & U3) Cells in U5:CC15 are using the following format: =IF(U$4>=$T5,1,"") My issue is is when I put in the three break times, the mid break comes out at a shorter time. My other issue is is that when I put in the times in row 5,6and 11, the data is coming up as a combined data in rows 5, 6 and seven on the page two. Just for reference, "page two" is the same spreadsheet. What I need to happen is that I enter in the shift start time and finish time. This then populates through to Break 1, 2 and 3. The Time entry is the time the break starts. ie: 1 hour after start of shift, 1 hour after coming back from break, etc. The break entry is the duration of the break taken. ie: 30 minutes. Once all the info is put in, the relevant "Time Block" on "Page 2" shows a 1. What is happening at the moment is that when I enter all the time data, the time blocks are not populating correctly in accordance to the entry. Basically, If I have numerous people on shiftI need the time blocks to show where I have shortfalls in shift cover and not having too many people on break at the same time. IE: Link to Live Copy: https://www.dropbox.com/scl/fi/eur1j526htu1j8a4d4290/Staff-Breaks.xlsx?rlkey=r4tm9xts4tonofpa2th2cusfw&st=nueyk0d7&dl=0 Any ideas would be greatly appreciated.
Best way to organize a café / drink menu with many items for easy scanning in Excel?
Hi everyone, I’m using Excel to manage a café-style menu that includes a lot of drink items, categories, and prices. The challenge I’m facing is readability. When everything is in one long sheet, it’s hard for people to quickly scan the menu and find what they want. I’m trying to make this more user-friendly for non-technical users, similar to how customers scan a real menu. In Excel, what approaches work best for this type of use case? For example: Separating items by category (coffee, cold drinks, specials, etc.) Using filters or tables to narrow choices Structuring the sheet in a more menu-style layout instead of a flat list From your experience, what makes menu-style lists easier to understand and navigate in Excel? Any practical advice would be appreciated.
Moving a column of text data into 3 columns of data?
I have a column of text data cells 1,2,3,4,5,6,7,8,9 and longer. I want to create 3 column of data to graph and manipulate Cell in Columns. 1,2,3 3,4,5 5,6,7 8,9,10 and longer. So i need to create 3 columns of data from 1 column of data. I am using Mac Excel 16 and I can not make this happen. I have tried all sorts of solutions. Help? Thank you,Double Thunking Works Wonders!
Given that most Excel users would not dream of employing one thunk, you might well ask why even consider nested thunks! The use case explored here is to return all the combinations by which one might choose m objects from n (not just a count of options =COMBIN(n, m), but the actual combinations) Knowing that sometimes allows one to deploy an exhaustive search of options to determine the best strategy for a task. Before considering the task further, one might ask 'what is a thunk; isn't it far too complicated to be useful?' All it is, is a LAMBDA function that evaluates a formula when used, the same as any other function. The formula could be an expensive calculation or, rather better, no more than a simple lookup of a term from a previously calculated array. The point is, that whilst 'arrays of arrays' are not currently supported in Excel, an array of functions is fine, after all, an unrun function is little more than a text string. Only when evaluated, does one recover an array. In the example challenge, each cell contains an list/array of binary numbers, which might itself run into the hundreds of terms. A '1' represents a selected object whilst a '0' is an omitted object. Rather like the counts of combinations obtained from Pascal's triangle, each cell is derived from the contents of the cell to the left and the cell above. This is SCAN on steroids, accumulating array results in two directions. Running down the sheet, the new combination contains those of the above cell, but all the objects are shifted left and an empty slot appears to the right. These values are appended to those from the left, in which the member objects are shifted left but the new object is added to the right. So the challenge is to build a 2D array, each member of which is itself an array. The contents of each cell is represented by a thunk; each row is therefore an array of thunks which, for REDUCE to treat it as a single entity, requires it to be securely tucked inside its own LAMBDA, to become a thunk containing thunks. Each pair of rows defined by REDUCE is itself SCANned left to right to evaluate the new row. By comparison the 2D SCAN required for the Levenshtein distance which measure the similarity of text strings was a pushover. I am not expecting a great amount of discussion to stem from this post but, if it encourages just a few to be a little more adventurous in the way they exploit Excel, its job will be done! p.s. The title of this discussion borrows from the Double Diamond advert for beer in the 1960sAs any one found cool icons to use on a Custom Ribbon?
Hello Excellers, I just finished making a neat custom ribbon for an application, and I am wondering if anyone has found a cool and neat place to grab some icons for the button faces specially if in color. My ribbon looks nice, and most importantly it works as intended, but I am kind of thinking it could be more colorful. So far I only used the built-in stuff. Thanks for any hints. GiGiWinFix Toolkit (All Windows 10 & 11 Repair Tools in One Excel)
After I published this small information tool (Excel (365 & 2016) with network information), several people contacted me and asked if I had a tool with Excel for general service tasks that, while available in Windows, are a bit scattered and confusing. So, I've prepared this small tool for Service Level 1, with most of the service options included. Hardware Repair Tools Repair Action Label Description Reset Windows Update Components UpdateReset Stops related services, renames cache folders, restarts services. Check System File Integrity (sfc /scannow) SFC Scans and repairs corrupted system files. Check Disk for Errors (chkdsk /f /r) CHKDSK Scans hard drive sectors and attempts repair. DISM Health Restore (dism /online /cleanup-image /restorehealth) DISM Repairs Windows image and component store. Network Reset (netsh int ip reset, netsh winsock reset) NetReset Resets TCP/IP and Winsock catalog. Flush DNS Cache (ipconfig /flushdns) DNSFlush Clears DNS resolver cache. Device Manager (open) DevMgr Opens Device Manager for hardware inspection. Software Repair Tools (examples) Repair Action Label Description Microsoft Office Quick Repair OfficeRepair Launches Office repair tool (Quick or Online). Reset Microsoft Store StoreReset Runs wsreset.exe to reset Store cache. Repair OneDrive OneDriveReset Resets OneDrive client (onedrive.exe /reset). Windows Defender Full Scan DefenderScan Triggers Windows Defender antivirus scan. Reset Windows Firewall FirewallReset Restores default firewall rules. Reset Windows Search Index SearchReset Rebuilds Windows search index. Clear Temp Files TempClean Deletes temporary files and folders. Reinstall UWP Apps (if broken) AppsReinstall Re-registers all built-in Store apps. I hope it might be helpful to some people. The tool has been tested, but it could still use some improvements, so I'd like to ask everyone who has looked at or used this tool for feedback. I would appreciate any constructive feedback or additional suggestions. Happy Excel-ing! *My tool are voluntary and without guarantee! NikolinoDE I know I don't know anything (Socrates)Suggestion: Add =IMAGE() function to Excel 2016 & 2019
Hi Microsoft Team, I’m an Excel 2016 user and really appreciate the product. However, I’ve noticed that the =IMAGE() function is available only in Microsoft 365 and the web version. Many of us who are using perpetual versions (Excel 2016 / 2019) would love to see this function back-ported. It’s a small but powerful feature that makes dashboards, catalogs, and reports much more useful. Not everyone can move to 365, but we still look up to Excel for improvements. Adding =IMAGE() to 2016 and 2019 via update would make these versions more practical and user-friendly. Please consider including this in a future update. Thanks for listening and for continuing to improve Excel!How to Be an Excel Detective: Finding and Highlighting Formulas
Hi everyone, I recently wrote a blog post on some simple, yet powerful, techniques for anyone who works with Excel spreadsheets, especially those with complex data. I wanted to share a summary of it with this community, as it might be helpful to others who are looking to understand and protect their work. The post covers two main things: Quickly Revealing All Formulas: A simple keyboard shortcut (Ctrl + `) or the "Show Formulas" option can instantly reveal all formulas in a worksheet. This is a great way to quickly see how a spreadsheet is structured. Permanently Highlighting Formulas: The article shows how to use the "Find & Select" > "Formulas" feature to select all cells containing formulas and then permanently highlight them with a fill color. This visual cue can help prevent accidental edits and protect your data. Watch This in Action: For a step-by-step guide on how to use these techniques, you can watch the video on my https://www.youtube.com/@BIGurus. 🔗 https://youtu.be/1x-1dbqlWXk You can also read the full article here: https://medium.com/@anandsharad/how-to-be-an-excel-detective-finding-and-highlighting-formulas-fe9d4fdbc1b1 I'd be happy to answer any questions you have or discuss other Excel tips and tricks in the comments!
