Excel ignores blanks cells when averaging. If you mean ignore cells uncertain a certain value. In Excel 2007, use AverageIf. In earlier versions you need to construct the formula using SumIf and Countif functions. For example, to average the range A1:A7, ignoring values less than 1: =SUMIF(A1:A7,">=1",A1:A7)/COUNTIF(A1:A7,">=1") Assuming you are formatted to 0 decimal places and want to average the Integers except zero values, use an array formula: =AVERAGE(IF(INT(B1:B7)>0,B1:B7)) Enter the above [Ctrl]/[Shift]/[Enter] If the above does not cover your problem ask your question again, setting out -Excel version -Sample data -How it is formatted -What you want to see as the result. -- Steve "Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in message news:DbedneyUMOJUO5LXnZ2dnUVZ_u6dnZ2d@posted.nuvoxcommunications... > What is the proper way when using the AVG function with cells formatted as > numbers, to fill a blank cell so it won't make the avg lower, but rather > be ignored in the AVG function? > > Thanks! > > --Randy Starkey > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 4080 (20090515) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > >

0 |

5/17/2009 6:54:56 AM

"" is an empty string and is treated the same way as null. However, VBA does not seem to recognise an empty cell as null. Even in an unused spreadsheet, the following returns FALSE. Sub Macro1() x = Range("A1").Value MsgBox IsNull(x) End Sub In your sheet, use IsBlank(). Note that a person using the spacebar to clear cell content will cause problems. .. -- Steve "Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in message news:kYadnefV24dnBYnXnZ2dnUVZ_sOdnZ2d@posted.nuvoxcommunications... > Thanks - that will get it. > > I have an issue with "" versus a true blank. I have some cells that were > set to "". Is there a fast way to set those back to a true blank? I guess > "" is considered "null"? > > Thanks! > > --Randy Starkey > > "AltaEgo" <Somewhere@NotHere> wrote in message > news:%23Oy6kxr1JHA.4944@TK2MSFTNGP06.phx.gbl... >> Excel ignores blanks cells when averaging. If you mean ignore cells >> uncertain a certain value. >> >> In Excel 2007, use AverageIf. >> >> In earlier versions you need to construct the formula using >> >> SumIf and Countif functions. For example, to average the range A1:A7, >> ignoring values less than 1: >> >> =SUMIF(A1:A7,">=1",A1:A7)/COUNTIF(A1:A7,">=1") >> >> Assuming you are formatted to 0 decimal places and want to average the >> Integers except zero values, use an array formula: >> >> =AVERAGE(IF(INT(B1:B7)>0,B1:B7)) >> >> Enter the above [Ctrl]/[Shift]/[Enter] >> >> >> If the above does not cover your problem ask your question again, setting >> out >> -Excel version >> -Sample data >> -How it is formatted >> -What you want to see as the result. >> >> -- >> Steve >> >> "Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in >> message >> news:DbedneyUMOJUO5LXnZ2dnUVZ_u6dnZ2d@posted.nuvoxcommunications... >>> What is the proper way when using the AVG function with cells formatted >>> as numbers, to fill a blank cell so it won't make the avg lower, but >>> rather be ignored in the AVG function? >>> >>> Thanks! >>> >>> --Randy Starkey >>> >>> >>> __________ Information from ESET NOD32 Antivirus, version of virus >>> signature database 4080 (20090515) __________ >>> >>> The message was checked by ESET NOD32 Antivirus. >>> >>> http://www.eset.com >>> >>> >>> >>> > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 4092 (20090520) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > >

0 |

5/21/2009 8:46:17 AM

Hello, I am using Excel 2007 I have a pivot table that gets refresh everyday. The data from the pivot table is based on a sql statement, which the data is connected to a AS/400 table. Here is my problem every morning I go in the connection properties and change the SQLstatement (date) in the command text. I don't want my user to do this. What other option can I do? I was thinking change the date in a cell (A1) and somehow the SQL statement picks up the new date or maybe some sort of parameter, but I am clueless in how to do this. Any tips or website to visit I will a...

I have a spreadsheet where I have included hyperlinks. When I sort the data or add new rows, the hyperlinks are NOT moving to the new destination of the cell. Big problem! How do I get Excel to keep the hyperlink moving with the cell, wherever it may go? Using Excel 2003. Thanks. -- Marina ...

I have two columns with Grant Numbers in each column coming from two different databases downloaded to an excel spreadsheet. I will need to first Match the Grant numbers and highlight the whole row if something is unique meaning there are duplicates,triplicates, one exist in one column but not in the other. I need your help! Thanks but for some reason this is not working. Should I put my formula in column C? I have two columns with data in cells A1-A6266 and data in column B1-B5016. I can see the formula it's not working in column C with the results. is there a command that ...

How do I return the cell name to the sheet name. When I start a ne sheet I write a name in a cell and I want it to automatically renam the sheet to that cell name //Andrea Olsso -- AndreaOlsso ----------------------------------------------------------------------- AndreaOlsson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1651 View this thread: http://www.excelforum.com/showthread.php?threadid=31367 introduce this in <thisworkbook> event code begins Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) ActiveSheet.Name =...

WHY IS MY CELL FORMAT AT WORK DISPLAYED PROPERLY AS -1,500.OO IS DISPLAYED AS (1,500.00) AND WHEN i DO IT AT HOME Ii CANNOT GET THE CLOSEST I GOT IS -1,500.00 is there a diffence between the formatting cell options between Excel's programs.. Thanx. Hi Bumpa! Excel takes it from your Windows Regional options. Use: Start > Settings > Control Panel > Regional options Change the negative number format You'll find that you now have () options. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classific...

I went to make a table with 8 colums and 3 rows, which turned out fine..but now i need to make 3 more rows added on but half the size long with only 2 colums.. so i figure i can just duplicate or copy from my table 3 of these rows and shirk it and delete 1 cell...but trying to get help on how to add or remove cells seems the hard part ...

Hello. I have a form with a combo box in it which has a list of part numbers. When the user enters a part number from the list and hits the "Execute" button it takes them to another form which has detailed info about that particular part number. HOWEVER, if the user enters a bogus part number, it takes them to a completely blank form. I would like to know how to make it so that instead of going to the form it simply says "Part Number Does Not Exist." Any help on this is greatly appreciated. Thanks :) You can set the LimitToList Property of the combo to Yes, tha...

hello, i would like to create a formula which says: if the value in column A is -1, and the value in column B is 2, and th value is column C is 0, then return the value -1 AND if the value in column A is 2, and the value in column B is 1, and th value is column C is -1, then return the value 0 AND etc.etc. There is a finite list of possible permutations for the values i columns A, B, and C, and I would like to define a result for eac possible permutation. I know that I could accomplish this b dedicating a separate column and IF() formula to each possible outcome then summing the results, bu...

I need to create charts from data collected via a com port. As i stands, the data is written to sheet 1. Chart 1 is ready to plot dat as it is written. Once the data is complete, I copy sheet 1 and char 1. This results in sheet 1(2) and Chart 1(2). All references to shee 1 on the newly created chart 1(2) automatically changes to refer t sheet 1(2). This applies to data ranges, Chart Title, Axis names. When creating text boxes refering to sheet1!$A$1, the reference doe not automacially change to sheet 1(2)!$A$1. Can this be done? If not, can I create multiple chart titles? Any help o...

I need to create a spreadsheet to calculate my weekly payroll. The timecards are punched in a timeclock which prints out the in and out times. for example, in at 7:30 am out at 5:00 pm. It also gives me a running total of the number of hours accumulated. I want to take the total number of hours and minutes and convert that to a decimal. If an employee has 32:35 minutes total time accumulated, then using a formula in an excel spreadsheet, I want to see these hours in decimal format, i.e., 32.58 Anyone know how to help me do this? Thanks! might want to try microsoft.public.excel Mat...

Currently this card game appears at the top left of my 21 inch screen. I wish to have it in the centre touching the bottom of the screen each time it appears. How do I achieve this. Thank you for your interest. Bushy And your Microsoft Word question is? "Ernie from Dunedin NZ" <Ernie from Dunedin NZ@discussions.microsoft.com> wrote in message news:E05D1650-D1CA-4E1C-96FB-CF8EC0D17277@microsoft.com... : Currently this card game appears at the top left of my 21 inch screen. : I wish to have it in the centre touching the bottom of the screen each time : it appe...

Hi, I am working with weekly timeshets in Excel and I am using the same form every week, but save it as a file named "Timesheet 20100122" for a week ending on Fri, Jan 22, 2010 (Our accounting week period). Therefore the daily hours appear every week in the same cell. I would need to export these hours to another - summarizing - spreadsheet (For example and invoice spreadsheet) somehow automatically. Any help would be welcomed. Thanks for your help, Cheers, Try http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm ...

when I try to sort a spreadsheet by certain columns, why does it mix everything up? "mix everything up" is not very informative. More information, please: What columns are in your data range? By what columns are you sorting? What is the result you get? -TedMi "Help with cell function" <Helpwithcellfunction@discussions.microsoft.com> wrote in message news:7886A10E-903D-40C9-8D2A-279090F2D644@microsoft.com... > when I try to sort a spreadsheet by certain columns, why does it mix > everything up? Be careful when you highlight the data that y...

I have text cells with sentences ranging from 0 to 160 characters long. I want to break these into 40 character chunks (in separate cells), but don't want to split any word in half. ie, if the 40 char mark is in the middle of the work, I want to go backwards, find where the word starts and split from that point. It's exactly like a wrap text -- but I want to split those lines up into separate cells. thanks kaf If your sentence were in A1, use these 4 formulas: B1: =LEFT(A1,MAX((MID(A1,ROW(INDIRECT("1:40")),1)=" ")*ROW (INDIRECT("1:40")))) C1: =LEF...

Hello, I need a formula that can do the following: -give an average minutes and seconds per phone call This is the data that I will be entering: Calls: 120 Total talk time (hours): 4 Total talk time (minutes): 47 Total talk time (seconds): 52 Thank you Assuming the hours will not be greater than 23. Try this: B1 = 120 B2 = 4 B3 = 47 B4 = 52 =TIME(B2,B3,B4)/B1 Format as m:ss or h:m:ss -- Biff Microsoft Excel MVP "Jimmy H" <spokenwords@gmail.com> wrote in message news:658b55fd-93e6-4eea-b11f-585544d97364@m7g2000prd.googlegroups.com... > Hello, > > I need a...

In match set of five games I want to keep track of the wins and loses of the matches. Example; if the number entered in cell b5 is greater than the number entered in b6 then a 1 (for one win) is entered in cell v5. If not, then a 1 (for one lose) is entered in cell v6. This scenerio would be repeated in ajoining cells as the matches progress. I.e., next match would be entered in b6 & c6, b7 & c7, etc. The numbers in cells v5 & v6 are cummulative. So, if a person won 4 matches with scores of 3/2, 4/1, 3/2, 3/2 and lost 3 matches with scores of 2/3, 1/4, and 2/3, in cells...

I have just added this on a work sheet. It works OK when sheet is unprotected but when I protect the sheet it comes up error 400. If I select Insert row on protection box it will insert row but not formulas while protected. How can I get it to work while sheet is protected. I have also setup a botton on the taskbar to do this for me but it will only do it for the day I set it up in. When I save as for the next day it wont work on the new sheet. Your help will be appreciated Regards Chris I'm not an expert but I thought the whole idea of protecting the worksheet is so p...

I have a hyperlink created by a VB program that should link to a cell on a different worksheet within the same book. The hyperlink code currently is as follows: =HYPERLINK(ADDRESS(4,2,1,FALSE,"MultipleAliases"),"MULTIPLE DP ALIASES") MultipleAliases is a separate worksheet and I want the link to jump to row 4 cell 2 on this sheet. I realise that normally you need a spreadsheet identifier as part of the worksheet definition, thus the "MultipleAliases" would be "[FILE1.XLS]MultipleAliases" if this was saved as FILE1.XLS. If I do save this file wi...

Is there a function that will return the "address" of the active cell? In other words, if I'm on cell G9, it there a function I can use that will return 'G9?' TIA Doug Hi Doug try =ADDRESS(ROW(),COLUMN()) Frank Doug Mc wrote: > Is there a function that will return the "address" of the active cell? > In other words, if I'm on cell G9, it there a function I can use that > will return 'G9?' > > TIA > Doug Or slightly less typing =CELL("address") -- Regards, Peo Sjoblom "Frank Kabel" <frank.kabel@fr...

I entered the following formula and the only way I get the result of each cell that doesn't have "xxxxx" is to fill in all cells; I want the result cell by cell, can you help? =IF(OR (C5="xxxxx",L5="xxxxx",U5="xxxxx",AD5="xxxxx",AM5="xxxxx"), "",CONCATENATE(C5&", ",L5&", ",U5&", ",AD5&", ",AM5)) Hi not quite sure what your expected result is. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany "Marcia O" <anonymous@discussions.mic...

I have a worksheet to calculate 12 week averages, and every week I need to add a row, but my formulas automatically change so do not include the new row. What do I need to do to stop the formula changing everytime I insert a new row? Tell us the layout of the worksheet: what cells have data, what cell has average. Then we can be of more help Bernard "BertiesMum" <BertiesMum@discussions.microsoft.com> wrote in message news:BF72CB7F-2866-47DF-AA47-909371817130@microsoft.com... >I have a worksheet to calculate 12 week averages, and every week I need to > add a row, b...

hey all, i have change the cell anem from A1 to StartCell. Bu, how can i rename it back to A1 or change it to another name? Thanks in advance Regards Dragon Hi Dragon go into insert / name / define - you can delete the name there and create another if you wish. Cheers JulieD "Dragon" <Dragon@discussions.microsoft.com> wrote in message news:B6A6510E-0233-4B2A-8A0C-F16F73585CBA@microsoft.com... > hey all, i have change the cell anem from A1 to StartCell. Bu, how can i > rename it back to A1 or change it to another name? > Thanks in advance > Regards > Drago...

How do you make the first number in a string of numbers a zero and keep it there? Depends on if you want a *true* number, or a text number. For Text, precede the entry with an apostrophe ( ' ), OR, pre-format the cell as text. For true numbers, custom format the cell with the number of digits you're using: 00000 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Mr_Jim" <MrJim@discussions.microsoft.com> wrote in message news...

Hi and thanks to anyone who reads this. I have a worksheet which contains two columns of dates. In a second worksheet i have a column which adds the two dates together as TEXT and ommits dates which are blank which works perfectly, however: I would like to know how i could format each of the 2 dates in the 1 cell to have different font colors? Here is my existing cell formula: =IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" "&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy"))) I have a feeling its not...

I want to reference a cell using some math: $B(1+1) which I would hope equals $B2 and the cell would then contai a reference to $B2. How does one perform math funtions to the row part of a reference? I what to be able to reference a cell that contains a cell reference So Cell A1 would contain the text B12 cell A2 would reference to cell A1 and show the contents of B12. and in cell A3, I want to show the contents of B13... but I want t take the contents of cell A1 and increment it from B12 to B13... How do I do that? I tried simple math like $B(1+1) which does no equal $B2. T...