Recent Discussions
VLOOKUP #N/A error -- first time poster
Hello. I am getting an #N/A error when using a VLOOKUP formula. Background: I want to be able to calculate a person's z-score to give me a percentile based on their age and score. B3: person's age B5: person's score A9:C17: 3x9 table that contains age ranges with their corresponding mean and standard deviation In order to calculate a person's z-score ((score-M)/SD), I use the following formula in B19: =IF(B3<35,((B5-B9)/C9),IF(B3<45,((B5-B10)/C10),IF(B3<55,((B5-B11)/C11),IF(B3<60,((B5-B12)/C12),IF(B3<65,((B5-B13)/C13),IF(B3<70,((B5-B14)/C14),IF(B3<75,((B5-B15)/C15),IF(B3<80,((B5-B16)/C16),IF(B3<89,((B5-B17)/C17)))))))))) This gives me a number in B19. E.g., If age (B3) is 34 and their score (B5) is 57, B19 = 0.34 ((57-56)/2.9). Next I want to convert the z-score/output from B19 into a percentile in B20. The 2x1001 table with z-scores and their corresponding percentiles are in a 'Supplemental' sheet. B20 has this formula: =VLOOKUP(B19,Supplemental!$H$17:$I$1017,2,FALSE). I keep getting an #N/A error for B20. I think it has something to do with B19 being calculated from a formula. If I type 0.34 into another cell and change the B20 formula to pull from that cell, it works. What I've tried: -changed format of cells so they are all the same (I tried changing both to Number, both to General, both to Text) -changed format of cells so that B19 is the same as Supplemental H, and so that B20 is the same as Supplemental I -made sure decimal spaces were the same Thank you for reading this far. Appreciate any advice you can give. I've played around with Excel a lot and have managed to figure things out by searching online when I get stuck, but I can't get around this error. I'm not a beginner, but I am no where near proficient with Excel, so please err on the side of overexplaining any recommendations. Thank you! A B C 3 Age 34 (for this example) 5 Score 57 (for this example) 8 Age M SD 9 25-34 56 2.9 10 35-44 56.1 3.6 11 45-54 55.4 3.6 ...etc. 19 z-score 0.34 (for this example; calculated from formula: =IF(B3<35,((B5-B9/C9), IF(B3<45.... ) 20 %ile #N/A (should be a value ranging from >99 to <1, calculated using formula: =VLOOKUP(B19,Supplemental!$H$17:$I$1017,2,FALSE) Supplemental sheet: H I 16 z-score %ile 17 5.00 >99 18 4.99 >99 19 4.98 >99 ...etc. (z-score ranges from 5.00 to -5.00, %ile ranges from >99,99,98,...3,2,1,<1)
How to insert a master foot note in many other word documents
Hi there, I can't find the proper information on how to create a separate footnote MS document and link it into other of my MS document. I need to have a sord of template that I could modify at one place and then will be updated in all other MS documents that I had insert it. ** I've tried to use : insert -- text tab -- object -- text to file. And then "Insert as a link" the footer MS file but there is nothing that has been imported... I don't get it. thanks, CC
Capture Edited Form Submission
Hello. I have created a Power automate to capture the response when a form is submitted. We allow the users to edit the from after submission, sometimes they don't have all the info at first submission. Does anyone know how to capture the resubmission of the form after the user edits it. The Excel sheet that the form is linked to does get updated with the resubmission but I cannot use this to trigger an alert for the edit. I am trying to avoid linking the form to Sharepoint list just to capture the change. Ideally I would like to trigger a workflow directly from the resubmission of the form. Thanks
MS Word keeps randomly pasting from the clipboard when editing a document (on Macbook Pro 2020)
I'm editing a word document. On a regular basis, the last contents of the clipboard gets pasted into the word document, without me asking it too. For ages I though I was somehow hitting an incorrect key, but I have typed very very carefully and it still "just happens , sometimes more than a couple of times a minute Its really annoying! This is the second Macbook Pro this has happened on. It does not happen with any other applications, which maybe points to it being a problem with MS word? The MS Office suite is up to date (word version 16.81 24011420)Add "Delay delivery" to outlook.com (web) and mobile apps
One of the features that I really like in the Outlook desktop apps is the ability to delay sending an email, that way I can take notes on the go and ensure the email only gets sent during business hours (to preserve digital work / life balance for my colleagues). A few tech platforms are introducing digital well being features, and think this feature could be really helpful. Note: Another limitation of the delay sending feature is that it requires the sender's machine to have the desktop app running (otherwise the message won't get out of your outbox).
Visual and dax level optimization that causes report slowdown
Hi, I have this visual as attached, that has lot of visual level filters applied it has a dax measure called data completeness as below Data Completeness = var _total = COUNT('Calendar'[Date])*COUNT(Points[DBName-Point_Id]) var _result = [Count of Exisitng Days]/_total return _result It references a dax called Count of Exisitng Days as below Count of Exisitng Days = Var dates = SUMMARIZE(Data, Data[DBName-Point_Id], Data[Date]) Var Ext_dates = COUNTROWS(dates) return Ext_dates Now, the problem I have here is the performance of this visual is causing report to slow down. Please let me know how to optimize the dax and further steps to increase the performance PFA file here PR-419 - Data Coverage - Copy.pbix Thanks in advance! SergeiBaklan​
Microsoft Places individual desk booking is not working
Hi, Following the documentation, Configure desk booking - Microsoft Places | Microsoft Learn, I successfully ran the shared command lines without any errors. However, on Microsoft Places, I am unable to see individual desks appear; only desk pools are visible. Has anyone managed to get this to work? Thanks,
365 App Sign in & Edit Issues
Hello, I am attempting to edit a word document saved on OneDrive. I have the 365 app. The document opens and states I cannot edit unless I sign in. When clicking to sign it, it then states it cannot put my personal email (which is what the subscription is under) I need to put a work or school email and if it doesn't work then to contact said work or school. However, my subscription is my personal email and I am logged into the app with my personal email. I'm confused and need support please! Exact wording opening document: "This account does not allow editing on your device. For an account with full access, contact your organizational out your subscription plan" **For reference: I have frequently used my android phone for editing documents Exact wording when signing in: "We couldn't find a work or school account with that email address"
Microsoft Places Analytics - Missing data
Hi! I'm looking to hear if anyone has succesfully configured Microsoft Places Analytics according to the guidelines here: Microsoft Places Analytics - Microsoft Places | Microsoft Learn I've reached out to Microsoft Support several months ago regarding this but the case has not progressed at all unfortunately when it comes to actually reaching a solution. I'm struggling to get enough data to show even though the following is OK: All buildings have analytics enabled The name of the building matches the attribute "Office Location" in Entra ID. A mail-enabled security group is used for assigning permissions to Places Analytics (as-per the requirements). All users of the above group have Teams Premium I'm mostly focusing on the building analytics here since that one is most interesting for us. Example of charts with missing or incomplete data: The above one shows nothing even though it should be fetched from the work location in conjuction with the leader's organisation data. In the above chart it's succesfully mapping the headcount of the number of people that have set their work plan with a chosen building, however it's completely ignoring the "remote" parts. Also ignoring the people that have no work plan set but do have the same Office location as the building name - which it should fetch from according to the "?" button: It's even more clear further down in the same "?": In my case it's ONLY showing the ones with a work plan set and with the actual building in question set. It's the same type of issue for most of the data but I believe that solving it on one end will solve the rest. So I'm really hoping there's someone else out there that has succesfully configured Places Analytics and might shed some light on what my configuration might be missing. Thanks!Shapes Moving Even When set to Do Not Move
I use Excel 365 Enterprise 4 edition. I created a file that is stored on a Teams site that contains shapes I use as buttons for my macros. Every time I open the file the shapes have moved and/or resized and I can't figure out how to prevent this. I have the shapes set to Don't move or size with cells set on each one of them. Users are not moving them. No one is playing any pranks, I wish they were it be a lot easier to fix. Anyone have any ideas?Using Filters to Auto-Update Complex Formulas
Hi, This is my first post here. Hoping someone here may be able to assist me. I have the below spreadsheet. There is currently 500 rows of data. At the Top is a Summary Grid with formulas summarizing the data below (Rating, Employee Level, etc. ) I'm also showing an example of one of the formulas in Cell F3. The other cells contain similar formulas. Ask: How do I get the Summary Grid to only display the data based on the rows visible, which is determined by Column T filter selections. For example, if the user selects "Level 4 Manager 2" and this manager has 10 employees, how do I get the Summary Grid to display just the data based on those 10 employees (versus the total 500). I've seen videos/sites that use the Subtotal or Aggregate formula, but it appears those can only be applied to specific functions, not a more complex formula. Is this possible?Help with VBA Code
Hello Experts...I am a novice, self taught, old golfer who has developed a workbook for calculating our golf game. The part I am struggling with is the calculation of team scores. I believe I need to have individual VBA macro buttons since the team game we play is different depending on how many golfers show up (typically 8-20). The scores are entered in the following format: The team score calculation needs to look at team #, gross score (yellow cells), calculate net score based on handicap (HDCP) and the number of scores needed for the team score. Net scores are determined by taking the gross score and determining if the golfer is eligible for a handicap stroke. Example: Bob M has a 7 HDCP (cell G2), on the #1 thru #7 handicap holes (row 16), one stroke is subtracted from his gross score to determine his net score. Team scores are reported relative to par (row 17) for the total 18 holes. The output should be a simple two column table with team # and 18 hole team score. Typical team games are: 2 golfer team best ball net score - The lowest net score of the two players. 3 golfer team 2 out of 3 net score total - The lowest 2 net scores of the three players. While there are additional games we play, if I can get these two programmed, I can use them as a guide to help script the remaining games. The workbook is 182MB so I can't attach it (it does a number of other things; team set up, payout calculations, etc...). Am I approaching this problem in the most efficient way or should I be focusing my efforts in a different direction? Thank you!Excel won't print
Since about 2 weeks I can't print anything from excel. The error message says something about "not enough storage": I can print from Word, PDFs, etc. but not from Excel. If I open the file with a different tool it prints just fine. I tried different spreadsheets, different files, but nothing help. I also tried the solutions I found eg registry permissions, repairing Office 365, etc. I'm using Windows 11, Office 2024 Professional Plus, the printer is a Minolta bizhub C300i. All software is uptodate. Any help, ideas much appreciated ThomasComplicated formula help for a budget spreedsheet
Hello everyone, Normally I can find what I'm looking for and apply it to my project, but this one is a bit complicated, if it can even be done. I'll try to explain it as best as I can. I've got a calendar type layout on one tab(Forcast Template), with bills listed on anoter tab(All Bills). What I'd like to get it to do, is automatically fill in each bill for the corresponding day. The complications are as follows: some days have multiple bills due, and some bills only apply to certain months. I'm familiar with if/then functions as well as xlookup, but combining the two for this application is proving to be a tad more complicated. Again, that's if it can be done. Not sure how to attach the file for viewing, but I'll post screenshots so hopefully you can tell what I'm trying to do. Let me know if I need to clarify anything. Thanks!
No events showing in Microsoft Mesh after latest update
Yesterday mesh auto updated and ever since I can't view any ongoing or past events. This happened right before an important demo. Am I the only one with this issue? On the web the event is active. I can also find it on my laptop, which has not updated the app. However, on my VR headset it displays that no events are active. The outcome is the same for both organizers and participants of the event We found a workaround which was to guide the participants into the Environment Template, but that's complicated for people not that aren't used to VR or, tech in general. This needs to be fixed!Adding data ONLY when two cells match across sheets...
Hi all, Apologies: this is complicated... I'm working across x2 sheets in an Excel book. On sheet 2, I have my export page, where I paste the uploaded data for it to fill in sheet 1. On sheet 1, I have the historical data which I need to add to weekly. Sheet 2: Export Page including current formula (based on a working formula on another sheet we use. I need both forename and surname to match on the next page, and automatically paste columns C and D if/when/where they do, then tell me where any names have been missed (new students, etc.) so I can add them to the doc. So if A2 and B2 were on A6 and B6 on sheet 1, that is where the data (C2 and D2) would be pasted, to ensure that the student retains their own points balances. Sheet 1: Overview Page - I intend on the above columns C and D then being put into columns J and K on this page. I'll then just copy and paste the data across into the correct week following analysis, ready for the formula to re-populate those cells with the coming week's data. I switched forename and surname around for readability but can return if that makes it easier (for GDPR purposes, I have removed all names). The formula there had green "Yes"s through the page when I used the original data. When I have gone to update today, everything is a red "No", as you can see, so I clearly don't have the right formulas!! Appreciate your help, as I've been working on this for a couple of weeks now and just can't figure it out!! The formula on the other sheet that I was trying to work from was: =IF(COUNTIF(Table1[@[Name and tutor]],A2)=1,"Y","N")
Help needed Exel Newby
I want to make a date range in Exel. if something is due to run out in less than 15 days I want it to be red if something runs out in 90 days I want it to be green and if something is runs out in 15 days to 90 days I want it orange. how can I do this? where can I find simple functions?
Recent Blogs
- 2 MIN READWe've eliminated the unused space between the ribbon and your canvas, giving you more room for what matters most.May 08, 2025
- 3 MIN READOneNote for the web went through a series of enhancements made over the past few months, designed to elevate your note-taking experience and boost your productivityMay 07, 2025
