Plot conditional chart in Google Sheets and automate the plotting

user_6293211 0
Viewed 112

I have data in Google Sheets as shown in attached first photo. I would like to plot Date v/s Vol in a chart. However, I would also like to indicate red 'X' (or 'O' or anything) to the datapoints where Condition1, Condition2, Condition3 are all = Yes (see second photo). These are the rows indicated in yellow color.

  1. How to accomplish such chart plotting (second photo) in Google Sheet chart?

  2. Is there any way to build a formula which takes data location (in this case B2:F38) as input and automatically plots such chart starting at, say, cell H6?

Link to file: https://docs.google.com/spreadsheets/d/102uEOgYxt4JfJQuYfroBZnaX3CCbmavD3eSh--qDJh4/edit?usp=sharing

enter image description here

enter image description here

google-sheets-formula google-apps-script google-sheets charts
1 Answers

In your situation, how about using the following sample formula?

Sample formula:

When your sample Spreadsheet is used, please put this formula to the cell "G3".

=ARRAYFORMULA(IF((D3:D="Yes")*(E3:E="Yes")*(F3:F="Yes")=1,C3:C,""))
  • In this formula, the columns "D", "E", and "F" are yes, and the value of column "C" is returned.

Testing:

When this formula is used, the values are retrieved as follows. And, create a chart using the columns "B", "C" and "G". And, when the circle of the values of column "G" is large, the following result is obtained.

enter image description here

Note:

  • When you use X mark as the plot of column "G", you can obtain the chart with X.
user_71086530
Related