Forum Widgets
Latest Discussions
sumifs doesnt work because of power query
Hi All, I've been trying to automate some regular reports using power query. The queries for the different reports are working and everything is fine, however I want to use those query tables and summarize them in my main report. The problem is that, i need to use a sumifs formula but it's not working. The formula that has always been used up until now with the old method of working is - =SUMIFS(SCRAP!$K:$K,SCRAP!$P:$P,"DTC*",SCRAP!$E:$E,"Job or Schedule",SCRAP!$B:$B,"<="&DATE(YEAR(SCRAP!$B$2),MONTH(SCRAP!$B$2),P$5)), where K:K is Value, P:P is Items, E:E is Source and B:B - dates and P5 is today's date (at the time of making the report) I think the issue is with column B (dates). For some reason, power query seems to "bug" the column with the dates and I've tried everything and it's still not working. Tried fixing it from power query editor and on paper it should be fixed but its not. Also, tried doing that in excel but still nothing. Any advice or a workaround would be greatly appreciated. Thanks in advance.
Excel form - may need VBA?
Hi all, New to this forum, but here goes: Have a complex Excel workbook (on Sharepoint). We need a better way to do data entry but with certain restrictions Want the data entry form to default to current date; how to enter a function into entry form? Want the form to behave according to data restrictions (pull-down) in the master Excel database sheet. The restrictions are on a separate tab How to do calculations in entry form (e.g. starting number plus quantity equals ending number; onlt start and end get populated back into database
combo bubble plot
Hi all I recently took part in a fauna survey of a meadow, where we reviewed one 1m square sites in a number of locations across the meadow to assess its health. I would like to present this data using the actual X and Y coordinates of the survey points and then have some representation of the over all health at that point. I thought a good way to do this would be using a bubble plot. With the coordinates being the X and Y and the health being the size of the bubble. That way we could get a visual representation (map) of the health across the site and potentially be able to compare future surveys be adding other data series for other surveys . The idea being that this might help us understand if some areas of the meadow healthier than others eg northwest corner or how things change over time. To help make this clearer I would like to do a couple of extra things to the chart and I just wondered if someone could given me a steer on how to do the following if possible: I would like to draw the meadow boundaries onto the same plot using the x and y coordinates so either using a scatter or line plot. So this would require a combo of scatter/line and bubble is this possible unless I can connect a separate series of bubbles with fixed size of 1% with lines. From a response in the Questions area it has been proposed that I grab a screen grab of another chart and use this as a background. which might make aligning difficult . So can I suggest that it might be worth looking at is this might be a future development Is it possible to get the actual bubble to colour code on the values in them ie RED if health under 30% to green if heath over 80% so that it is even clearer which are the healthier meadow areas. The proposed answer to this was to split the data into series for plotting with each series having a designated colour. Which works but would make plotting future years on the same graph difficult.
Need to ignore rows with text before using another function
I track the dates I start a task and the date it is returned to the client. I wanted an average of the times so used the below formula. Sometimes a task gets put on hold so I want to exclude those tasks which would be signified by text in row 'U' (the text varies due to different reasons for holds). I am using =(SUM('Tracker'!Q:Q)-SUMIFS('Tracker'!D:D,'Doc Executions'!Q:Q,">0"))/COUNTIF('Tracker'!Q:Q,">0") to get the average of the dates but this includes rows that were on hold which bumps up my average. Tracker is the name of the sheet Column Q is the return date Column D is my start date Thanks in advance!Default Date Format refuses to use system regional settings
There are lots of forum questions about date format - this might be answered somewhere, but it's too deep to find the answer. I have Excel from Microsoft 365, installed on my Windows 10 computer. Excel says it's version 2506. I have the regional settings on my computer set to US: Notice the date formats. When I open a new workbook in Excel, and type a date, if I don't include the year, it immediately converts it to a European format. I type 7/15, and it converts it 15-Jul: How do I correct this behavior? I typed it the way I want it. My system is set up to use MM/DD. But I have to change the column format to MM/DD to get Excel to use that.Excel Query
Reposting since I did a typo in last post. Hi everyone, Yellow highlighted column enteries are added manually. I want to insert a formula which will automatically write 1 in column "P" for unique enteries against column "C" and then write 2 in Column "P" for next unique entry in column "C". Can somebody please help?
