Hello, I have al total in column a ($3.95) and a total in column b 4,803. When I try to divide it the total comes out as ($18,987.28) but it should be ($18,971.85). What am I doing wrong. Thank you for any help you can give me. -- thank you mac See http://www.mcgimpsey.com/excel/pennyoff.html My guess is that your "3.95" is actually more like 3.9532 In article <2909D013-4352-4235-A904-29D66826CCFA@microsoft.com>, mac <mac@discussions.microsoft.com> wrote: > Hello, > I have al total in column a ($3.95) and a total in column b 4,803. When I > ...

In Excell 2002 I have a worksheet where the user can choose what percent of the time three different procedures will be followed. The total of the three choices MUST equal 100. Is there a way to get the data validation (or some other non-VBA method) to tell the user his math doesn't add up to, or goes over, 100? i.e., using cells 1A, 1B, and 1C as entry cells, cell 1D must = 100 (using the formula: "=1A+1B+1C" in cell 1D) Thanks, JR JR, I would use conditional formatting. Select D1. Goto menu Format>Conditional Formatting Set the test to 'Not equal to', th...

I am trying to come up with a simple cell formula to total or sum amounts bolded in a column. Any Ideas? Thanks from a person needing help. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=392229 There's nothing built into excel that will sum values based on formatting. You could use a User defined function, though. Option Explicit Function SumBold(rng As Range) Applicati...

Ever since I got Money 2005 I have had some big concerns about this software. The bugs are so many and obvious that I really can't believe they released it this way. However, I am a pretty tolerant person and can put up with a lot so I kept using it (about 2 or 3 months now). I decided not to return within the 30 days because even if I don't use it I felt the free year subscription to Experian Credit Manager and Money magazine was worth it. I am now seriously thinking of going back to Money 2004 but was wondering if there was a less painful way than opening up the old file a...

Hi All, I am having problems adding time values hwen a negative time is involved. I have read all the posts on this and have chosen the 1904 date option, but no success. I determine the difference between times and then subtract a standard day from this amount to achieve a result (positive or negative). Then I want to sum the results. Works fine as long as I don't have any negative results. If I do have negative then the answer is always 0. Example: Morning.............Afternoon..............Balance In.......Out........In........Out..........Total..Flex...Balance 8:15...

Hi, I can think of no reason why Excel (2002) suddenly has decided to exit (even wihtout asking to save te current workbooks!) every time I ry to use the conditional sum wizard. I have tried to uninstall he add-ins, then run excel, op en workbook, close it and exit excel to make sure it runs without add-ins. In a next session I reinstalled the add-ins. But when I click on conditional sum wizard excel exits. Can anybody tell me what is happening here? greets Sybolt -- ||//////|| ( o o ) ( O ) - ( ) ( ) (_...

I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 It will only average the cells with numbers. Make sure the blank cells ARE blank. -- Ken Russell kenrussellyourhat@optushome.com.au Remove yourhat to reply by e-mail .. "Randy Lefferts" <anonymous@discussions.microsoft.com> wrote in message news:16d501c4d6e7$46e0bee0$a501280a@phx.gbl... >I have a column with 31 rows. In select cells, there is > a number. I want to avera...

Hi, I'm looking for a way to make a sum of values in a column depending on the respective value of another column. Example: A1 = 1 A2 = 2 A3 = 1 B1 = 100 B2 = 300 B3 = 125 I'd like to be able to make a sum of B values when A values = 1. In that case, my sum would be 225. Thanks, Alex One way: =SUMIF(A:A,1,B:B) In article <OQ#LYB6hEHA.356@tk2msftngp13.phx.gbl>, "Alex Langlois" <alex.nospam@nospam.net> wrote: > Hi, > I'm looking for a way to make a sum of values in a column depending on > the respective value of another column. ...

I have a table of products in rows against years in columns with revenues in the data array. I want to sum revenues for a 'x' year period starting at 'y' year for product 'p' on a separate worksheet. So it's a 2D lookup. Following useful tips from the community I was able to find the revenue for 'y' year for product 'p', using INDEX: =INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0)) - where $C14 gives the product name and $Q$2 shows ...

Hi, could anybody help me? I have a worksheet with expenses i different currrency, I wanted to transform everything in dollar an then add then. In column A I have the expense, in column B I have th currency. Then I have a table on the side with the exchange rates. Column C currency again and Column D the exchange rate. Is there a wa to create a formula where I transform all expenses in dollar and the add them? Thanks, Marco -- Message posted from http://www.ExcelForum.com Hi why not format the cells conatining the values as currency?. And then simply use SUM on this range -- Regards ...

I am doing a Pivot Table importing data from Access and am trying to get sums for both columns and rows. I can get one or the other but not both. I tried adding a calculated field and calculated item and they do not show up, even though I have entered a formula. If I delete the total columns and rows, then it lets me add a calculated field or item. Any ideas? You do not mention if you checked up under pivot table Options Grand Totals for Columns Grand Totals for Row -- Message posted from http://www.ExcelForum.com ...

I want to make a sum if Range is a week number and if style is Equal to CONC-92 or CONC-45 Week# 49 Week# 50 CONC-92= 27 CONC-92= 30 CONC-45= 27 CONC-45= 30 Datas are in a pivot table and... Pivot table looks like this: Date CONC-92 CONC-45 CONC-92 CONC-45 12/7 5 5 10 10 12/8 2 2 10 10 12/9 5 5 10 10 12/10 5 5 10 10 please help -- Lorenzo Díaz Cad Technician ...

I am trying to set up a form that will totalise multiple fields relating to a unique identifying field in the same table Hi, put an unbound textbox on the form. Set its datasource to something like = Nz(Me.txtA, 0) + Nz(Me.txtB,0) + Nz(Me.txtC,0) where txtA, txtB, txtC are controls for the fields you want to sum. Jeanette Cunningham "Gezza77" <Gezza77@discussions.microsoft.com> wrote in message news:03C166B7-5710-4EB2-B744-4635F8633B6C@microsoft.com... >I am trying to set up a form that will totalise multiple fields relating to >a > unique identifying field in t...

how can I count all the occupied cells in the same column if the cells contain text and numbers? A row 1 br1 row 2 row 3 row 4 cb2 row 5 row 6 row 7 df6 ect down to row 200. the answer should be 3 (the number of cells occupied) please help thanks rgs jerie use: =COUNTA(A:A) Regards Trevor "jerie" <anonymous@discussions.microsoft.com> wrote in message news:4a1c01c5214e$19ffd710$a401280a@phx.gbl... > how can I count all the occupied cells in the same column > if the cells contain text and numbers? > ...

Hello, One of our business partners is having trouble w/an Excel document. They have the SUM function used multiple times throughout the document, and as they have changed values in certain fields, the SUM has automatically updated itself to reflect this. It's not working anymore. I have tried to redo the formulas but they simply won't work. The SUM stays the same regardless of the values in the cells. I can't even create a brand new column w/the SUM function at the bottom...it won't up date either. In a new document I can get the SUM function to work properly, how...

is there a way to average only visible cells in a list? i am using an advanced filter and wan to automate the averages of columns following the filter. however, i run in to issues of hidden rows being counted in the average when i try to create the formula. is there a function for "visible cells". i know you can go to edit->go to-> visible cells to copy the cells, but i dont want to copy the cells to another sheet and then take the averages there. thoughts? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post ...

I need help creating an average formula that will only average those numbers that are greater then 0. For example if I have 3,0,3,3,6,5 I need a formula that will average only 3,3,3,6,5. Any help would be appreciated. Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: =SUMIF(A1:A10,">0")/COUNTIF(A1:A10,">0") HTH Jason Atlanta, GA >-----Original Message----- >I need help creating an average formula that will only ...

Please help!! Excel beginner! I need to build a formula that will add any remaining value over 75 in a cell. For example: In cell A1 = 76.5 In cell A2 - I need a formula that will sum anything over 75 and enter 1.5 into cell A2. Thanks in advance for everyone's help!! > For example: > In cell A1 = 76.5 > In cell A2 - I need a formula that will sum anything over 75 and enter 1.5 > into cell A2. Perhaps, as a start .. try in A2: =IF(A1>75,1.5,"") A2 will return 1.5 if A1 contains a number exceeding 75, otherwise A2 will just appear empty ("") -- ...

Hi. I have a gradebook. I have 16 assignments, and thus have 16 columns for scores. However, I would like to include ONLY the highest 10 scores in the final grade. In other words, how can I find the sum of the largest 10 values in a 16-cell range? Thanks! =SUM(LARGE($A$1:$P$1,ROW(INDIRECT("1:10")))) arary entered using CTRL+SHIFT+ENTER -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Best wishes to all, and hope f...

Hi, I have created a Tabular report that lists names and weights for different people. I want to create a stand-alone field in the page footer that will display the SUM of all the weights on the report. Can somebody please help me? (I'm using Access 2000 btw) I have tried creating a text box with the Control Source property set as "=Sum(weight)". However, this just produces an error. Thanks in advance. Julie Smith wrote: >Hi, >I have created a Tabular report that lists names and weights for different >people. I want to create a stand-alone field in the page f...

One way" If(SUM(A1:A10)<0,0,SUM(A1:A10)) tj "ej764" wrote: > Try =MAX(0,SUM(F2:F300)) -- Don Guillett SalesAid Software donaldb@281.com "ej764" <ej764@discussions.microsoft.com> wrote in message news:5FBED33E-FC8F-4E0B-9E1B-AB63205D589E@microsoft.com... > Hi =SUMPRODUCT(--(A1:A10>0),A1:A10) Arvi Laanemets "ej764" <ej764@discussions.microsoft.com> wrote in message news:5FBED33E-FC8F-4E0B-9E1B-AB63205D589E@microsoft.com... > ...

Please Help...This is a Quality Monitoring Form. Sheet B has all representatives sorted Alphanumerically. They all report to different teams. My formula resides on sheet A and reads {=AVERAGE(IF('Sheet B'!$D$2:$D$200="Team 1",'Sheet B'!$F$2:$F$200))} The "D" column represents the Team Number and the "F" column represents the score that I want retrieved. I can't sort by Team, because they change often. The reason I can't change the sheets is because each of the sheets represent a week in the month with the same Representatives. ...

Hi all - I'm struggling to find a formula that will solve my problem: I have a matrix of numbers defined by a series of numerical column and row headers. RefCel 1 2 3 4 5 . . . m 1 a # # # # 2 # # # # # .. 5 b # # # # .. n # # # # # What I'm trying to do is for a give column header and a subset of row headers, sum the intersecting values. Let's assume that the subset are held in a named range Subset and the row and column headers are in range called RowHead and ColHead and...

Why do totals that are grearter than 100 get #### How can that be changed Try and widen the column and see if that helps -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "2141233" <2141233@discussions.microsoft.com> wrote in message news:36F6768A-47E4-474B-8557-9A3719E5FB4D@microsoft.com... > Why do totals that are grearter than 100 get #### > How can that be changed Because the cell width is too small Widen i...

Hi All, Here is what I'v been trying to do but..! I'v 2 sets of data (data1 & data2) Column name may be sane/different in each data set and same applies to acct_no.. I want to prepare a report that combine product & accounts data show accumulatd result on another sheet/file. Data 1 - upto Jan 2006 Acct. No Total Prod - A Prod - B Prod-C 1001 51 10 30 11 1002 47 15 20 12 1003 80 20 15 45 1004 64 25 16 23 Total 242 70 81 91 Data 2 - Feb 2006 Acct. No Total Prod - A Prod - B 1002 7 5 2 1004 16 10 6 1009 9 3 6 Total 32 18 14 Report required Acct upto Jan'06 Prod - ...