How to ignore #NA in subtotals?

I have several data tables of identical layout that are updated daily, the 
first two manually and the others by preset formula that calculates from the 
first two.  If a zero or no data are input into cells in the first two then 
the formula of the others will return #NA, this is intentional because charts 
are plotted from the latter tables and #NA prevents zero's being plotted all 
over it for data that has not been entered yet (zero is a valid in negative 
and positive data entry only when entered). 

Each table has a SUM total, but, if #NA exits in any one of the cells 
totaled the 
SUM is returned #NA.  How can I overcome this?

0
LeeIT (6)
3/2/2005 5:53:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1772 Views

Similar Articles

[PageSpeed] 50

One way is to use
=if(isna(your_function),"N/A",your_function)
intead of allowing the #N/A

On Wed, 2 Mar 2005 09:53:02 -0800, Lee IT
<LeeIT@discussions.microsoft.com> wrote:

>I have several data tables of identical layout that are updated daily, the 
>first two manually and the others by preset formula that calculates from the 
>first two.  If a zero or no data are input into cells in the first two then 
>the formula of the others will return #NA, this is intentional because charts 
>are plotted from the latter tables and #NA prevents zero's being plotted all 
>over it for data that has not been entered yet (zero is a valid in negative 
>and positive data entry only when entered). 
>
>Each table has a SUM total, but, if #NA exits in any one of the cells 
>totaled the 
>SUM is returned #NA.  How can I overcome this?

0
3/2/2005 6:25:16 PM
I usually use two columns when I do this--one for the real values and one to be
used for charting (with the #n/a's).

But you could do something like:

=sum(if(isnumber(a1:a30),a1:a30))

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)



Lee IT wrote:
> 
> I have several data tables of identical layout that are updated daily, the
> first two manually and the others by preset formula that calculates from the
> first two.  If a zero or no data are input into cells in the first two then
> the formula of the others will return #NA, this is intentional because charts
> are plotted from the latter tables and #NA prevents zero's being plotted all
> over it for data that has not been entered yet (zero is a valid in negative
> and positive data entry only when entered).
> 
> Each table has a SUM total, but, if #NA exits in any one of the cells
> totaled the
> SUM is returned #NA.  How can I overcome this?

-- 

Dave Peterson
0
ec357201 (5290)
3/2/2005 7:47:50 PM
Reply:

Similar Artilces:

how do i make pie chart ignore data labels where value is zero?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a pie chart tracking the amount of time spent in a day. I have lots of possible categories and i certainly don't use them all each day, and the chart shows a bunch of overlapping labels in between slices if their value is zero. Does anyone know how to make the chart exclude these labels? <br><br>Thanks in advance! On 4/7/10 1:02 PM, Steppenwolf@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel I have a pie chart tracking t...

labels for subtotals
Hello: I'm going to create an Excel macro that, among other things, subtotals a column of numbers based on column B. Column B contains account numbers. (So, the macro will sort by column B, first.) I'd like for the row that shows the subtotal to have a distinct label. The label essentially will come from column C. Column C contains the account number description. This subtotal label will make it easier for the end user to understand what is being subtotaled. Is it possible to program a subtotal "label" in Excel? childofthe1980s On Feb 21, 1:01=A...

Subtotals #2
When the value in column A changes, I need the amount- subtotals for each of the values in columns B and C. (See sample spreadsheet below For example, for "687223007-5", I need: For "19-038": $ 388.89 For "68-1366": 30.36 For "68-1378": 3.51 For "68-1856": 12.72 For "68-2232": 332.91 For "68-4115": 353.20 (Keep in mind that, for the value in column A, the number of entries in columns B and C may range from 1 to ???). Gary A B C ...

Subtotal data excluding negitive numbers.
I have a spreadsheet contains lots information, each one linked to some other spreadsheet. In column =93A=94 some numbers are positive and some are negative. What I want to do is to subtotal(9,A3:A100) to show the total numbers when I filter something out. While I don=92t want these negative numbers to be calculated. I knew if I add a new column can resolve this problem, but if I the spreadsheet does not allow me to do that change, how can I put kind of formula in one single cell to show the subtotal result without adding the negative numbers? I have tried sumproduct but result only ...

#NA in remote reference formula
I have a budget summary report that pulls multiple names from several different pipeline detail reports. I have 4 cells that return #N/A when cells are updated i nthe budget summary report. If I open the actual pipeline detail report file though, then the correct name is populated on the budget status report. I have to do this each time though. There are a multitude of other names that are updated from the various pipeline details files with no problems nor without having to open the respective files. I have checked the cell formatting in both spreadsheets, copied the formula, del...

Chart ignoring formulas but only plotting formula results
I am creating a line chart where the source data is the result of an =IF formula. How do I get the chart to only plot the cells where there is a result? Currently the chart is plotting a zero value for all the cells where the formula does not yet show a result. If( something, formula, NA() ) The otherwise blank cells now have #N/A in then (you can hide these with Conditional formatting if they bother you) Charts ignore these values best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JD" <JD@discussions.microsoft.com> wrote in message new...

Decimal key on keypad is ignored
In Excel 2000 with fixed decimal 2 places in the setup, I could enter 10+. and the cell would read 10.00. In 2003, the decimal is ignored and I must enter 1000 to get 10.00. Is there a way to fix this change? The functionality has not changed in Excel 2003, the fixed decimal is overridden if you manually enter the decimal. I would check a couple of things 1) Your decimal separator under windows regional settings is . 2) If you are wanting to use the system separators, as above, you make sure that setting is checked in Excel Tools>Options>International>Use system settings Of...

How to get Access to ignore subform records when browsing form
I have an Access 2007 form that has a primary record in the main form that draws data from three separate tables. It's an asset database tracking photo usage; each photo has a unique ID that's the main key. A fourth table, which records uses of the photo, is on a subform, linked to the unique ID. This is a 1 to many relationship. There are about 4200 unique photos in the database, and about 8700 records in the table in the subform. I notice that after adding the subform to the form, when I attempt to browse through neighboring IDs, the table treats each subform record as a ...

Changing my functions to use subtotals?
I have 2 functions that are calculating my data correctly, except now I need to consider the autofilter. I figure using subtotal is the way to go if I want to disregard filtered out rows. Also, I plan on moving my data to another sheet, so it will need to reference the current sheet, which is AAT_Raw_Data. Finally, the # of rows of data will vary, but I don't think it will ever be over 10,000. I'm using Excel 2003, so I think there's an issue with using something like "A:A" in my formulas. Can someone help me change these to add in a subtotal and take into...

test post ignore
test post ignore ...

subtotal abd total are incorrect in SOP
In Sales Transaction Entry, the salesman copied from a previous order, changed the quantity to 10, the price to $120, and saved the order. There is only one line item. The extended price is $1200, but the Subtotal and Total are $2554. I check SOP10100 and SOP10200 and found $2554 in SOP10100 and $1200 in SOP10200. How did it happen? How can I fix it? Please help. Thanks. stien Run 'Reconcile Sales Documents' on the document. It should fix it. -- Microsoft Dynamics GP MVP http://ddelprado.blogspot.com "stien" wrote: > In Sales Transaction Entry, the salesman cop...

ignoring change in combo box
Hi, how can I completly ignore change in combo box if some terms are not fulfilled, ei if someone picks something in combo box I want to check if some terms are fulfilled and judging on that I shall allow change on combo box or not. If terms are not fulfilled I want combo box to stay with old value, just as nothing has happend. thanx in advance alek_mil "alekm" <alekm@discussions.microsoft.com> wrote in message news:45B68237-8726-43FC-B479-4E7A001B5F9F@microsoft.com... > Hi, > how can I completly ignore change in combo box if some terms are not > ...

Duplicates and subtotals
Each week we receive a file from our payroll system, approx 6000 rows. Contains General Ledger data by employee and GL account with amounts. We use this file to post to our GL system. I'd like to find a way in Excel to subtotal by row containing unique data. Emp Co Accnt date amount 1234 10 4000 10/17/03 100.00 1234 10 4000 10/17/03 5.00 I would like to see, instead of two lines of detail, 1234 10 4000 10/17/03 105.00 Anyone had the opportunity to do this? Thanks in advance Deanna I'd insert a new column A and use a helper formula: (as...

Incorrect bank accounts subtotal in Canadian dollars. #2
Hi Everyone I am new to using Money and I am having a problem and hoping omeone could help me figure it out. I have entered all my bank info and the individual accounts totals are correct, however when I view the accounts list screen the sub total has displays as follows: (CA$115,989.67) This negative amount is way off (should be a positive amount) and I have no idea how to fix it. If anyone knows or could give me some guidance on how this total may have came about it would be greatly appreciated. ...

Subtotal Grades
I have a list of names with grades blah blah, and have calculated their number grade. ALso, I used Vlookup and a lookup table to calculate their letter grade. NOW: Use the subtotal feature to determine the number of As, Bs, Cs, Ds and Fs. :confused: Data--->Subtotals I click some stuff in that menu, but none of it displays the number of As, Bs, Cs, Ds and Fs. Any help would be greatly appreciated. -- Triptanes ------------------------------------------------------------------------ Triptanes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33849 Vi...

Excel
Since the worm blaster virus has hit our office, I'm having a lot of problems with the subtotal function. The file hangs up then I save it and close the file - when I go back into the file, the data has been subtotaled. Does anyone know what's going on? I actually had the IT guy uninstall and reinstall excel and that did not fix the problem. Could this be related to the fact that they just installed service pack 2 yesterday?!! >-----Original Message----- >MSBlaster didn't do anything to the Office load that I >know of... this has to be unrelated to the worm. I...

How do I copy only subtotals to a new sheet?
Not including selecting each one individually. Select the range, press F5, select special and visible cells only, copy and paste Regards, Peo Sjoblom "darlinvee" wrote: > Not including selecting each one individually. Hi The way I do it is to set the grouping to display just the totals. Hit F5 and from Special select visible cells. Copy and Edit|Paste Special|Values where you want them to go! Andy. "darlinvee" <darlinvee@discussions.microsoft.com> wrote in message news:96DBD597-C934-4198-8407-370DB3369593@microsoft.com... > Not including selecting ...

Please ignore #2
Testing a posting problem -- Jim Bunton ...

please ignore this post
Oops... please ignore this pos ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

Can't use NA() instead of "" but then pb in chart with zero values
I have column that are created from the IF formula which returns the "" values if the result is false. I can't use NA() instead because then, I am using these columns for other calculations such as average. I need to use those columns also to create charts, but my lines with "" are showing zero values. I read in this forum that we can use a trick solution: to have a column using "" for the calculation and another using NA() for the graph. If I have too many coumns, is there an easier way to solve it? Thank, What is "too many" columns? In my e...

Ignore Y axis value
I have 4 facilities 901, 960, 972, and 990 that share 4 departments Pulling, Shipping, Receiving, and Stocking that are graded on 4 criteria UPH, Overtime%, IndirectLabor%, and Performance%. I want a 1 bar graph for each department that compares each faclities criteria in 4 sections. My problem is the UPH is in the thousands and the Indirect is less than 10. So, in my graph I can not see the smaller numbers in each sectioin. Is there a way to have a bar graph that only shows the values on each bar without regard to a Y axis? That's called a table. - Jon ------- Jon Pel...

Just a test. please ignore
test message ...

subtotal with sumif nested
Hi all. I'm trying to do a subtotal function with a nested sumif. =SUBTOTAL(109,SUMIF(Sum!B15:B103,"718",Sum!F15:F103)) or =SUBTOTAL(109,SUMIF(Sum!B15:B103,right(a1,3),Sum!F15:F103)) where a1 is the name that I want to test against for my criteria, and the Sum! is the sheet name, column B is my criteria range, and column F is my sum range. I keep getting an error message box, but it doesn't way what the error is. I.e., "the formula you typed contains an error" and then gives the 3 typical options for resolving it. None of which apply as far as I...

Great Plains Checkbook Register should have subtotal columns for .
It would be very helpful in the Checkbook Register had a total of the Payments and Deposits built right in. I know that there is a way to add this in Report Writer but I think that a standard checkbook register should come with this feature already built in. This would be very helpful when reconciling accounts. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion i...

Excel subtotals
I have a list that is approx 2000 rows by 8 columns (A:H) which needs to be subtotaled in Column H on each change in column G. Once the list is subtotalled it needs to be filtered to only show the rows which do NOT result in a subtotal of 0. How can I do this? Thank you, Michele You could setup the subtotals using the Data - Subtotal dialogue. Then, add a helepr column to your data (I) witht his formula: =VLOOKUP(G2&" Total",G:H,2,)<>0 Then filter on this column for TRUE. -- Best Regards, Luke M "Michele" <Michele@discussions.micro...