#### Substracting numbers in non contigous cells

```Hi!
I need a formula to substract numbers in non contigous cells THAT ARE
IN ONE COLUMN and have the result in other cell, for example:

..=2E.....................A...................B

R1..................................BLANKCELL
R2..................................BLANKCELL
R3..................................15
R4..................................BLANKCELL
R5..................................BLANKCELL
R6..................60.............75
R8..................................BLANKCELL
R9..................................BLANKCELL
R10................................BLANKCELL
R11................................BLANKCELL
R12................................BLANKCELL
R13................................BLANKCELL
R14..............175.............250
R15................................BLANK
R16................................BLANK
R17................................BLANK
R18................................BLANK
R19..............50...............300

Any help will be appreciated

Thanks and regards

```
 0
4/9/2005 2:12:19 AM
excel 39879 articles. 2 followers.

1 Replies
593 Views

Similar Articles

[PageSpeed] 14

```B1==IF(ISBLANK(A1),"",SUM(\$A\$1:A1))
and copy formula drop down

```
 0
KqMeasure
4/9/2005 3:02:57 AM

Similar Artilces:

how do i get a warning for repeated numbers #2
I am working with a spreedsheet incorporating invoice numbers. How do I create a warning when i repeat an invoice number I like to use conditional formatting when it's convenient: If you have invoice numbers starting in Cell A3, try conditional formatting from A3 through as far down column A as necessary with this: Formula is: =COUNTIF(A\$3:A3,A3)>1 Format: Pattern: Bright Red Using that, the first entry of an invoice number is ignored, but all subsequent duplicates are flagged. Does that help? Regards, Ron "Kenya" wrote: > I am working with a spreedsheet incorpor...

How convert Numeric number in words
Is there any easy formula or way to convert numeric values in words? For example in cell A1 there is an amount 19549. How could it will be automaticaly converted by a formula in cell B1 as Ninteen thousand five hundred and forty-nine. Please give me an easy answer because i am not a programmer but just an excel user. Imran Murtaza There is no direct function to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob "...

how do I substract
I am new to this. I am trying to do balance sheets. I need to add the rows (which I have been able to do) then substract the 2 columns. I cannot remember how to make the cells substract. Thanks To subtract one cell from another you would enter a formula in a third cell. =A1-B1 You can also subtract ranges as in =SUM(A1:A10)-SUM(B1:B10) Gord Dibben Excel MVP On Mon, 23 May 2005 16:42:02 -0700, "kiserlouisa" <kiserlouisa@discussions.microsoft.com> wrote: >I am new to this. I am trying to do balance sheets. I need to add the rows >(which I have been able to ...

Excel page numbering in the header
In a workbook of nine worksheets, I was able to get numbering like 1 of 9, 2 of 9, etc. as advised by Ragdyer. But there are two problems: 1. The numbering in the center of the header was got by using "page 1 of ?". But the size of the fonts on the different sheets are different and I can find no control to be able to adjust these both for size and bold. 2. If I seek to do the same in the spcial header and special footer, my only choice is to use two icons next to each other--one is like a # sign, and the other two signs. No matter how I try, I cannot get these two to give pag...

cell margines and boundaries
is it posible to define cell margines, so to have boundary area that will be not used for content, just like page margins are? now, we use extra columns, left and right of actual cell to show space, but this column "overhead" is sometimes too complicated to maintain. i am looking for every new excel release, hoping to find that "cell margines" feature, but nothing ... it looks very usefull to me, maybe it is already realised [supported] on some way, just i am not aware of that? is it posible te define "custom" cell boundary line definition, like i may define cu...

Hi , This may be quite simple but as me stumped, if I do a simple formula - A1 + B1 with the forumla in c1 how do I also get the answer to appear in a different cell i.e. D1 without putting a formula in d1 Hi without using VBA no way!. You either have to put a formula in D1 - e.g.: =IF(C1<>"",C1,"") or use the worksheet_change event. I would recommend using the formula approach -- Regards Frank Kabel Frankfurt, Germany Dennis wrote: > Hi ,, > > This may be quite simple but as me stumped, if I do a simple formula > - A1 + B1 with the forumla in c1 h...

Product Numbers
A long time ago there was a listing (I believe a KB article) that provided most of the known product numbers for dictionaries in Dynamics.set file. Can any one shed some light? The specific numbers I'm trying to track down are 258 and 346. Hi Thomas: I have a Dynamics.set for GP 9.0. This file show the 258 is for Project Accounting and 346 are for Manufacturing. Hope that helps, Valentin "Thomas R. Moore" wrote: > A long time ago there was a listing (I believe a KB article) that provided > most of the known product numbers for dictionaries in Dynamics.set file...

Universal currency cell conversion
I need to convert a worksheet valued in Euro, to Australian and Singapore Dollars. How can I do this in a simpler way than taking the time to convert each cell? Thanks in anticipation Hi, - Find an empty cell and enter the conversion rate into it. - select all cells which need conversion - choose Edit, paste special, values, multiply. Regards, Jan Karel Pieterse Excel TA/MVP >-----Original Message----- >I need to convert a worksheet valued in Euro, to >Australian and Singapore Dollars. How can I do this in a >simpler way than taking the time to convert each cell? > &...

Delayed Page Numbering
I use MS Publisher 2003. How can I best delay the master page and page numbering? I want the master page and page numbering (i.e. beginning at 1) to come into affect on page 3 of the actual publication, so that I may include two sides of a front cover at the beginning. A similar thing with 2 unnumbered pages at the end for the back cover. Any help is appreciated. Jim Cook wrote: > I use MS Publisher 2003. How can I best delay the master page and > page numbering? I want the master page and page numbering (i.e. > beginning at 1) to come into affect on page 3 of the actual > p...

how do i set up a table for phone numbers
I need to make a neat phone number table, were do I start 1. Why not use Outlook? 2. You can format a cell for a phone number (I am using Excel 2003) with the Format | Cells | Special Category | Phone Number type. Just type in the digits of the phone number and the parentheses and dash will be added after you press enter "Militza" wrote: > I need to make a neat phone number table, were do I start > ...

need held for auto date calculation for adding years and substract
A1 22-03-88 Start Date (ddmmyy) A2 19-06-08 Endt Date A3 Difference betwwen two dates A4 20 years, 2 months, 27 days (=DATEDIF(A12,B12,"y")&" years, "&DATEDIF(A12,B12,"ym")&" months, "&DATEDIF(A12,B12,"md")&" days") A5 A6 Now some years to add for substraction from a4 cell A7 A8 1 years, 1 month 25 days A9 2 years, 0 months 4 days A10 1 year, 6 months 1 days A11 A12 Here I need a formula to add A8 to A10 ? A13 A14 manually it is 4 years, 8 months,0 day ...

deleting a non functioning account
I would like to close an account on Money.. yet keep the information available untill he end of the year for tax purposes. How can I move it so this account doesn't show up on my home page. In microsoft.public.money, Lstew wrote: >I would like to close an account on Money.. yet keep the >information available untill he end of the year for tax >purposes. How can I move it so this account doesn't show >up on my home page. From the Account List, right-click the account, and select Account Is Closed. Also un-check Favorite if that is checked. In the Change View on...

How do I substract numbers in range like this: Substract(a1..a10)
depends on what you mean... Maybe: =-sum(a1:a10) or maybe: a1-sum(a2:a10) Paulo wrote: -- Dave Peterson For example: to add I do this a1 1.00 + a2 2.00 + a3 3.00 + a4 6.00 (total) =sum(a1:a3) I want to do this a1 6.00 - a2 1.00 - a3 1.00 - a4 4.00 (total) =?(a1:a3) "Dave Peterson" wrote: > depends on what you mean... > > Maybe: > =-sum(a1:a10) > or > maybe: > a1-sum(a2:a10) > > > > Paulo wrote: > > -- > > Dave Peterson > Try =A1-SUM(A2:A3) Regards Rowan Paulo wrote: > For example: to add I do this > a1 1.0...

automatically change number of bars in bar chart
Hi newsgroup, is there a way in excel 2003 to automatically adjust the number of bars that are displayed in a bar chart? (and in other charts as well?) I've got a table with categories that adjusts automatically depending on inputs, i.e. there can be 5 categories, 6, 10 or whatever. now if I link the data ranges to this table then I get bars for each of the rows in this table, even if there's no data (or "") in them. This means that all the charts look awful. can this be done automatically? and a more advanced one: i've got XY charts where I draw boxes for each of th...

Automate Numbers
I am looking for advice for the best way to solve a problem. I am developing a system of telephony extensions etc at a workplace, some sites may have 200+. I am trying to automate entering all the extension numbers, rather than manually inputting them. I also have a format for the range of extensions, but don't know if Access will allow me to give an example and then create the extension for me automatically in sequence. Thanks, Lisa W. Sounds simple enough.. what is your format? "Lisa W." wrote: > I am looking for advice for the best way to solve a problem. > ...

selecting non blank columns
Hi, I was hoping someone could help me with this problem. I have a spreadsheet where each cell in the first row contain a heading. The following rows contain actual data, which may or may not be present. Is there a way to select the row containing the headings, as well as all other rows and columns that contain values. Noel, Do you mean programmatically? Try something like Range("A1").CurrentRegion.Select -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com chip@cpearson.com "noel" <noel@alavista.co.uk> wrote in message news:077c01c36de8...

custom validation with number & text
how can i write formula for custom validation for a cell like this. first 3 digit shoulg be a number, next digit should be a text then next 6 digit should be number. e.g. 126P151008 thanks in advance =AND(NOT(ISERROR(LEFT(A1,3)+RIGHT(A1,6))), ISERROR(MID(A1,4,1)+0)) the trick is that we can test if a set of characters is a number by adding zero to it and see if we get and error. -- Gary''s Student - gsnu200854 "aditya" wrote: > how can i write formula for custom validation for a cell like this. > > first 3 digit shoulg be a number, next digit should be ...

number sequence in a template
How do you continue a numbering sequence in a template like a raffle ticket? -- Maxine ...

Ecxel substraction
Hello, I am doing some Excel-work recently, and came across this "strange feature" : A 87,600000000000000000000000000000 B 89,700000000000000000000000000000 B-A 2,100000000000010000000000000000 Another one: A 84,7000000000000000000000000000000000000000000000000000000000000000000000000000000000000 B 89,8000000000000000000000000000000000000000000000000000000000000000000000000000000000000 B-A 5,0999999999999900000000000000000000000000000000000000000000000000000000000000000000000 Somebody got an idea why this simple substraction gives a wrong answer? And even better,...

How can I convert a number to english text in Excel
Is there a way to convert an certain number, to english text in Excel. For eg. An invoice total of \$1000 shoul also appear in words (Dollars One Thousand) Hi, See the following: http://longre.free.fr/english/ http://support.microsoft.com/default.aspx?scid=KB;EN-US;140704& http://support.microsoft.com/default.aspx?scid=KB;EN-US;213360 http://www.bygsoftware.com/examples/examples.htm http://www.bygsoftware.com/examples/zipfiles/num2wrds.zip Also check out the following add-in by astien Mensink http://www.asap-utilities.com/ Super utility one of the functions converts numbers to text lik...

Returning a cell value in a database.
I am creating a home building estimate program in excel and want to return the value in a certain cell by giving row and column referances. I created lists with headers to referance the rows and col. I named the database to referance it. Everything I try to do to referance the database returns an error. I have tried the Index function (array style) to retreive the data. I suspected that the row and col names have something to do with the problem so I tried formatting their cells as text only and that didn't help. If anyone can help I will send the file so you could see what I am wor...

number of characters in POP
Hello: This client wants to be able to expand the number of characters for the item description in PO Entry so that more characters will print on the purchase order. But, when I go into Modify Current Window, I cannot find the field for the item's description nor am I able to tell if it is possible to increase the number of characters for this field. Does anyone have any ideas on this? Perhaps, it's not possible to simply do this with Modifier. Thanks! John John, It is not possible to modify the length of the description field. However this is what I suggest. a. You could...

Outline Numbering
I'm trying to outline number a report and have followed the guidance on Shauna Kelly's page. However, my numbering is back to front - so it goes: 1 1.1 1.1.1 1.2 2.1.1 Instead of 1.2 and 1.2.1. When I go into 'level 2' numbering, I can't link this to the previous level - it selects it, then unselects it by itself. If I clear level 2 and start again by selecting 'number style 1,2,3' then choose 'previous level number - 1' it puts the numbers together (11) and unselects the link to the previous level. And when I put a 'period' in it...

Number of Columns
Dear all, We are using Excel 97, and we need to know if there is possible add more columns than the 256 that already are available from A - IV. We need that because we are exporting data from one aplication to Excel. Thanks a lot. Regards, Pedro Mestre no, 256 is the limit. you may be able to use ms query to 'query' the data source and select only the colums you need, or to build separate queries that return the data over multiple sheets No, 256 is all there are, Regards, Alan. "Pedro Mestre" <PedroMestre@discussions.microsoft.com> wrote in message news:0FBBBE2...

number format
hello my numbers are formatted using question marks, so i can center them and still justify them by the decimal point. for ex. a column of numbers with 2345678 being the largest of them, will be formatted with the string: ?,???,??0;-?,???,??0;- this way i can center the column, and all numbers will straighten to the right, by the decimal point. zeros appear as hyphens. the problem is with the minus signs. if on the previous example i will have -2345, it will look like this: - 2,345 (the minus sign away from the digits). any suggestions? i still wish to center the column. if i give this ...