hello,

I have difficulty doing lines chart without graphing "0" or "blank" in the graph with MS Office 2000. I am wondering if anyone here know how, I am very grateful.

Here is the problem:

10 cells directly to graph are in sheet1, and those 10 cells data are reference to 10 cells in sheet2. If I enter values in any of 10 cells in sheet2 that sheet1 references to, it will shown identical values in sheet1 otherwise it shows blank(s)

For test purposes, I entered values in that 9 cells and leave one cell blank in sheet2, sheet1 shown the same and the graph graph the data in sheet1 treats the blank cell as "0" value.

I have tried CHART/plot empty cells as 'not plotted' and 'interpolated' but the graph still graph it as "0".

Anyone know the way around this, im greatly appreciated.

minharnold@yahoo.com

I have difficulty doing lines chart without graphing "0" or "blank" in the graph with MS Office 2000. I am wondering if anyone here know how, I am very grateful.

Here is the problem:

10 cells directly to graph are in sheet1, and those 10 cells data are reference to 10 cells in sheet2. If I enter values in any of 10 cells in sheet2 that sheet1 references to, it will shown identical values in sheet1 otherwise it shows blank(s)

For test purposes, I entered values in that 9 cells and leave one cell blank in sheet2, sheet1 shown the same and the graph graph the data in sheet1 treats the blank cell as "0" value.

I have tried CHART/plot empty cells as 'not plotted' and 'interpolated' but the graph still graph it as "0".

Anyone know the way around this, im greatly appreciated.

minharnold@yahoo.com

- Vlookup to show 0 as 0 and blank as blank
- Conditional Format Ignore "0" and blank
- Exclude "0" and blank cells from duplicates
- Excel 0 and Blank fields.
- Replicate Charts Without Copy/paste
- Leaving #DIV/0! and #N/A blank
- How to do chart without showing zero values
- Conditional format with 0 and blank cell
- Replicate charts without copy/paste
- Chart and 0-values -?
- How to chart a series and ignore values of 0
- Capture values over 0 and populate results in another column
- Function to compute average without high and low values
- Charts: Distinguishing 0 value from no data in a chart
- How differentiate between zero and blank?
- Chart with Range and Percentages
- How can I use “countif” to count BOTH zeros AND blank cells as zer
- How to Differentiate between a Student's Score of "0" and a Score of Null?
- Validation list without duplicates and blanks
- Ignoring headers and blank lines while importing
- Data Validation and Blanks in List
- Need help w/ Formula please!!!
- Code to count and then reset to 0 or blank out.
- Find value closest to another value using type 0 and 1 matches at the same time

I get 0 for every blank and 0. Furthermore, when I useI get blanks for every blank and 0. What is the easiest way to ensure that the cell shows what the parent actually is?

Column AM6:AM57 contains various monetary numbers.

I want a conditional formating formula or method that highlights the lowest 5 numbers, but ignores "0" and blanks.

What would that formula be?

Regards, Gary

How do I get it to exclude the condition for a blank cell, or for a cell value of zero? When more than one zero is in the range, then the zero's and all blank cells get highlighted with the format condition (highlighting the cells red).

I have tried to create another condition of [Cell value is equal to 0] and [Cell value is equal to ""], and set the format to the default white background, but this doesn't work when there are duplicate zero's in the range.

I am working on a spreadsheet that is relatively simple, but I need to have

empty cells and cells input with a 0 to give a referenced cell different

values. I have used the following formulas to do this for a cell with a 0

in it but I have found that this interprets an empty cell the same as a cell

with a 0 in it.

Cell C2 Ex.1: =IF (A2+A3=0), " " , SUM (A2:A3)

RESULTS: If I enter a 0 in A2 or A3 or if I leave A2 or A3 empty the

returned value will be blank.

Cell C2 Ex.2: =IF (A2+A3= " " ), " " , SUM (A2:A3)

RESULTS: I get the same results as Ex.1.

A

B

C

1

Input Data

Output Data

2

3

00

What I need is: IF (A2 AND A3= Blank ) THEN return blank, ELSE SUM (A2:A3)

[even if the value(s) entered into A2 and/or A3 is/are 0 or any combination

of zeros and blanks, I need it to return a 0.]

Thanks for any input.

I am trying to rebuilt a chartsheet without using copy/paste. I have chartsheets with 2 or more charts on it. I add a new chartsheet , then add a new chartobject and then I want to copy the same series (name, xyvalues, values). Unfortunately it doesn't work. Can someone help me?

VB:ReplicateCharts() Dim Cht As Chart Dim NewCht As Chart Dim ChtObj, NewChtobj As ChartObject Dim SrSerie As Series Dim ns As Series Dim x As Variant If ActiveWorkbook.Charts.Count > 0 Then For Each Cht In .Charts Set NewCht = .Charts.Add() NewCht.PageSetup.Orientation = Cht.PageSetup.Orientation For Each ChtObj In Cht.ChartObjects For Each SrSerie In ChtObj.Chart.SeriesCollection Set NewChtobj = NewCht.ChartObjects.Add (ChtObj.Left, ChtObj.Top, ChtObj.Width, ChtObj.Height) Set ns = NewChtobj.Chart.SeriesCollection.NewSeries ns.Name = SrSerie.Name [I][COLOR=red]ns.XValues = SrSerie.XValues this Is were i get error[/COLOR][/I][COLOR=red] [I]ns.Values= SrSerie.Values[/I][/COLOR] Next SrSerie Next ChtObj Next Cht End With End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am trying to conditional color format cells with the condition:

if cell content = 0 then color red, and if blank no background color?

any suggestions

plettieri

I am trying to rebuilt a chartsheet without using copy/paste. I have chartsheets with 2 or more charts on it. I add a new chartsheet , then add a new chartobject and then I want to copy the same series (name, xyvalues, values). Unfortunately it doesn't work. Can someone help me?

Sub ReplicateCharts()

Dim Cht As Chart

Dim NewCht As Chart

Dim ChtObj, NewChtobj As ChartObject

Dim SrSerie As Series

Dim ns As Series

Dim x As Variant

If ActiveWorkbook.Charts.Count > 0 Then

For Each Cht In .Charts

Set NewCht = .Charts.Add()

NewCht.PageSetup.Orientation = Cht.PageSetup.Orientation

For Each ChtObj In Cht.ChartObjects

For Each SrSerie In ChtObj.Chart.SeriesCollection

Set NewChtobj = NewCht.ChartObjects.Add (ChtObj.Left, ChtObj.Top, ChtObj.Width, ChtObj.Height)

Set ns = NewChtobj.Chart.SeriesCollection.NewSeries

ns.Name = SrSerie.Name

ns.XValues = SrSerie.XValues this is were i get error

ns.Values= SrSerie.Values

Next SrSerie

Next ChtObj

Next Cht

End With

End If

End Sub

I have one question considering about excel charts..

Is it possible to create Excel chart, without 0-values, by selecting all data A1:B2000 if my data include many 0-values (in column B), f.e.x

A.............B

Car........count

Toyota.....3

Honda......0

Opel........0

Lada........2

Chevy......1

I would like to create chart which includes only those cars which count is not 0.

-> Chart: Toyota3, Lada2, Chevy1.

There is allso picture to help understanding -?

Can anyone have an idea..? My Excel is version 2002.

Is there a way to tell it instead of $A$1:$A$31 to go from $A$1 to the end of the entries >0 (I am using those cell entries as a matter of example, again trying to simplify things!)?

Column G

0

0

99

0

94

0

114

0

91

86

111

Column K

99

94

114

91

86

111

I have attached a sample spreadsheet for your review. Any assistance would be greatly appreciated.

and then double-click the selected text to go to the "About user-defined functions" topic. Back to the top. Microsoft Excel 7.0 For more information about using custom functions ...

I am charting datain a bar graph where some data is 0% and other places it is No data. I need to distinguish betweenthe two of these in my chart.

The chart does not plot anything if there is 0% or if there is no data to chart.

HELP!

I tried using

Code:

and

Code:If Range("b3") = 0 Then Range("b2").EntireRow.DeleteBoth ended up deleting row 2, when it shouldn't have been deleted bacause row 3 was blank.

I'm sure this is really a dumb question that's been answered a bunch of times before, but I can't seem to figure out how to search for it, since zero and blank appear in so many posts...

Thanks!

Jennifer

Values

$0 - $8,928 = 0%

$8,929-$34,397 = 21.2%

$34,398-$37,178 = 24.15%

$37,179-$68,794 = 30.65%

$68,795-$74,357 = 33.1%

And so on and so on.

I need a line chart showing percentages on the Y access and dollar values on the X access and what I am getting is

Percents in the 1,0000 and the dollar value is coming up as $1, $2, etc.

Hope you can make sense out this.

Thanks ~ E

Wondering if I am unaware of a formula that exists that is less messy than my

formula below to achieve this answer.

I was going to do this: =COUNTBLANK([range]) + COUNTIF([range], 0)

D.

I am trying to average assignment scores in a gradebook. Assignment

scores are entered into cols starting with AO. On row 10 are the max

points possible for assignments (for instance, AO10 may be 20 for 20

points poss). Students begin on row 13, and, if students do not need

to do an assignment, I will leave their cell blank for that

assignment. I cannot solve how to write a formula that will exclude

null cells when max values are still present in row 10. Here's what I

have:

Formula in O13 to compute Assign. Points Possible:

=SUMIF(AO13:AO13,">0",AO$10:AO$10)

Formula in P13 to compute Assign. Points Earned:

=SUMIF(AO13:AO13,">0",AO$13:AO$13)

Formula in Q13 to compute Assign. % Earned:

=AVERAGE(IF(($AO13:AO13"")*($AO$10:AO$100),$AO 13:AO13/$AO$10:AO

$10))*100

**Here's the key--the student in row 13 may get a 0 on an assignment--

AO13 would = 0 and then Q13 would then = 0.00, which is the same

result as if the student didn't have to do the assignment. Other

formulas will use this value of "0"

If AO13 is left blank, how can this formula produce the desired

result?

I would like to simply figure each assignment percent for each

student, then average all of the assignments for a student during a

quarter.

Thanks a bunch!!!

Column A contains some data.

Cell $B$1 has a drop-down box.

I want the drop-down box to show only the unique records in column A and skip the blanks.

I have inherited a spreadsheet that imports a file of raw data and splits it up accordingly. The problem is that the raw data can have multiple header rows and blank lines depending on the data dump and the blank lines are preventing the entire data to be imported. My macro seems to stop importing once it hits the blank lines.

If I take out the blank lines manually then I am left with a couple of header rows that end up being a part of my data which is not what I want.

The application that creates the raw data has a limit of 4096 lines/page and then it automatically inserts 2 blank lines and then the header row and a line of dashes(-).

I need need help to ignore these lines.

Here is the code that deals with cleaning up the input file.

Private Sub Correct_The_File(src_filename_and_path As String, dest_filename_and_path As String) Dim temp_string As String Dim filebytes() As Byte Dim byteindex As Long Dim maxbytes As Long On Error Resume Next Open src_filename_and_path For Binary As #1 ReDim filebytes(LOF(1) - 1) Get #1, , filebytes Close On Error GoTo 0 ' Remove the characters you don't want (actually we will replace with a space to keep your fixed width formating correct) maxbytes = UBound(filebytes) For byteindex = 0 To maxbytes - 1 ' Don't bother with the last byte as it will have no effect anyway If filebytes(byteindex) = CR And filebytes(byteindex + 1) <> LF Then ' All CR should be followed by a LF, otherwise they are faulty data filebytes(byteindex) = SPACE_CHR End If Next byteindex 'temp_string now contains the whole text file, write it back to a file Open dest_filename_and_path For Binary As #1 Put #1, , filebytes Close End SubAny help is appreciated.

Thanks

I have been looking on the boards to the resolution for the question I

have but I only keep coming accross the same resolution for something

similiar but not what I am looking for. So hopefully someone can help.

I am trying to create a Data Validation list in Excel but here is the

situation. I have 3 values for instance, value 1 is at A1, value 2 at

A6, value 3 at A11. I want to create the list out of these values but

not have to list them on another sheet without blanks between them.

However, I cannot get the Data Validation list to have just these three

values in it without showing the blanks in between them. I know I

could type in a manual list instead of having a named reference, but

there are actually more than just 3 values, this is just an example. I

have tried just creating a named list with only the three cells

referenced and that doesn't even populate the drop down. So how can I

get these three values to show up in a Data Validation list without the

blanks in the drop down between them??

Thanks in advance for your help,

Ron

Could someone please help-

I am doing a finacial spread sheet. Looking for a formula that allows me to add in new columns without having to go back and change the formula, by adding additional columns.

I started out with chart numbers that vary like 5000, 5002, 5530, 5532. These are the only numbers I am using. Now what I would like is to find a formula that works with finding the amount each chart number adds up to. (so if I were to add in another column with a matching chart number I would get the sum and not have to change anything.

Ex: (each in its own column)

Chart #: 1 2 3 4 2 3 3 4 2 4 3 3 3 3 4 3 3 3

and under the chart #'s are the amounts that match up to each additional chart number. some are left blank.

Lets say chart# 1=$0 and chart 2= - 1 and chart 3 varies in amounts, so I would need the sum of each chart #.

Chart# 1 ---- need formula that Matches the chart # to give me a SUM for each and works with adding in additional columns by using the same chart #'s-

Chart# 2

Chart# 3

Chart# 4

Would like to add additional columns later without having to change the formula. Does anyone know how I go about doing this?????? If anyone could please help. I greatly appreciate it! Thanks = )

Not sure if this is possible.

I have a simple count function in D3 - every time there is an entry in A5:A150 it counts up by 1.

In A5 thru A150 will be a date, is there a way to have D3 reset to 0 and start counting again once the date is not the same as the cell above?

Is there then a way to capture the number in D3 prior to it reseting to 0 ?

I am trying to find out what time each shovel started loading at after 07:00 and 19:00 each day, by using the load start timestamp. The data comes in with all haul cycles for all shovels for the whole mine for a multiple of days. I have to match a shovel, or "Loading_Unit_Ident" with a "Start_Shift_Date" and a "Start_Shift_Ident". The shift date is in the mm/dd/yyyy 0:00 format, shift ident is for dayshift and nightshift.

The big problem here is I want the formula to return the closes value after 7:00 or 19:00, but that is a type 1 match, and in order to match the loading unit, shift date, and shift ident, I need to use a type 0 match.

I have attached a sample with shortened data for only a few days. I have spent a long time trying to figure this out, and I am drawing a blank on how to use both type 0 and type 1 matches at the same time.

I am trying to input the formula into the "Report" worksheet. The haul cycles are on the "Haul_Cycles" worksheet.