Chart to display only bars for values that are > 3 & blank cells
 

Hi! I have a table to count how many times a place comes up in one of the
worksheets. Sheet 1 contains the data, sheet 2 contains the table. It looks
like this:


COLUMN C COLUMN D
Albany 3
Alexandra Park 0
Ashmore 4
etc...

Column D uses the following formula:
(for D4) =COUNTIF('Sheet1'!AE:AE,Sheet2!C4)
(for D5) =COUNTIF('Sheet1'!AE:AE,Sheet2!C5)
etc...

I need to make a bar chart of this information, but displaying a bar only if
the value in the D cell is equal to or more than 3.

Is there a way of doing this?

I tried a few ways, one being the use of IF formulae to print the name and
number only if the value was more than 3. If it was not, it prints nothing,
as used in the following:
COLUMN E uses
=IF(D4=3,"Yes","No")

COLUMN H uses
=IF($E4="Yes",$C4,"")

COLUMN I uses
=IF($E4="Yes",$D4,"")

So after all that, if the value in D4 (for the place C4) is greater than or
equal to 3, I get the place name in H4 and the value in I4.
If the value in D4 was less than 3, H4 gets"" and I4 gets"".
So it looks blank.

I then try to sort these columns to put blank cells at the bottom, and any
places that have a value greater than or equal to 3 at the top.
BUT......

It doesn't do it. I guess although the cell LOOKS blank, it actually still
contains a formula, and so it won't go to the bottom as a blank cell would.

I am well and truly stuck! Does anybody know a solution please?

Many thanks, Neil Goldwasser

You could apply a filter to column D to show only values =3. Then chart
will only show the open rows

"Neil Goldwasser" wrote:

Hi! I have a table to count how many times a place comes up in one of the
worksheets. Sheet 1 contains the data, sheet 2 contains the table. It looks
like this:


COLUMN C COLUMN D
Albany 3
Alexandra Park 0
Ashmore 4
etc...

Column D uses the following formula:
(for D4) =COUNTIF('Sheet1'!AE:AE,Sheet2!C4)
(for D5) =COUNTIF('Sheet1'!AE:AE,Sheet2!C5)
etc...

I need to make a bar chart of this information, but displaying a bar only if
the value in the D cell is equal to or more than 3.

Is there a way of doing this?

I tried a few ways, one being the use of IF formulae to print the name and
number only if the value was more than 3. If it was not, it prints nothing,
as used in the following:
COLUMN E uses
=IF(D4=3,"Yes","No")

COLUMN H uses
=IF($E4="Yes",$C4,"")

COLUMN I uses
=IF($E4="Yes",$D4,"")

So after all that, if the value in D4 (for the place C4) is greater than or
equal to 3, I get the place name in H4 and the value in I4.
If the value in D4 was less than 3, H4 gets"" and I4 gets"".
So it looks blank.

I then try to sort these columns to put blank cells at the bottom, and any
places that have a value greater than or equal to 3 at the top.
BUT......

It doesn't do it. I guess although the cell LOOKS blank, it actually still
contains a formula, and so it won't go to the bottom as a blank cell would.

I am well and truly stuck! Does anybody know a solution please?

Many thanks, Neil Goldwasser