I have a spreadsheet that I calculates monthly data and puts it into a year end management spreadsheet. How do I clear the cells of data (zero out) but leave the formulas intact so that I can use the spreadsheet again in the comming year? You should be able to just delete the static data (the data that the formulas are using to make calculations). To toggle the formula view (see the formulas) use Ctrl+~ "clemrogan" wrote: > I have a spreadsheet that I calculates monthly data and puts it into a year > end management spreadsheet. How do I clear the cells of data (zero...

I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote: >I have a column of data as illustrated below than contains numbers, blank >cells, dashes and < entries. Is it possible to average only ...

Hi Is there a way to change all the existing formulas in a workbook to Absolute cell references? Thanks -A "Arska" wrote... >Is there a way to change all the existing formulas in a workbook to >Absolute cell references? Using a macro, yes. Sub foo() Dim ws As Worksheet, c As Range For Each ws in ActiveWorkbook.Worksheets For Each c In ws.UsedRange If c.HasFormula Then _ c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute) Next c Next ws End Sub -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usuall...

I know this should be simple but I need help, I need a blank cell to have "X" and a non-blank cell to have "Have" Thanks! Hi, I don't quite understand your question, if you mean when A1 is blank to enter an X in cell B1 or "Have" if A1 is not blank use =if(A1="","X","HAVE") "mrsjcd3" wrote: > I know this should be simple but I need help, > > I need a blank cell to have "X" and a non-blank cell to have "Have" > > Thanks! you mean? =IF(A1="","X","Have...

I had an older version of Excel (97 maybe?) and our office updated to 2007. Now my vlookup will not return data. The vlookup formula appears in the cell or I get the NA#. I've formatted my lookup cells and table to General but still get the same result. After formatting the cell as General, re-enter it (F2/Enter) - if you're still seeing the formula, press Ctrl/~ -- you're looking at the formula layer instead of the value layer. "Chaps" wrote: > I had an older version of Excel (97 maybe?) and our office updated to 2007. > > Now my vloo...

Another seemingly simple process which I can't figure. :) I have a list of averages, and I want to get the average of them. As such, A 1 25% 2 42% 3 100% 4 50% I need a formula to average something like the above. Thanks, Alan =AVERAGE(A1:A4) is one possible solution. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=513937 Hi Alan It really depen...

How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD Assuming source data as posted in A2:A7 Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: > How do I average ...

Hi, I have imported a CVS file delimited on commas. I got "" in beging and end along with numeric data in each cell. Data is in more than 1000 rows with 10 columns. I want to remove these commas and change the cell format to numeric without lossing any numerical data. Thanks in advance. Hi have you tried 'Edit - Replace' for this operation?. You may also have a look at: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Regards Frank Kabel Frankfurt, Germany Dalvi wrote: > Hi, > > I have imported a CVS file delimited on commas. > I got "&quo...

I have a "quiz-style" spreadsheet. In it, I have included several CONCATENATE functions that takes their name which they entered at the beginging and a message and their score and puts it into a sentance. I have also used hyperlinks within my spreadsheet, I have been able to include the concatenate feature in these. It is possible to include any excel functions within this screentip? Thank's for your time Dan -- iPod's ROCK! By 'screentip' I assume you're talking about comments. There is no way to use Excel functions within a comment. -- Cordially, C...

Hello again, I have an interesting situation. I basically inherited an older database that is used to enter dates, usage and charges for accounts. The situation is that we are attempting to do averages filtered by account, and averaged against the number of days that data has been entered. So, for example, we had 7 months of data entered, our query should total the number of days in the seven months (x), then average that with the total usage number (y). This data currently resides in the same table labeled "data" in the form of from and to dates (6/1/1999 to 7/1/1999) and usage (32...

I have many cells with descriptive text, does anyone know of a formula where I can take for instance a cell with 500 characters and take only the first 30 and paste it in to a new cell. I have used a formula thanks to Gary =left(a1,len(a1)/2 which has worked for other uses I have--now I need to extract only the first 30 characters of a description and I have thousands of cells in my description column..Please Help =left(a1,30) Pete.Cornejo@gmail.com wrote: > > I have many cells with descriptive text, does anyone know of a formula > where I can take for instance a cell with 500...

How do I make a cell which contains a formula display the result of that calculation rather than the formula (it is formatted as "number" and I have tried "recalculating") Peter Peter Chadbund expressed precisely : > How do I make a cell which contains a formula display the result of that > calculation rather than the formula (it is formatted as "number" and I have > tried "recalculating") > Peter Change the cell format to 'General'. Redo the formula. Change the cell format to 'Number' and apply your display preferenc...

I have a formula, bt4/37 (bt4 = 6) and it returns 5. However, my calculator and an Access database returns 16. Can someone tell me why Excel returns 5? thanks. -- Kat3n hi, Either I'm reading this post incorrectly or you have a broken calculator and are gettting results out of excel & access that are equally incorrect. 6/37= 0.162 recurring So if we assume that your result of 16 is a typo and you meant .16 there must be something your not telling us about the formula your using in Excel. How is the 6 derived in BT4 ? What is the format of BT4 ? Post the pr...

Is there a way to have a forumla reference a date and then find the closest Monday in the past? For example, if I had the date 11/19/2004 (which is a Friday) is there anyway to have a formula "calculate" the date 11/15/04 which is the most recent Monday? Try this with the date in E1 =E1-WEEKDAY(E1,2)+1 -- Regards Ron de Bruin http://www.rondebruin.nl "Woody13" <Woody13@discussions.microsoft.com> wrote in message news:48E51922-1131-496B-9274-5975D4ECC9DB@microsoft.com... > Is there a way to have a forumla reference a date and then find the closest > Mo...

Just installed Outlook 2003 running XP2...Why is it when I am composing an e mail, and next to options the HTML option is showing(not rich text etc.) but the font & font size is grayed out until I click the options button? Are not those two options supposed to be working? How do I correct this? thanks ...

Does anybody know if the average lead time in Item Vendor Maintenance counts weekends, or only business days? The average lead time is calcualted as follows: Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. If you're using Purchase Order Processing, this field will be updated each time a shipment or shipment/invoice is posted in Purchase Order Processing. The average lead time is calculated using the following formula: [(Number of Receipts) * (Average Lead Time) + (Received Date - Ordered Date)] / (...

IF cell A1 contains the word "DAM" in any part of the cell then cell A must equal "UNAVAILABLE" the key here is that the cell needs to contain the word "DAM" and if i does i need it to be identified in a seperate cell. Any suggestions?? Many Thank -- Message posted from http://www.ExcelForum.com Hi try =IF(ISNUMBER(FIND("DAM",A1)),"Unavailable","") >-----Original Message----- >IF cell A1 contains the word "DAM" in any part of the cell then cell A4 >must equal "UNAVAILABLE" > > >the key he...

Hi, I have to perform a tricky calculation using excel. In one cell I have entered the following formula: =(E2*I$2)+(E25*I$3)+(E24*I$4)+(E23*I$5)+(E22*I$6)+(E21*I$7)+(E20*I$8)+ (E19*I$9)+(E18*I$10)+(E17*I$11)+(E16*I$12)+(E15*I$13)+(E14*I$14)+(E13*I $15)+(E12*I$16)+(E11*I$17)+(E10*I$18)+(E9*I$19)+(E8*I$20)+(E7*I$21)+ (E6*I$22)+(E5*I$23)+(E4*I$24)+(E3*I$25) Now, I can;t copy this formula down to the next cell but I am expecting this formula in the next cell down: =(E3*I$2)+(E2*I$3)+(E25*I$4)+(E24*I$5)+(E23*I$6)+(E22*I$7)+(E21*I$8)+ (E20*I$9)+(E19*I$10)+(E18*I$11)+(E17*I$12)+(E16*I$13)+(E15*I...

i am using choose formula with combo box. On the basis of "Cell Link" am retriving the figures shown just against the combo box list e.g abc 100 def 200 ghi 300 now when i select "abc", with the help of choose formula 100 is show in cell d1. The porblem when i insert a new row to the list, (eg above 'def') the choose formula does not adjust itself to mention its value! Every time i have to change the choose fromula every change. Is there any easy way to do it -- Message posted from http://www.ExcelForum.com What kind of combobox is it?...

Can anyone help me with a question about an IF formula? Is there any way to write an IF formula that causes multiple actions or results to occur as part of the value_if_true or the value_if_false portions of the formula. For example if an IF Formula reads: IF(A1="1",1,0)) -- the A1="1" portion is the logical_test, and 1 is entered as the value_if_true and 0 is entered as the value_if_false is there any way to make the value_if_true cause more than one result, i.e. B1=1 and C1=1. Thank you in advance to anyone who can help. Hi Ed, No. A formula can only return a value, ...

in excel 2000 there is a edit formula button that when clicked starts the formula and produces a edit formula bar.. I find this helpful because I put it just below the rows I am working on and keeps me on the row I am working on and from getting confused when selecting cells. now i have 2003 and it has a function button which I rarely used in 2000 and I really have no use for.. can I get my edit formula button back.. which is very helpful... Same answer I gave yesrerday when you posted this question. The = sign "edit formula" went away when XL2002 was introduced. Maybe will ...

Hi group, I have a formula in a cell that takes the average Sales of the 12 rows (for Jan to Dec) like so: =Average(A1:A12)The problem is that for example, since therea are no sales figures from August to Dec, the average is underestimated because it averages it over the entire range of cells (though they are zero from Augus to Dec). How can I make this Average function to be smarter and only average those months that the sales is not zero (August to Dec is Not BLANK but its zero) Hi Average is a very simple thing: SUM/COUNT. So =SUM(A1:A12)/12 should do for all years with 12 mont...

I must be brain dead or something. I am just looking for a simple formula to return a number. I have a key: Example a 121001 b 121002 c 121003 d 121004 Column "c" will have the a, b or c item, but I want column D to autopopulate the number that corresponds to it.? simple eh? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ In D put =CODE(C1)-96+121000 where C1 holds "a", copy down so a formula might look like this =IF(C1="&quo...

In J5 I have the following formul =IF((D6<>"")*(E6<>""),LOOKUP(E6,{3,3.15,3.25,3.35,3.45,3.55,3.65,3.75},{0.38,0.43,0.46,0.49,0.52,0.56,0.59,0.62})*D6-H6,"") This is based on selling product "A" in B5. If we sell product "B,C,D,E etc." I would like to add to the formula E5-2.35*D5 to J5. Thanks in advance for your help!!! if I am reading your statement correctly =IF((D6<>"")*(E6<>""),LOOKUP(E6,{3,3.15,3.25,3.35,3.45,3.55,3.65,3.75},{0.38,0.43,0.46,0.49,0.52,0.56,0.59,0.62})*D6-H6 + if(or(B5="B...

Someone has set up a simple look up formula for me. I see "v-lookup" in the formula. Basically it's a two sheet workbook, sheet one is the master list with a column of part numbers. Two columns next to it are descriptions. Sheet two also has three columns. In the first column I enter a part number and the next two columns fill in automatically the descriptions. For some reason when I import an updated list of data into the master list, there are part numbers that don't result in a description....the cells are left blank. There is one block of 30 numbers that doesn...