Forum Widgets
Latest Discussions
Complicated 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!ComicazyMay 14, 2025Occasional Reader97Views0likes3CommentsVlookup or any better function
Dear Experts, Greetings! I have a data like below:- Sheet 1 & 2, and have to fetch the data from 2 -> 1. If , I use Vlookup with True(approximate match) then it populates many times , but I want to populate the exact time from sheet-2 and put it in sheet-1 when the RRC(Column F) triggered example shown as 1st line from Sheet2 What's the best way to achieve this, we work in terms of ms( milliseconds ) so can't do any Approximations. Thanks in Advance, Br, AnupamSolvedanupambit1797May 14, 2025Steel Contributor47Views0likes9CommentsAdding 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")KirstyMay 14, 2025Copper Contributor91Views0likes6CommentsVLOOKUP #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)Solvedgetcracken801May 14, 2025Occasional Reader33Views0likes3CommentsVisual 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! SergeiBaklan167Views0likes10CommentsShapes 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?heylookitsmeMay 14, 2025Brass Contributor14Views0likes1Comment
Resources
Tags
- excel42,689 Topics
- Formulas and Functions24,767 Topics
- Macros and VBA6,420 Topics
- office 3656,050 Topics
- Excel on Mac2,658 Topics
- BI & Data Analysis2,380 Topics
- Excel for web1,927 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,640 Topics