CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetschartsgoogle-sheets-formula
Sinha
Sinha

Reputation: 456

Plot conditional chart in Google Sheets and automate the plotting

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

Upvotes: 1

Views: 816

Answers (1)

Tanaike
Tanaike

Reputation: 201388

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.

Upvotes: 2

Related Questions