Ignoring 0 values in the series data
How can I get my chart to ignore values which are zero in the series data and
not show these?
replace zeros with =NA()
mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=38003
You can delete the formulas that are producing zero, or replace them with
> How can I get my chart to ignore values which are zero in the series data a...Counting Unique Values within a Group
I have a report that lists students by grade and the amount of money donated
by that student's family. If the parents are divorced, the student's name is
listed twice, once for each parent. I want to count only the unique student
record in each grade. DCount is giving me the count for the whole school
regardless of what group detail i put it in. How do I count only the unique
records within each grouping?
Thanks in advance.
I would attempt to create a totals query that groups by whatever and counts
something. Add this totals query to your report's record source...How to delete rows automatically with values zero out?
I have a spreadsheet with hundreds of rows. Many of them were entered
at the beginning and then were reversed out. I got this spreadsheet
from our ERP system. It is just like this:
Part# Quantity Value
013-001 1 $1.00
013-001 -1 $1.00-
013-002 5 $25.00
013-007 3 $120.00
013-007 -3 $120.00-
022-001 12 $17.00
041-009 7 $251.00
041-009 -7 $251.00-
052-061 10 $30.00
How can I delete those rows autometically with the quantity were zero
out. I don't want them show up on my spr...Macro to insert to move values of a cell to next cell in the same row
I would like to know the Macro code for Moving the Values of a cell to
next cell based upon values in a particular cell.
Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day)
Based upon date in Col A i want move the value in col b and c to the
next cell.Basically this for aging the items in the work sheet
Can anyone pls give the code for this query.
...Can you expand/collapse columns based on a reportitem/field values
I have a report that is driven by accounting periods. The columns are 1, 2,
3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown
is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want
to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So
the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible?
Thanks in advance
I created the following dataset:
SELECT 'Q1' AS Expr1, 1 AS Expr2, 500 AS Expr3
UNION ALL SELECT 'Q1' AS Expr1, 2 AS Expr...How to have one year value added to another cell
I have a cell where I date foramtted fill in the date.
How to have on another cell the date + one year automatically filled?
"AA Arens" <email@example.com> wrote in message
> I have a cell where I date foramtted fill in the date.
> How to have on another cell the date + one year automatically filled?
> A1 02-04-06
> B1 02-04-07
>...SQL to get the lowest unique value from table
I'm looking for some SQL to get the lowest unique value in a table:
3 is the lowest unique value. I would need to return the ID.
"Lee" <firstname.lastname@example.org> wrote in message
> I'm looking for some SQL to get the lowest unique value in a table:
> ID BID
> 1 1
> 2 1
> 3 1
> 4 2
> 5 2
> 6 2
...valid FirstName value
Anyone know how to search for a valid FirstName in a field?
I don't want junk like this:
Any assisatance will be greatly appreciated.
Have a nice evening.
How about "Gert-Jan"?
"Himansu" <email@example.com> wrote in message
> Hello everyone,
> Anyone know how to search for a valid FirstName in a field?
A union query I developed has suddenly stopped working. The syntax
SELECT * FROM [myQuery - Part 1] UNION SELECT * FROM [myQuery - Part
Pulling up each query individually works fine. When i run the above
union query I receive "Invalid use of Null". The number and order of
the columns in each query are identical. Access 2003 here.
Any ideas what could be the problem?
I suspect it is the VBA code that you are using around the query, not the
query, that stopped working. To confirm, does your query is executable from
the query editor?
If it does not, you also confirm t...Originating Values
I have been workign with GP for a while, but am, not really clear on the
difference between Originating Price and Price, Originating Cost and Cost,
etc. Can anyone clue me in, I haven't been able to find a good explanation.
I believe this has to do with Multicurrency. 'Originating' is the
originating currency. If you are not using Multicurrency the Originating
field will be the same as the other field. If your making any changes to the
windows using Modifier be careful because these two fields are 'stacked' on
top of one another.
"Chris Hornun...adding values of cells
I am trying to use a formula or function that will look at a cells say column
A check for a condition, if true, then add the value of another cell that is
on the same row to another cell. Yes I know that may be difficult to
understand so let me give you an example.
A B C D
2 dvd 25.23
3 cd 12.25
4 dvd 25.23
5 cd 12.25
6 bat 19.25
ok so what i want to do is look at column A, and look for any cells that
have dvd and add the corresponding value from column D to cell B1. So in
this case we can see the cell A2 and cell A4 is dvd so I want to add cells D2
and D4 and that...Copying Specific Values From Sheet
Is there a way to copy specific rows from 1 sheet to another?
For instance I have a workbook containing 4 tabs. These are labeled "raw
data", "Sheet A"," Sheet B", and "Sheet C". On the "raw data" sheet there
are 3 columns. One of the columns is titled "Project". In there the values
are A, B, C or D (and maybe E someday). I would like to say "if the
"Project" column contains and "A" copy everything on that row to "sheet A".
Basically I want to take all t he information that has a proje...Sorting Alpha-Numeric Values
I would like to sort data that have aplha numeric values. When I use the
sort command, the numbers wind up getting sorted by place values instead of
the actual value of the number, kind of like alphabeticaly except with
instead of letters.
For instance, I would like to these numbers sorted like this:
Instead, Excel sorts them like this:
It appears that the suffixed alpha characters alter the way the numbers
would normally be
Any ideas how I can sort them more logically/numericaly?
...Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below:
Column A Column B
I want to poplulate a column (for exampel column A on a new sheet),
where the values in column A will be repeated as many times as the
value in column B
Thus the first 24 rows will say Apples, the next 36 rows will Pears,
I need a formula that recogonizes that when it gets to row 25 that it
should no longer need to copy Apples, but then switch to pears.
This might sound like an unusual request but if I can grasp a way to
do this I can create the table I need and populate the rest...converting checkboxes to a text value
I have an existing database that employs checkboxes. I'd like to create a
report that displays those boxes as "yes/no" text values, so that I can
export them to an .rtf format, and have them actually show up.
I'm a novice, so any help is greatly appreciated.
The purpose of this is to be able to email specific pages of the report.
I'm also open to other ideas for making this possible, if I'm going about
this the wrong way.
Thanks in advance.
> I have an existing database that employs checkboxes. I'd like to
> create a report that...different colors for x-Value axis
I want to use different colors for X-Values in X-axis of a chart. Is it
I-1, I-2 black
II-1, II-2, II-3 grey
III-1, III-2 red
V-1, V-2, V-3 green
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan
The best solution is that, Excel brings the formatting of x-axis value from
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan
> I want to use different colors for X-Values in X-axis of a chart. Is it
> I-1, I-2 black
> II-1, II-2, II-3 grey
> III-1, III-2 red
> IV pink
>...Conditional Max value
i have 2 columns and i'm trying to calculate the conditonal maximum from
These are the columns :
and i try to find a formula that gives the maximum in column one, where
column 2 has value 7
in this case this would be 58
Use the following array formula:
Change the range references to match your data.
This is an array formula, so you must press CTRL+SHIFT+ENTER rather than
just ENTER when you first enter the for...Error Msg: Parameters values not valid
When I attempt to drag an e-mail message to the outlook
bar task folder to create a task I get an error message:
"Could not complete operation. One or more parameter
values are not valid".
The denied funtionality is useful shortcut. Any ideas why
I have lost this shortcut to create tasks?
Thank you for any suggestions - Dee
Can you create new, blank Task items in your Tasks folder? Try opening your
Tasks folder and selecting Save Task Order from the Actions menu.
PATRICK REED [Outlook - MVP]~~~~~~
-Microsoft Certified Professional (MCP)
-Have you checked http://www....a2=a1-32 how do i hide -32 until I have a value in a4
the box a2 contains the formula =a1-32. how can i get rid of -32 that is
shown in all the boxes the formula applies to a2 down to k2
I think you have columns and rows mixed up.
You cannot have A2 "down to" K2
My best guess to enter in A2 =IF(A1="","",A1-32)
Copy across to K2
Gord Dibben MS Excel MVP
On Thu, 26 Mar 2009 13:13:02 -0700, jackdaw999
>the box a2 contains the formula =a1-32. how can i get rid of -32 that is
>shown in all the b...HOW DO I ADD NON-NUMERIC VALUES
HOW DO I ADD NON-NUMERIC VALUES?
Without shouting (i.e., using ALL CAPS), please explain what you mean by
"add non-numeric values".
Addition normally requires that values be numeric.
In article <4DB070A2-B74F-44EA-8FDF-A49C20AE691A@microsoft.com>,
DUKE <DUKE@discussions.microsoft.com> wrote:
> HOW DO I ADD NON-NUMERIC VALUES?
Can you give examples of the "non-numeric values"?
(btw, we avoid typing in all capitals
in the newsgroups.....it indicates shouting)
Microsoft MVP (Excel)
(XL2003, Win XP)
"DUKE...Returning one of two values in Microsoft Excel 2003
I am trying to allocate values across various headings in my worksheet.
In, say, A2, data is input via drop down box. Depending on the value of
this entry, and on entering a value in B2, B2's value is allocated to a cell
under a general heading via the formula =IF(OR(A2="value1", A2="value2",
A2="value3"), B2, " ") This is an income allocation spreadsheet.
My problem comes in my outgoings. Once again data is input via drop down
box and depending on these values I have to allocate my outgoings whether the
currency value is input in cell B2 o...Hlookup with Merged cell as look up value
Hopefully someone can help me out on this one. I have a spreadsheet that
looks similar to this:
2/15/2010 3/15/2010 . . .
Site# INV VALUE INV VALUE
1 34 332 40 440
2 47 250 47 250
3 55 476 36 420
Total 136 1058 123 1100
I am trying to use a Hlookup to return both the Totals for both the INV and
Value in a compainon sheet. The date cells are merged over the INV and VAL
Colums so my Hlookup fo...Excel does not display entered values correctly
I am currently running Excel 2000 on WinXP Pro. Excel does not display the
correct values in cells when they are typed. The program arbitrarily assigns
a decimal value to whatever number is typed. For example, if "1111111111" is
entered, it is displayed as "11111.11111" I have gone through cell
formatting, detect and repair and other options, but none have worked. The
program use to work fine. It started this recently and it won't go away. If
it helps, when a number is typed in another program, i.e. notepad, and copied
and pasted into Excel the value displ...Hi Blinking/Flashing Text in cell based upon date value
I need a project completion sheet where ..
the contents (TEXT) of the particular cell is to flash/blink upon some
will any one respond pls
Just a personal view but if I opened a workbook and it blinked at me I would
close and delete it but if you must then have a look here.
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
> H...Asking Excel Solver to use binaries, but selects other values
I am trying to get a linear program solved using solver. The values that
Solver needs to enter are contrained as binary, but excel still try to find
values that are neither 0 or 1.
I assume that when you say it is selecting values that are neither 0 or 1,
they are values that are numerically close to 0 or 1 (such as 0.99998). If
this is the case, you may want to consider the following approach:
1. Have the Solver constrain your decision variables as binary.
2. Have another set of cells which uses the decision variable to produce a
result. When the underlying decision variabl...