totaling rows

New at this hope this makes sense: 

I have rows of data that include entering demographics, 6 columns indicate 
race, 2 columns gender, six age range columns and a total participant column.

What formula would add the numbers in each of the demographics and insure 
that the different ranges equal each other and the total participants for 
each row.

Thanks, Mel

0
Mel1 (144)
7/24/2007 2:36:16 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
638 Views

Similar Articles

[PageSpeed] 32


"Mel" wrote:

> New at this hope this makes sense: 
> 
> I have rows of data that include entering demographics, 6 columns indicate 
> race, 2 columns gender, six age range columns and a total participant column.
> 
> What formula would add the numbers in each of the demographics and insure 
> that the different ranges equal each other and the total participants for 
> each row.
ie.
col.      I J K L M N       O        P               Q   R       S      T    
     U     V
           WB	AOHU	Male Female        <5	5-12 13-18 19-49 50-64 65+	Total
 row     55    4              27     28                              59      
                         59     								
It would let the person know the Gender doesn't equal the other totals.				
Mel
> 
0
Mel1 (144)
7/24/2007 3:36:06 PM
Taken separately,

=SUM(I2:N2)  gives the total of the race columns,
=O2+P2         gives the total of the gender columns,
=SUM(Q2:V2) gives the total of the age columns,

assuming your data is on row 2. So, depending on what you want to
highlight, you can put these together in an IF formula, eg:

=IF(SUM(I2:N2)<>O2+P2,"Gender/Race
difference",IF(SUM(Q2:V2)<>SUM(I2:N2),"Age/Race
difference",IF(O2+P2<>SUM(Q2:V2),"Gender/Age difference","Totals
agree")))

Is this the kind of thing you want? Obviously, Excel can't tell you
which is correct, just point out that there is a difference.

Hope this helps.

Pete

On Jul 24, 4:36 pm, Mel <M...@discussions.microsoft.com> wrote:
> "Mel" wrote:
> > New at this hope this makes sense:
>
> > I have rows of data that include entering demographics, 6 columns indicate
> > race, 2 columns gender, six age range columns and a total participant column.
>
> > What formula would add the numbers in each of the demographics and insure
> > that the different ranges equal each other and the total participants for
> > each row.
>
> ie.
> col.      I J K L M N       O        P               Q   R       S      T    
>      U     V
>            WB   AOHU    Male Female        <5        5-12 13-18 19-49 50-64 65+      Total
>  row     55    4              27     28                              59      
>                          59                                                                    
> It would let the person know the Gender doesn't equal the other totals.                        
> Mel
>
>
>
> - Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
7/24/2007 10:18:10 PM
Thank you so much, took the formula you wrote and add another IF statement to 
include the total.  Prefect just what I wanted. Thanks again.

"Pete_UK" wrote:

> Taken separately,
> 
> =SUM(I2:N2)  gives the total of the race columns,
> =O2+P2         gives the total of the gender columns,
> =SUM(Q2:V2) gives the total of the age columns,
> 
> assuming your data is on row 2. So, depending on what you want to
> highlight, you can put these together in an IF formula, eg:
> 
> =IF(SUM(I2:N2)<>O2+P2,"Gender/Race
> difference",IF(SUM(Q2:V2)<>SUM(I2:N2),"Age/Race
> difference",IF(O2+P2<>SUM(Q2:V2),"Gender/Age difference","Totals
> agree")))
> 
> Is this the kind of thing you want? Obviously, Excel can't tell you
> which is correct, just point out that there is a difference.
> 
> Hope this helps.
> 
> Pete
> 
> On Jul 24, 4:36 pm, Mel <M...@discussions.microsoft.com> wrote:
> > "Mel" wrote:
> > > New at this hope this makes sense:
> >
> > > I have rows of data that include entering demographics, 6 columns indicate
> > > race, 2 columns gender, six age range columns and a total participant column.
> >
> > > What formula would add the numbers in each of the demographics and insure
> > > that the different ranges equal each other and the total participants for
> > > each row.
> >
> > ie.
> > col.      I J K L M N       O        P               Q   R       S      T    
> >      U     V
> >            WB   AOHU    Male Female        <5        5-12 13-18 19-49 50-64 65+      Total
> >  row     55    4              27     28                              59      
> >                          59                                                                    
> > It would let the person know the Gender doesn't equal the other totals.                        
> > Mel
> >
> >
> >
> > - Hide quoted text -
> >
> > - Show quoted text -
> 
> 
> 
0
Mel1 (144)
7/25/2007 2:36:03 PM
Thanks for feeding back, Mel - glad it worked for you.

Pete

On Jul 25, 3:36 pm, Mel <M...@discussions.microsoft.com> wrote:
> Thank you so much, took the formula you wrote and add another IF statement to
> include the total.  Prefect just what I wanted. Thanks again.
>
>
>
> "Pete_UK" wrote:
> > Taken separately,
>
> > =SUM(I2:N2)  gives the total of the race columns,
> > =O2+P2         gives the total of the gender columns,
> > =SUM(Q2:V2) gives the total of the age columns,
>
> > assuming your data is on row 2. So, depending on what you want to
> > highlight, you can put these together in an IF formula, eg:
>
> > =IF(SUM(I2:N2)<>O2+P2,"Gender/Race
> > difference",IF(SUM(Q2:V2)<>SUM(I2:N2),"Age/Race
> > difference",IF(O2+P2<>SUM(Q2:V2),"Gender/Age difference","Totals
> > agree")))
>
> > Is this the kind of thing you want? Obviously, Excel can't tell you
> > which is correct, just point out that there is a difference.
>
> > Hope this helps.
>
> > Pete
>
> > On Jul 24, 4:36 pm, Mel <M...@discussions.microsoft.com> wrote:
> > > "Mel" wrote:
> > > > New at this hope this makes sense:
>
> > > > I have rows of data that include entering demographics, 6 columns indicate
> > > > race, 2 columns gender, six age range columns and a total participant column.
>
> > > > What formula would add the numbers in each of the demographics and insure
> > > > that the different ranges equal each other and the total participants for
> > > > each row.
>
> > > ie.
> > > col.      I J K L M N       O        P               Q   R       S      T    
> > >      U     V
> > >            WB   AOHU    Male Female        <5        5-12 13-18 19-49 50-64 65+      Total
> > >  row     55    4              27     28                              59      
> > >                          59                                                                    
> > > It would let the person know the Gender doesn't equal the other totals.                        
> > > Mel
>
> > > - Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
7/25/2007 6:32:44 PM
Reply:

Similar Artilces:

changing row height
Is it possible to change the row height within the same sheet of some rows without it affecting the other rows? -- A fledgling at 64 hi yes it is. are you having trouble of any kind?? to set a row....on the menu bar>format>row>row height set the height to what you want. you may have to experiment. once set to the desired height and you need to set other rows to the same height, select the other row and hit the f4 key. this is the repeat action key. the set the height of the select row to the same height as the last row. if you need to have different heights then you ...

Attach Rows with Row Data
I am trying to sort by Data/Time in a Column, but if I sort by Date/Time, Just that changes, the other columns don't change with it. How can I either LOCK or ATTACH the Rpw data together so when I sort, Everything changes, not just the Date/Time? Thanks Mike Miller Hi just select the entire data range (all columns) prior to sorting your data -- Regards Frank Kabel Frankfurt, Germany "Admiral_Kirk_1" <Admiral_Kirk_1@discussions.microsoft.com> schrieb im Newsbeitrag news:BAFA6FEE-04C3-4AFB-9FA6-CFF7A41224C9@microsoft.com... > I am trying to sort by Data/Time in a ...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

Moving rows #2
In Word2K, I can shift table rows up or down with Alt+Shift+Up/Down Arrow Is there a similar keyboard shortcut for Excel2K? -- David The best I can suggest is.......... Highlight the row you want to move.........Right click > Cut > Right click to highlight the row below where you want the cut row inserted > InsertCutCells Vaya con Dios, Chuck, CABGx3 "David" <dturner4_1999@yahoo.com> wrote in message news:u862$UceFHA.4040@TK2MSFTNGP14.phx.gbl... > In Word2K, I can shift table rows up or down with Alt+Shift+Up/Down Arrow > Is there a similar keyboard short...

Counting Consecutive cells in a row with a value greater than zero
I have 7 cells in a row (D39:J39). Default value of these cells will be 0.00. I want a cell to display the count of cells whose value is greater than zero. How do I do this? I was looking at DCount - but from the documentation in help that I looked at, the values tested need to be in a column. - is this correct. Any example will be appreciated - Thanks - Sean Hi Sean try =COUNTIF(d39:j39,">0") hth Keith "Keith74" <keith.willis@sbjbc.co.uk> wrote in message news:1180614455.498158.304370@o5g2000hsb.googlegroups.com... > Hi Sean > > try &...

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

How row heights are determined in Excel
Is it possible to change the unit of measure of row heights settings lets say to centimeters or inches? How? Hi Anne, The short answer is no. Row height is measured in points or pixels. 72 points or 96 pixels per inch. Don't ask about columns!!! Biff >-----Original Message----- >Is it possible to change the unit of measure of row >heights settings lets say to centimeters or inches? How? >. > See http://www.erlandsendata.no/english/index.php?d=envbawssetrowcolumnmm for a small macro that does it. This said, you can zoom both a spreadsheet window and a printout, an...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

macro
I have several columns in an income sheet. One column has a sum at the bottom. I want to be able to run a simple macro that will copy the last row of formatted cells, just above the sum total, and insert it as a new row just above the sum total so another line of data can be input. But I want the sum total to sum also the newly inserted row. Presently I can't figure out how to do it. Example A B C 1 24 Oct Dollars In $100 2 25 Oct Dollars out $25 3 Total ...

Copying rows from one Excel spreadsheet to another
At my office, we use Excel 2000 (9.0.3821 SR-1). I have a daily spreadsheet that I maintain and add comments to one of the cells. At the end of the week, I maintain a weekly spreadsheet in which I copy the rows in which I have comments to the weekly spreadsheet. Sometimes, it allows me to also copy the comments, and at other times, it does not, and then I have to re-add them. Can you come up with a reason and a solution? What do you mean by "allows me"? If you do a regular copy as in ctrl + C it will copy everything, if you only want to copy comments, copy and when pasting do e...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

jump to row
Hi all, I posted this problem yesterday and was very grateful for the reply below, however I have carried out the instructions but it does not seem to work or I do not understand it correctly, any other help would be great. p.s I am inexperienced at macros so please explain in simple terms.Thank you again in advance your help on this site is much appreciated. I have an inventory with 500 rows all with a number i.d Is there a command by where I can input the i.d (say 450) I wish to view into a cell (say B1) at the top of the page and the row will be shown even if it is off the visible p...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

Go to the last Row using VBA
Does anyone know of a line of code I can put into my macro that will allow me to go down to the last line of data without using a loop. For example whether I have 90 rows of data or 150. My code would make the active cell the last one. Thanks in advance for any help available. Hi Nick Example for the Active sheet Sub test() Cells(LastRow(ActiveSheet), 1).Select End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, ...

Importing Campaign Responses Data in row did not match the import
We are struggling to understand how to importing Campaign Responses using the bulk import wizard. We receive the errors: Reason: Data in row did not match the import type Reason ID: 0x8004032d Please could someone help us diagnose where the problem lies? Thanks Hi, It is very difficult to help you with just this information. We will need some more information to give you helpful suggestions. like whts are the fields, type of file, mapping to which fields and other details. Following URL may be helpful to you. http://www.microsoft.com/dynamics/crm/using/troubleshooting/tsbulkimport.msp...

Total of Totals
Hey there, I've set up a database consisting of four tables. I run a query which calculates a total for the item (unit price * quantity). Is it possible to calculate the final total of all this for a report? If that's possible, how can I get it to be at the bottom of all the records but only on the last page? Thanks Mathew On Aug 1, 12:48 am, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Yes. > Open the form in design view, click on menu VIEW - Report Header/Footer a= nd > add a text box in the footer with Control Source =3D[unit price] * > [quantit...

How to change the individual row into columns
In Excel If each row looks like the below given format Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US I want to change it to be the following format (Given below). Is i possible to do that? If so, what are the steps to be followed? I nee your help. Please reply this mail. Thanks in Advance. Michael 20, Eastman Avenue Boston US David 56, George Patel Road, Texas US Michael 20, Eastman Av...

Pole display Sub Total
Hi All, When a client of mine scans items the amount displaying is the sub total. Is there any way to display the total (ie including tax amount) Regards Amanda ...

Adjust Row Height for Text
I am working on a worksheet, and I have a row that contains text that I want to restrain in length so the text automatically wraps around and starts a new line within the row. I've tried "help" in Encel, but I can't figure how to do this. I want a wrap around in the row, because I don't want the text to exceed the limits of the paper when the worksheet is printed out. Any help would be appreciated. Pedro -- To contact me directly by email, please remove the words "NO" and "SPAM" from the reply address above. Alt click on the cell you want...

Calculate Total Books
I am preparing packing slips for boxes to be sent out. There are different book orders going into the same box. Each book in the order will have its own set of id numbers that are usually, but not always, in a string(101-121 etc). I use a calculated field on the form to display the number of books in the string as I store the start book number and subract it from the end book number and add 1 to get the actual number of books in the string. I use the same calculated field on the report to show the number of books in each detial, but I have multiple strings in 1 box. I need to show a...

Importing CSV file into single column/multiple rows vs. multiple rows/single column
Hello, I need to know how to import data into a single column with multiple rows. By default, when importing a CSV or TAB txt file, it imorts data across multiple columns in one row but I need it to go into one column and multiple rows. I don't have the option on the import wizard. Thanks, Brian Brian, Open it as usual, then do a copy pastespecial transpose. HTH, Bernie MS Excel MVP <bjorgenson@charter.net> wrote in message news:1119877803.916313.176730@g14g2000cwa.googlegroups.com... > Hello, I need to know how to import data into a single column with > multiple rows. B...

Inserted Rows not re-calculating
I use SUM(Column_Title) a lot as I often do not know how many rows the worksheet will grow to. It works great but when I add in rows, and double-click on the formula cell, the extra rows I added are not included in the formula range (the colour-highlighted box). My workaround is to double-click on the formula, then double-click the colunm title (in Row 1). Then when I double-click the formula again, it shows it formula range includes the rows I added. This has been happening for a while, and the workaround is tiresome as there are many columns in some of my worksheets (and I have to ...

how do I get a total for all worksheets in my workbook?
I have ten worksheets, each with a total. How do I get a total of all totals? Considering you have 3 sheets, and the totals are in cell A1, then use: =SUM(Sheet1:Sheet3!A1) Mangesh "Aileen Hewat" <AileenHewat@discussions.microsoft.com> wrote in message news:18C80118-642F-4690-8B5E-7D68B63752E3@microsoft.com... > I have ten worksheets, each with a total. How do I get a total of all totals? This file might be a help: http://www.bygsoftware.com/examples/zipfiles/consol.zip It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/exa...