A Scatter Chart Plots None-Existing Values

[ddgacic]

I have two worksheets in the workbook – “Data” and “Dashboards”

Worksheet Data has a 3 column table – A4:C6

Column “Region”: A4-A6 – in this case regions are labelled A, B and C.
Column “Score 1”: B4-B6 – scores are always between 0-10. Sometimes these cells can be empty if a score is currently unavailable.
Column “Score 2”: C4-C6 – same rules as for Score 1 above.


“Dashboards” worksheet is importing relevant information from “Data” worksheet (Score A and B columns) by formula in order to create a scatter dashboard for each region. All formulas in this worksheet are constructed to:

Check cells in “Data” worksheet; and
If a target cell is empty - leave a destination cell in “Dashboards” worksheet empty; or
If a target cell contain score - copy that score to a destination cell in “Dashboards” worksheet.

For example, two cells in “Dashboards” worksheet (B4 and C4) that are used to construct a scatter dashboard for Region A reads:
Cell B4: =IF(Data!B4="","",Data!B4) – this data is used for horizontal axis
Cell C4: =IF(Data!C4="","",Data!C4) – this data is used for vertical axis

The problem occurs if the data for horizontal axis is currently unavailable.

For example, if cell B4 in “Data” worksheet is currently empty corresponding cell B4 in “Dashboards” worksheet will also be empty due to the formula outlined above. However, a scatter dashboard will plot value 1 on the horizontal axis.

If, however, the data for vertical axis is not available in “Data” worksheet a scatter dashboard will plot value 0 on the vertical axis.

Please see attachment for detailed explanation.

I have 2 questions here:

Question 1:
Why value 1 is plotted if the cell is empty in one case and value 0 in the other case; and

Question 2:
Is there better way to write formulas in “Dashboards” worksheet – something like:

If one of the two values is missing (either Score A OR Score B in “Data” worksheet); then

A corresponding destination cell in “Dashboard” worksheet should be read as EMPTY by a scatter dashboard (rather than plotting O or any other number value). In this case the dashboard will remain empty itself until both values are available.

[Pete_UK]

In B4 of your Dashboards sheet, you can change the formula to this:

=IF(Data!B4="",NA(),Data!B4)

this will return #N/A to the cell, but this error will not be shown on the graph. You can do the same for your other formulae, i.e. instead of returning "" if the cell is empty, return NA() instead.

Hope this helps.

Pete