Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Neil Goldwasser
 
Posts: n/a
Default 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
  #2   Report Post  
eider
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Activating a Chart object Hari Prasadh Charts and Charting in Excel 6 August 2nd 05 07:22 PM
Bars in a chart??????? Gee Excel Worksheet Functions 0 July 15th 05 11:36 AM
Urgent Chart Assistance Brent E Charts and Charting in Excel 1 May 10th 05 09:09 AM
Urgent Chart Questions Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
How to display chart with three bars and one line? KB Charts and Charting in Excel 1 March 16th 05 09:09 PM


All times are GMT +1. The time now is 09:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"