Zeroing out data but leaving formulas.
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
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+~
> 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...Averaging numbers but ignoring < and
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
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 ...Changing all Formulas to Absolute References
Is there a way to change all the existing formulas in a workbook to
Absolute cell references?
>Is there a way to change all the existing formulas in a workbook to
>Absolute cell references?
Using a macro, yes.
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)
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usuall...How do I do an if/then formula
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"
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
> 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"
=IF(A1="","X","Have...VLOOKUP formula displays in cell, will not return data
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.
> I had an older version of Excel (97 maybe?) and our office updated to 2007.
> Now my vloo...Average of Averages
Another seemingly simple process which I can't figure. :)
I have a list of averages, and I want to get the average of them.
I need a formula to average something like the above.
=AVERAGE(A1:A4) is one possible solution.
Hope it helps.
goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838
View this thread: http://www.excelforum.com/showthread.php?threadid=513937
It really depen...averaging less than values
How do I average a column of numbers where less than values are calculated as
In the average I want the<2 to = 2. So the answer should be 3
Assuming source data as posted in A2:A7
Paste this in say, B2,
then press CTRL+SHIFT+ENTER to array-enter the formula:
Adapt the ranges to suit
Downloads:17,500 Files:358 Subscribers:55
> How do I average ...Formula for deleting "
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.
have you tried 'Edit - Replace' for this operation?.
You may also have a look at:
> I have imported a CVS file delimited on commas.
> I got "&quo...Entering a formula into a screentip
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
By 'screentip' I assume you're talking about comments. There is
no way to use Excel functions within a comment.
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...formula for cell
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
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
> I have many cells with descriptive text, does anyone know of a formula
> where I can take for instance a cell with 500...display result of formula
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
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")
Change the cell format to 'General'.
Redo the formula.
Change the cell format to 'Number' and apply your display preferenc...Simple Formula
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?
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...Date Formula #9
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
Regards Ron de Bruin
"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...HTML #6
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?
...Average Lead Time
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 Function formula
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.
Message posted from http://www.ExcelForum.com
>IF cell A1 contains the word "DAM" in any part of the
cell then cell A4
>must equal "UNAVAILABLE"
>the key he...Tricky Formulas
I have to perform a tricky calculation using excel. In one cell I have
entered the following formula:
Now, I can;t copy this formula down to the next cell but I am
expecting this formula in the next cell down:
(E20*I$9)+(E19*I$10)+(E18*I$11)+(E17*I$12)+(E16*I$13)+(E15*I...Combo-box with Choose formula
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
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?...Help with an IF Formula
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.
A formula can only return a value, ...edit formula bar in excel 2003?
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 ...Average Function
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
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)
Average is a very simple thing: SUM/COUNT. So
should do for all years with 12 mont...Simple formula
I must be brain dead or something. I am just looking for a simple
formula to return a number. I have a key:
Column "c" will have the a, b or c item, but I want column D to
autopopulate the number that corresponds to it.?
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
In D put
where C1 holds "a", copy down
so a formula might look like this
=IF(C1="&quo...excel formulas #18
In J5 I have the following formul
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(or(B5="B...a simple v-look up formula problem
Someone has set up a simple look up formula for me. I see "v-lookup" in
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...