Scatter plot using Google sheets

[vikas_newports]

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.

[MrShorty]

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?

[vikas_newports]

[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

[MrShorty]

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).

[vikas_newports]

. 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.

[MrShorty]

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.

[vikas_newports]

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 .

[MrShorty]

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.

[vikas_newports]

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

[MrShorty]

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.

[vikas_newports]

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 ?

[MrShorty]

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.

[vikas_newports]

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

[MrShorty]

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?

[vikas_newports]

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)

[MrShorty]

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?

[vikas_newports]

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

[MrShorty]

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.

[vikas_newports]

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

[MrShorty]

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).

[vikas_newports]

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"

[MrShorty]

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(...))?