+ Reply to Thread
Results 1 to 22 of 22

Scatter plot using Google sheets

  1. #1
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Scatter plot using Google sheets

    Hello
    I hope everyone is doing well . I want to draw the scatter plot using google sheet but I am unable to draw .
    I have attached my data files.


    Each row of the 'meals' sheet has several columns. The only relevant ones for this task are "finish time" and "date".
    The sheet HRV contains 2 columns: date and HRV (integer value).

    Result:
    Scatter plot using Google sheets
    The values to be plotted are the "finish time" of the last meal of a day and 'HRV' value of the day after.
    X axis = finish time (of the last meal of a day)
    Y axis = HRV

    There might be more than one meal (row) for a day, only the 'finish time' for the last meal of the day should be plotted.
    Only meals occurring after 17 hours should be taking into account too.

    Example:
    HRV:
    11/2/2021, 34
    12/2/2021, 20

    Meals:
    11/2/2021, 12:30
    11/2/2021, 18:15

    From the data above, the only point represented in the chart will have coordinates x=18:15 y=20
    Output Formatting:
    time: "hh:mm"
    Dots in the graph can be of any form or color.


    Normalize date formats to dd/mm/yyyy.
    Otherwise you can modify other formatting, data structure, merge sheets, etc to your will.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    Since I'm not sure what part of this you need help with, here's what I see needing to happen (starting from these text files) in a broad overview. Let us know what part of this you have trouble with:

    1) With text files, you first need to import the text into the spreadsheet. You have not said if your data are already stored in Sheets or if you still need to import. If you are uncertain how to import text/csv into Sheets, here's a brief tutorial: https://www.organimi.com/how-to-conv...google-sheets/ I'd probably import both files into separate tabs in the same spreadsheet file.
    1a) Pay attention to the different data recognition steps so you can select the appropriate options to make sure that Sheets imports the date and time fields as date/time numbers and not text. Most searches I've made for problems with date/time stored as text mentions using an add-on called Power Tools: https://www.ablebits.com/docs/google...lit-time-date/ Of course, steps 1 and 1a are not important if your data are already stored in Sheets correctly. These steps are only if you are starting with a text file.
    2) We need to identify the latest (maximum) finish time for each date. This can be accomplished with a MAXIFS() function (note that many versions of Excel hide the MAXIFS() function behind a subscription wall, so if you need this to be backwards compatible to many Excel versions, MAXIFS() will not always be compatible). https://support.google.com/docs/answer/7013817?hl=en I would expect something like =MAXIFS(meals!$C$2:$C$1000,meals!$A$2:$A$1000,hrv!A2-1) in hrv!C2 and copied down.
    3) Create the scatter chart using hrv column B as the Y values and hrv column C as the X values. I don't know exactly how Google sheets will do that, but this describes the basic process of adding a scatter chart to sheets: https://productivityspot.com/scatter...google-sheets/
    Alternatively, because spreadsheets typically assume that the x values are in the left column and y values are in the right column, you may want to put your MAXIFS() column in between the date and hrv column i8nstead of off to the side.
    4) Any cell or chart formatting you want to do.

    Which of those steps do you have trouble with?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    [QUOTE=MrShorty;5472026]Since I'm not sure what part of this you need help with, /QUOTE]

    Thanks i am stuck at Time)(Hour:MIN) Column C in HRV sheet .... as maxifs not giving right answer
    for example in meals 10/2/2021 thre is max time available in C column but maxifs showing as 12:00AM

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    for example in meals 10/2/2021 thre is max time available in C column but maxifs showing as 12:00AM
    . What is the underlying actual value being returned by the function? A time of day value of 12:00AM suggests it is either returning 0 or some other integer value.

    If it's returning 0, that suggests to me that the time values are actually text. I tried to emphasize in my step 1a that it is essential to be sure that Sheets imports the time values as actual time numbers and not text strings. Obviously, without a sample Sheets file, we cannot debug this for you, but I would first look at the values in column C (and column B) to see if Sheets has imported/entered them as time numbers or text. =ISTEXT(C2) in a convenient, adjacent column would be one quick debugging test. If this formula returns TRUE, then the value is text and the MAXIFS() function will return 0 (because it is programmed to ignore text). If the value is text, then we will need to look into strategies that can be used for converting text to numbers in sheets (like maybe a TIMEVALUE() function followed by a copy/paste as values operation).

  5. #5
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    Quote Originally Posted by MrShorty View Post
    . What is the underlying actual value being returned by the function? A time of day value of 12:00AM suggests it is either returning 0 or some other integer value.
    Thanks MrShorty here is the link for sheets
    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    I hope I didn't hurt anything, but I changed the date format in both sheets to an unambiguous d-mmm-yyyy format so that I wouldn't need to worry about whether the dates were in the DMY European tradition or MDY US tradition. There are no formulas in either tab, so I could not debug anything.

    It also seems like I am misunderstanding what you want to do. On HRV, your first entry is for 6 Feb 2020 and column C has 19:25, but I see no data for 6 Feb 2020 in meals. For 11 Feb 2020, HRV shows 0:00, but I see a finish time of 12:00 for 11 Feb 2020 in meals. I am not readily seeing how the data on HRV and meals are intended to be connected.

    I entered the MAXIFS() function I expected to see in column D. It looks like the results are one row off from column C, but I don't know how you obtained the values in column C, so I don't know why that would be.

  7. #7
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    Thanks MRSHORTY
    dates are incorrect now because the max date is the Feb 2021 and after changes, it is NOV 2021
    I have mentioned in OP my desire results .... i think First we need to set the data in excel file then upload it to sheets .

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    The spreadsheet part of Sheets is the same as it would be in Excel. I recognize that there are some things Excel does better than Sheets (and some things Sheets does better than Excel), but the only reason I can see for "needing" to pass the data through Excel before Sheets is to take advantage of something Excel does substantially better than Sheets. Based on what I understand, there is nothing in your request so far that Excel would handle better than Sheets.

    I know you tried to explain your requirement in the OP, and the MAXIFS() function should be one way to solve the problem in the OP as I understand it. But I also feel there is something about your request that I am not understanding. For example, in your latest version of the file, you have separated the dates out into separate Day, Month, Year columns. Why did you separate the dates into separate Day/month/year columns?

    Your current file does not show how you separated the date into its components. My guess is that the shift from Feb to Nov is an error in whatever algorithm/function/procedure that you used to separate the dates into components.

  9. #9
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    Quote Originally Posted by MrShorty View Post
    you have separated the dates out into separate Day, Month, Year columns. Why did you separate the dates into separate Day/month/year columns?
    I aim to ue date function that is why I used text to column to separate the date but got busy in domestic work. Well I agree with you we need to do things in excel than upload it to Sheets

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    I aim to ue date function that is why I used text to column to separate the date
    The DATE() function is one way to combine separated date components into a proper date serial number, but that also suggests that you are having trouble with the csv import process to get Sheets to properly recognize these dates. I don't know what Sheets is using in your locale for date detection standards (if it is having trouble, I assume it is expecting MDY dates). Here's what I did to import your csv files into Sheets without the need for a DATE() function:

    1) Import the csv file using the information in the tutorial linked to earlier: https://www.organimi.com/how-to-conv...google-sheets/ Be sure to specify "No" under "Convert text to numbers and dates".
    2) After the csv file is imported (with number/dates/times stored as text), specify the regional settings you would like to use for this file: https://productivityspot.com/convert...t_Text_to_Date I chose the UK, but any region that would expect a DMY date format would work.
    3) Select each column that has date/time/number information -> Data -> Split text to columns
    4) Verify that the date/time/number conversion was correct.

    The only advantage I see to using Excel to import the text files is that Excel's text import wizard (when you force Excel to use the text import wizard, which it doesn't always do with .csv files) has a step during the import process where you can specify DMY dates before Excel imports the text file. I could not find a way to tell Sheets to expect DMY dates before import. I could only do so after import. But that is a minor advantage that, IMO, doesn't offset the disadvantage of needing to import into Excel, then upload to Sheets. But, it is up to you which way you would prefer to do it.

  11. #11
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    I have changed the dates in Meals sheet ... but maxif funtions out now
    I think now we can easily create the Scatter graph as per request. what if we first try it in a excel file and then upload this data to sheets ?

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    what if we first try it in a excel file and then upload this data to sheets ?
    As I noted, I find Excel's text import wizard better -- especially for dates -- than Sheets. But, the end result is the same in either spreadsheet -- the data gets imported and dates/times/numbers in their proper data type.

    In Sheets, the MAXIFS() function is always available. As I noted in post #2, the MAXIFS() function in Excel is not available in all versions. In some of the Excel versions listed in your profile (such as Excel 2010), the MAXIFS() function is not available, so you won't be able to perform the calculation using MAXIFS(). As long as you are using a version of Excel with MAXIFS(), then there is no difference between using Sheets or Excel.

    I have found that the differences in the charting engines are quite significant across the different spreadsheet applications. If you create a simple scatter chart with basic formatting, a chart created in one spreadsheet will usually work okay in the other spreadsheets. However, it doesn't take very much non-standard formatting or other ways of making a chart more complicated before the chart will not carry over from spreadsheet to spreadsheet. IMO, I would create the chart in the spreadsheet where you need to view it. If you are going to view the chart in Sheets, then create the chart in Sheets. If you are going to view the chart in Excel, then use Excel to create the chart. If you need to go between the two spreadsheets, make the chart as simple as possible, and test it out in both applications until you find which chart features that you are using will carry over between the spreadsheets.

  13. #13
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    agree with you but still i m not getting a proper chart in sheets ... might be dates issue i think i must leave it as data is not getting proper form in sheets

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    Do you have a link to a current example of where you are at in this?

    The two main dates issues that I see when importing text to a spreadsheet are dates stored as text and dates incorrectly interpreted during import (the spreadsheet interprets the date as DMY when MDY was intended, for example). An ISTEXT() function can tell you if a date is stored as text or not. An unambiguous date format (like "dd-mmm-yyyy") can help determine if dates imported as the correct date.

    In your example spreadsheet linked in post #5, you have a good scatter chart of the data, so it seems like creating the chart itself is not a problem for you. The problem appears to be in getting the data imported into Sheets and then entering the appropriate spreadsheet functions.
    Are you able to import the text data correctly?

  15. #15
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    well I forget about it that I have posted my question earlier here and post again on the forum. Well I think we should make it on Excel and I am thinking to make the same date format whatever date format we follow it does not matter but the chart will have the below options to represtent
    x=18:15 Time in HH:MM
    y=20 (HRV)

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    I still see 3 steps:

    1) Text import. You were having some trouble with this in Sheets. Are you able to import the text correctly into Excel?
    2) Function to determine latest time for each day. In Sheets, I was proposing a MAXIFS() function. Will you be able to use the MAXIFS() function in Excel, or do some of the Excel versions where you need this not support the MAXIFS() function?
    3) Once the spreadsheet work is done, the scatter chart should be fairly straightforward -- select the appropriate columns in HRV -> Insert a scatter chart -> format the scatter chart.

    At this point, the main question is step 1 -- are you able to import the text into Excel correctly?

  17. #17
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    yes text is imported correctly due to system change issue not getting proper date formats

  18. #18
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    Perhaps it is the language barrier, but I am a little confused. You suggest that the text imported correctly, but then you suggest that you are not getting proper date formats. This suggests to me that the text did not import correctly. Can you verify? If you cannot format the dates or if a date like 1/2/2020 is imported as 2 Jan 2020 when it should be 1 Feb 2020, then the text is not being imported correctly and we need to spend more time with the importation step until Excel imports the dates correctly.

  19. #19
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    you are right and now I have set the dates and the maximum date is 11-Feb-2021
    The revised Final File is attached.
    Attached Files Attached Files

  20. #20
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    Did you decide if you can use the MAXIFS() function? For step 2, my first thought would be =MAXIFS(Meals!$C$2:$C$2000,Meals!$A$2:$A$2000,A2) in HRV!C2 and copy/paste/fill down. As noted previously, MSFT hides the MAXIFS() function behind certain subscription walls, so it does not work in all versions of Excel, so this only works if you will always open this Excel file in a version that supports the MAXIFS() function (or in Google Sheets or other spreadsheet app that does not hide the MAXIFS() function behind a subscription wall).

  21. #21
    Forum Contributor
    Join Date
    05-26-2016
    Location
    India
    MS-Off Ver
    Microsoft 365
    Posts
    237

    Re: Scatter plot using Google sheets

    Yes I tried maxifs and it is giving me time with 0:00 too ... I am not getting how to achieve below

    There might be more than one meal (row) for a day, only the 'finish time' for the last meal of the day should be plotted.
    Only meals occurring after 17 hours should be taking into account too.

    Example:
    HRV:
    11/2/2021, 34
    12/2/2021, 20

    Meals:
    11/2/2021, 12:30
    11/2/2021, 18:15

    From the data above, the only point represented in the chart will have coordinates x=18:15 y=20
    Output Formatting:
    time: "hh:mm"

  22. #22
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,222

    Re: Scatter plot using Google sheets

    I'm not following. Your example in #21 does not match the values in the example in #19 (11 Feb 2021 has an HRV of 35 and there are not entries at all for 12 Feb 2021).

    Yes I tried maxifs and it is giving me time with 0:00 too
    MAXIFS() will return 0 if there are not records that match the criteria. For example, there are no records in meals that are dated 4 Feb 2021, so the MAXIFS() function in HRV will naturally return 0. Are you noting the existence of these 0 values, or are you seeing cases where the date exists in both HRV and Meals but the MAXIFS() is erroneously returning 0 when it should return something else? Or are you wanting to trap the returned 0 and replace it with something else (NA() error is usually best for scatter charts) =IF(MAXIFS(...)=0,NA(),MAXIFS(...))?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 11-08-2019, 04:57 AM
  2. Replies: 3
    Last Post: 02-07-2018, 04:43 PM
  3. How to plot 3 variables in scatter plot with excel formula
    By tanvir in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-13-2017, 04:10 AM
  4. Excel 2010 - X Y scatter plot will not plot empty cells as gap
    By bjeffers0306 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-12-2013, 12:56 AM
  5. Replies: 2
    Last Post: 07-26-2012, 09:27 AM
  6. Can I copy x-y scatter plot data direct from one plot to another?
    By Chris in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-03-2005, 09:05 AM
  7. Converting XY Scatter plot to Line Plot and back
    By SPDavern in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2005, 10:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1