SUM or AVERAGE of mm:ss
I need help, I received link references from a corporate spreadsheet with time format 13:30 (representing mm:ss). I am trying to get at SUM and AVERAGE of these cells and having a hard time getting it right. I did try to have output format as [m]:ss and it is coming out incorrectly. If I have to convert these to h:mm:ss is there a way to make to conversion happen automatically. Example: 20:12 5:31 9:47 8:45 8:27 7:25 20:21 17:46 13:01 22:17 SUM these to format [m]:ss will give me 15212:00 SUM these to format mm:mm will give me 32:00 AVERAGE these to format 13:30 will give me 1:21 Can someone explain what is Excel doing here and possible offer me a solution for AVERAGE these times. Thanks, Kha
KhaVu
excel.misc
78881 articles. 
5 followers.
5 Replies
412 Views
Although these may look like minutes and seconds, I think Excel has taken them as hours, minutes and seconds and is just displaying [h]:mm. Click on one of the cells and check the format with Format | Cells | Number (tab). If this is the case, then in a blank column enter the formula: =A1/60, assuming your times are in column A, and format as [m]:ss. Copy this down. You can fix the values using <copy> with Edit | Paste Special | Values | OK, and you can also paste the fixed values to over-write the original values. Hopefully, your SUM and AVERAGE formulae will work after this. Hope this helps. Pete
pashurstThe cell format is actually Time>13:30 "Pete" wrote: > Although these may look like minutes and seconds, I think Excel has > taken them as hours, minutes and seconds and is just displaying [h]:mm. > Click on one of the cells and check the format with Format | Cells | > Number (tab). > > If this is the case, then in a blank column enter the formula: > > =A1/60, assuming your times are in column A, and format as [m]:ss. Copy > this down. > > You can fix the values using <copy> with Edit | Paste Special | Values > | OK, and you can also paste the fixed values to over-write the > original values. Hopefully, your SUM and AVERAGE formulae will work > after this. > > Hope this helps. > > Pete > >
Yes, if you do Format | Cells | Number (tab) then it shows Time with 13:30 - this means hours and minutes. Click on 13:30:55 (two down on the list) and you should see your first value change to 20:12:00, in which case it is formatted as I suggested and you can follow my suggestions to rectify it. Pete
Now, I am really in trouble because I want them the be in mm:ss not hh:mm:ss i.e. the first number should be 0:20:12. Can you help with this? or another forum is needed here. "Pete" wrote: > Yes, if you do Format | Cells | Number (tab) then it shows Time with > 13:30 - this means hours and minutes. Click on 13:30:55 (two down on > the list) and you should see your first value change to 20:12:00, in > which case it is formatted as I suggested and you can follow my > suggestions to rectify it. > > Pete > >
Read the second part of my first posting, ie: " ... If this is the case, then in a blank column enter the formula: =A1/60, assuming your times are in column A, and format as [m]:ss. Copy this down. You can fix the values using <copy> with Edit | Paste Special | Values | OK, and you can also paste the fixed values to over-write the original values. Hopefully, your SUM and AVERAGE formulae will work after this. Hope this helps. ...." And I hope this re-posting helps. Pete
Similar Artilces:
sum total
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 > ...
validation for a sum that must = 100%
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...
Sum total bolded figures in a column
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...
Possibly going back to MM 2004, any advice?
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...
Summing negative time
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...
crash on conditional sum wizard
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 ) - ( ) ( ) (_...
Averaging only cells with data
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...
Sum using probably a VLOOKUP
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. ...
INDEX and sum
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 ...
look up with multiplication and sum
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 ...
Pivot Table Sum Columns and Rows
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 ...
Sum if Condition is Equal in Range Date and find column
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 ...
How do I sum multiple fields relating to a single field in a form
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...
count or sum
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? > ...
SUM Function not Updating
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...
average only visible cells?
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 ...
Average Formula #3
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 ...
sum a cell value over 75
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 ("") -- ...
Sum of the Largest Values
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...
Column Sum in Footer
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...
how do I default sum to 0 for negative numbers in excel
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... > ...
Average(If...
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. ...
Conditional Summing across ranges using arrays
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...
Excel sums
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...
Look up column name, match rows (a/cnumber) & summing up....its confusing!
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 - ...
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 > ...
validation for a sum that must = 100%
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...
Sum total bolded figures in a column
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...
Possibly going back to MM 2004, any advice?
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...
Summing negative time
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...
crash on conditional sum wizard
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 ) - ( ) ( ) (_...
Averaging only cells with data
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...
Sum using probably a VLOOKUP
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. ...
INDEX and sum
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 ...
look up with multiplication and sum
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 ...
Pivot Table Sum Columns and Rows
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 ...
Sum if Condition is Equal in Range Date and find column
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 ...
How do I sum multiple fields relating to a single field in a form
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...
count or sum
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? > ...
SUM Function not Updating
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...
average only visible cells?
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 ...
Average Formula #3
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 ...
sum a cell value over 75
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 ("") -- ...
Sum of the Largest Values
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...
Column Sum in Footer
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...
how do I default sum to 0 for negative numbers in excel
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... > ...
Average(If...
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. ...
Conditional Summing across ranges using arrays
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...
Excel sums
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...
Look up column name, match rows (a/cnumber) & summing up....its confusing!
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 - ...