Forum Widgets
Latest Discussions
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?
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
Conditional Formatting Rules for Icon Sets
Hi, The following pivot table has been created using Conditional formatting Icon Sets : However, the Default Formatting Rule applied by Excel is not understood. Following is the screen shot of Formatting Rules: Under the Formatting Rule Section: 01. The Green Icon is applicable when the value is >= 67 % 02. The Yellow Icon is applicable when value is < 67 % but >= 33% 03. The Red Icon is applicable when the value is less than 33%. The above Rule is clear but in my following pivot table data I have not achieved 67% or 33% of Total Revenue. Pls take a look of the screen shot : In Column E , Sales_Amount / Grand Total does not achieve 66% or 33%. All values are less than 33%. But Icons in Col B are appearing in Red, Green and Yellow. Pls let me know what is the logic behind the application of Icon Sets in Col B (Sum of Sales Amount). However, Same Default Formatting Rule is applied in Col D (Sum of Dollar per Hour). Pls see the screen shot below: However, it is not clear on which amount 67% and 33% are getting calculated. In Pivot Table CALIFORNIA has Dolloar Per Hour ( Sales Amount / Contracted Hours) 68.30 and it is getting Yellow Icon. Pls let us know the logic / Calculation of : 67% and 33% for Dollar per Hour. I have also attached the Excel file for reference. Regards, Somnath6309
