Free Microsoft Excel 2013 Quick Reference

chart without "0" and blank

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 am using the vlookup function and want the result to show 0 if the parent cell is in fact 0 and blank if it is blank. Both are viable answers but when I use

I get 0 for every blank and 0.  Furthermore, when I use

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

Excel 2007

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

I have the conditional formatting formula =COUNTIF($C$2:$C$4177,C2)>1 to highlight duplicates in column C. The condition is found in cells C2:C4177.

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.

Hi,
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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


does anyone know how to leave cells containing #DIV/0! and #N/A blank (just to tidy up a worksheet)? thanks in advance!

I have several charts that I look at every day. They read data from a daily cumulative file of operations data. The charts read the daily inout file and create the chart without me having to do anything because the chart looks for data in cells for every day of the month. This works fine on the last day of the month but before the last day the charts graph a value of zero for days that have not yet happened. Is there a way to tell a Line Chart to ignore zero values? I don't want to have to change the chart every day to look at one more row of data and I don't want to delete the formulas in my input files - they are complicated formulas that show a blank when there is no data yet.

Hello,

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

Hi,
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

Hello

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.

I want to chart productivity of employees. The shart is rather simple, an efficiency rating on the Y axis and time one the X - generally two week intervals, but just as easily and maybe more simply for this lets just say days of the month. I don't want to have to go in and adjust the series every day for the additional day, but then if I take the entire month of days into the series, all the ones beyond the date of last data entry are 0 and cause that crash at the end of the table, and highly distort any trend line superimposed.

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!)?

Hello. This is my first post and I have been racking my brain for several days trying to figure out a way to capture values greater than zero in a column and then populate an adjacent column with the results without leaving any blank cells. I would also like the formula to be dynamic such that it automatically updates with new data that is entered into my original column. Here is a basic example of what I am looking to accomplish.

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.

Function to compute average without high and low values

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 ...

Can someone please help me ove this hurdle?
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 need to figure out if a cell is actually blank. If it has a zero it it, I need to do something take a different action. How?

I tried using
Code:
and

Code:
 
    If Range("b3") = 0 Then Range("b2").EntireRow.Delete
Both 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

It's been awhile since I've had to use Excel and I am trying to do a chart as follows and it's just not working for me. Any help would be greatly appreciated.

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

How can I use “countif” to count BOTH zeros AND blank cells as zeros?
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.

Hi Excel-lent People,
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!!!

please see the attached worksheet.
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.

Hi,

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 Sub
Any help is appreciated.

Thanks

Hello,

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

Hi-

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 = )

Hey,

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 have a worksheet that has Haul Cycles for a mine, with a timestamp for when the load was started by a shovel. Shift change is at 7am and 7pm each day, all data is recorded in 24 hour format.

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.