Graph: conditional X-axis values

[Clariinc]

Hi!

I have a set of non-static values that is setup based on certain value criteria:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]value 1[/TD]
[TD]value 2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6%[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]-10%[/TD]
[TD]8%[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

Basically I have a database, but this table only shows the values of interest. In this case it is to display both columns of data if the number in 'Value 1' exceeds +/- 5%. So as long as the cell in 'Value 1' meets this criteria, the corresponding number in 'value 2' will also be displayed.

However, I would like to graph Name (x-axis) against Value 1 and 2 (Y-axis). And I have a problem because I don't want my x-axis to have the irrelevant data as labels too. In this case, I only need B & C in my x-axis.

But since its a non-static data, i.e. if the criteria was changed, it may be A, C & D that have the table populated while B is blank. Then my x-axis should only have A, C & D.

Is it possible to format my graph such that it is able to adjust the axis according to whether or not there is a corresponding value?

Thanks!!!

 

[Fowmy]

Hi, welcome to the forum !

To get the desired graph where only the relevant values will be shown, create a supporting table and enter following array formula. You also need to create three named ranges for category, series one and and series two.

1. Create the supporting table using the array formulas I have shown below.
2. Create the graph and assign the Named Ranges in the Select Data dialog of the graph


Named Ranges:

[TABLE="width: 144"]
<colgroup><col width="72" span="2" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72"]Cat[/TD]
[TD="width: 72"]=OFFSET(Sheet1!$F$3,1,0,COUNT(Sheet1!$G$4:$G$7),1)[/TD]
[/TR]
[TR]
[TD]ValOne[/TD]
[TD]=OFFSET(Sheet1!$G$3,1,0,COUNT(Sheet1!$G$4:$G$7),1)[/TD]
[/TR]
[TR]
[TD]ValTwo[/TD]
[TD]=OFFSET(Sheet1!$H$3,1,0,COUNT(Sheet1!$G$4:$G$7),1)[/TD]
[/TR]
</tbody>[/TABLE]

	F	G	H
Helper Table

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"><col style="width:72px;"><col style="width:72px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #fafafa"] [/TD]
[TD="bgcolor: #fafafa"]value 1[/TD]
[TD="bgcolor: #fafafa"]value 2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #fafafa"]B[/TD]
[TD="bgcolor: #fafafa, align: right"]8%[/TD]
[TD="bgcolor: #fafafa, align: right"]8%[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #fafafa"]C[/TD]
[TD="bgcolor: #fafafa, align: right"]9%[/TD]
[TD="bgcolor: #fafafa, align: right"]9%[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #fafafa"]#NUM![/TD]
[TD="bgcolor: #fafafa"]#NUM![/TD]
[TD="bgcolor: #fafafa"]#NUM![/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #fafafa"]#NUM![/TD]
[TD="bgcolor: #fafafa"]#NUM![/TD]
[TD="bgcolor: #fafafa"]#NUM![/TD]

</tbody>

Formeln der Tabelle

Zelle Formel F4 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),1)} G4 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),2)} H4 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),3)} F5 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),1)} G5 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),2)} H5 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),3)} F6 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),1)} G6 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),2)} H6 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),3)} F7 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),1)} G7 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),2)} H7 {=INDEX($B$4:$D$7,SMALL(IF($D$4:$D$7<>"",ROW(D4:D7)-ROW(D4)+1,""),ROW(D4:D7)-ROW(D4)+1),3)} <tbody> </tbody>

<tbody>
</tbody>