error in formula #2
Using Excel 2007 and working on a xls file I am trying to count the
occurrences of a certain value (in this case "1940 Census") in a column.
I tried to follow the example in the XL Help and used this formula:
but the result I get is
and the error info says "a value used in the formula is the wrong type"
The column L2-L303 contains either blank cells, or "1940 Census", or
just words like "immigration", "History", etc.
Can someone suggest how I should correct this formula?
I also wan...Date Formula #3
Is there a simple way to lookup a value using the day of the week a
the criterion? From a list of dates, I want to see if there is
statistical correlation if a certain event happens every, say, Tuesda
rather than another day of the week. Something like this:
I want to lookup all values in this range that fall on Tuesday.
tried AutoFilter/Custom/Contains but that didn't work. Can this b
Message posted from http://www.ExcelForum.com
Looking at your dates I see they are text and not True dates. In this
case try AutoFil...Another Formula Need
Thanks for all every ones help.
But I need another formula. I changed the format of the spreadsheet.
The columns are as follows:
Date, Description, Hotel, Transport, Mileage, Mileage Cost, Meals, Phone,
Entertainment, Misc, and Total.
I need a formula in the total field that would automatically calculate the
mileage cost (e.g.miles time 0.405) with all the other fields.
I do not want the actual mileage rate of 0.405 to show on the spreadsheet. I
just want the mileage cost to appear in that field.
I am making the assumption that all the heading you have specified are
...Remove automatic date formatting
Does anyone know how to turn off the feature that automatically turns
anything that remotely looks like a date (ex: 01-01) into an actual
date? I don't want to have to format the cells or sheets every time, or
to have to add a space or ' in the cell, I just want to turn the
automatic feature OFF.
vaillancourt_ch's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32751
View this thread: http://www.excelforum.com/showthread.php?threadid=525832
No way but the ones y...Variables in URL formula help
Can anyone help me solve a simple but annoying problem?
I have a formula that links to a URL
='http://www.domain.com/18 Apr 2007/[results.xls]results'!$D$2
which gives back the value 3 (as it should)
Now I want to draw 18 Apr 2007 from a cell value found in an open file
So I tried
but it just shows the words
'http://www.domain.com/18 Apr 2007/[results.xls]results'!$D$2
instead of the value
Can anyone tell me the rig...decimal formatting
Is there a way to format cells so that the decimal digits only show if
value other than zero? For instance, if I entered "65" in a cell, i
would show up simply as 65, no decimal point showing. If I changed i
to "65.5"- same cell - then the .5 would show, without having t
physically change the setting. On calculators, it's called floatin
decimal. I checked Help, but didn't see anything like this. Am
missing something??? Thanks for any help
amyenoch's Profile: http://www.e...Formula for mm/dd/yyyy extract into separate columns?
How do I extract mm/dd/yyyy into separate columns
Sneilan, use the Text to Columns command on the Data menu. Delimited text,
with / as the separator. Afterwards, format all cells as General.
"DDM's Microsoft Office Tips and Tricks"
"Sneilan" <firstname.lastname@example.org> wrote in message
> How do I extract mm/dd/yyyy into separate columns?
If A1 = mm/dd/yyyy, then use the following formulas in
B1 = MONTH(A1)
C1 = D...Formula to give special date format but with addition?
To get this format of date in cell B1, with my abbreviated days:
I was given the cell formula of this:
It's straightforward because it just copies the date from A2 into the
correct date format for the purpose needed in this particular sheet.
However, I have some addition in another cell where I'd like to
slightly change the date format to our particular format to
standardize the ...2003 Inbox format
I am using Outlook 2003. When I view my inbox, on the right of the window I
view the e-mail that is selected on the left. How can I hide viewing the
e-mail. I only want to see it when I open it.
On 3/2/2010 12:35 PM, Stanley wrote:
> I am using Outlook 2003. When I view my inbox, on the right of the window I
> view the e-mail that is selected on the left. How can I hide viewing the
> e-mail. I only want to see it when I open it.
View -> Reading Pane -> Off
Leonid S. Knyshov
Twitt...Copying a date format
when I've copied one cell (formatted in mmm-dd) into a different worksheet
within the same workbook, I'm getting wierd results. For instance, From
worksheet A, I've copied cell C1 (which is Oct-04) into worksheet B, cell A2.
It copies fine, but when I go to change the date to 05/05, it comes out as
Jan-00. The actuals cell now reads =05/2005. However, it's still formatted
for the date. But I've never actually inputed an = sign. I've tried to just
to special paste the format, but still get the same results. Any thoughts?
Michele from Nextel
Take a look at
...Moving from one format to another
I'm going to have to move all my Outlook 2003 data to Shedule + in
Office 2000 Premium. Any thoughts on this...or am I reduced to one
item at a time til Im done??? I just discovered that the office 2003
I've been using is not authentic and I still have the Office 2000
Premium that I paid for, but now I'm retired and can't afford paying
for Office2003. Even on eBay copies are more than $300.
Eddie in Loveland
"Do not confuse advances in technology with advances in civilization."
2003 on Ebay + $300 are you sure?
If you want to open 2003 data in 2000, within 20...Fiscal Year Formatting for Money 2004
Does anyone know if Money 2004 can have budgets and reprots set up for
Australian fiscal year format, July to June, rather than calendar year?
Cannot find any reference to this capability in Help.
Nope - sorry.
Microsoft MVP - Money
For unofficial FAQs see
I do not respond to any emails that I have not specifically asked for.
"Gazza J" <Gazza J@discussions.microsoft.com> wrote in message
> Does anyone know if Money 2004 can have budgets and...Help with Formula in Excel 2003
I have a column of data which I add to twice a day for one month.
I have a second column which calculates the daily average.
I need to calculate the difference between the last entry in the average
column & the first, placing the result into another cell.
What I can't find is how to make this a dynamic update.
Any ideas please?
Microsoft MVP Excel
"Andy" <Andy@discussions.microsoft.com> wrote in message
news:418C8345-4A2E-42F2-8A0E-43FF1BC2BD13@microso...Conditional formatting #13
I can't figure this simple out.
my conditional format is in cell a2 =a2<a1+11 format pattern red
in cell a1 put 940 and in cell a2 put 950, now cell a2 will turn red ,
"type 951 in a2 and it turns white" back to 950 and red again which
is correct. a2 is less than 11 of cell a1, this works fine the
problem is change
cell a1 to 950 and cell a2 to 1000, again less than 11 but a2 doesn't
Hope I worded this correct.
Thanks for reading this, and the help.
On Fri, 17 Sep 2004 00:12:03 GMT, Bobby <camaro463...Formulas using dates, pls help!
A newbie here. I'm putting together a spreadsheet to track variou
budgets. I am stumped. What I want to do is...
I have two columns of data - projected budget and actual budget. (ie
am tracking actual funds committed vs actually invoiced). I have thes
in two columns. Under the 'invoiced?' column I have the cells with th
default "N", which I will change to "Y" when an invoice is received an
paid. I have used SUMIF to sum the 'Invoiced?' column total as a "N" i
turned into a "Y". Well heres the question.
I then want a thi...Formatting ALL data labels for ALL data series at once
I've applied data labels to a pivot chart (PivotChar Tools/Layout/Data
Then, i actually want the data series name to show instead of the value and
also want the text orientation to be vertical.
I can change this for a single data series, but not for all at once.
Am i missing something ?
You can pick Show Series for all series of labels at once, if you select the
chart, go to the Chart menu > Chart Options > Data Labels tab. This does all
series at once, not just the ones you've already labeled.
You cannot apply other formatting to more than one series...String formatting question
Is there a shorter way to express the following using a value indicating =
how many @ to
use? It just gets tedious counting those little buggers out.
"-mhd" <email@example.com> wrote in message
> Is there a shorter way to express the following using a value
> indicating how many @ to use? It just gets tedious counting
> those little buggers out.
> Format$(sString, "!@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@...save text field w/ leading zeros in .csv format & not lose zeros?
I currently have a csv file with a field that has one digit numbers. I need
to create a text field and convert the one digit number to two digit text by
adding a leading zero. I have tried using the concatenate function to add
the leading zero, but when I close the csv file, it reverts back to one
digit. How can I get the file to save the field as text and include the
Check your CSV file in Notepad, not in Excel. Excel will frequently convert
your text representation of a number into a number, even though it likely was
written out to disk with the leading zero
"...One More Formula Question
I need to get a specific total from the following criteria. I have data
in 2 columns and need to get a total based upon specific entries in the
cells in thoses columns. For example:
Column A has 4 choices from a picklist. (N,P,C,R)
Column B has a different set of choices. (ENG,OP,T&E,RS)
What I need to do is be able to get an answer for how many N's are also
doc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1434
View this thread: http://www.excelforu...Conditional formatting border thickness
In Excel 2003 when you set the border format using conditional formatting
there doesn't seem to be an option for border thickness. Is there a way of
(It's for a calendar where I want thick cell borders between cells which are
in different months and normal borders if they are in the same month).
Grateful for advice
No. Conditional formatting dont have options which might change the cell
height or width.
> In Excel 2003 when you set the border format using conditional formatting
> there doesn't...How do you create a new BLANK worksheet with same cell format as another?
Suppose I have a worksheet formatted the way I like it. In particular,
it takes some effort to get column widths adjusted and various cells to
display using various number formats. If I have such a sheet as an
exemplar, how can I create another sheet just like that except that
there is no data in it. If I copy the original sheet and Paste Special
Format, the format of column width does not transfer.
I know I can save a copy of the workbook or worksheet and then open it.
I then can clear the data in the new sheet and rename it. Is there a
simpler and more direct way?
Conserva...Delete two columns with formula
I need to delete 2 columns (with formula) and only display the last two
When I delete the first 2 columns, the last two columns display #REF!.
I know I can copy the info I need to a new sheet and paste special the
value. Is there a way to fix this on the current sheet I am working on.
"ED" <ED@discussions.microsoft.com> wrote in message
>I need to delete 2 columns (with formula) and only display the last two
> When I delete the first 2 columns, the last two columns di...formula to search and find item sheet1 copy to sheet 2
i need to find an item in a colunm on sheet1 and auto copy that items row of info to sheet2. sheet1 changes with each import of data as to where "wings" may be on line 27 one time and move to 29 on the next import. so i need to search colunm B for "wings", when "wings" is found copy that row of info to sheet2 to create a list on sheet2.the formula will need to be adjustable per line, where as i can just change the item "wings" to "burgers" then insert the formula into line 2 sheet2.this will allow sheet2 to locate "burgers" on sheet1 ...Help with setting a formula
I am attempting to setup two columns. One column is adding charges. The
other is showing the charges deducted from a starting balance. I am trying
to keep track of a cash card as I am spending the funds.
Thanks in advance for your assistance
Put your initial balance in A1 and nothing in B1
In A2 enter:
=A1-B2 and copy down
As you enter charges in B2 and going down, they will be debitted in column A
and the balance will appear.
Gary''s Student - gsnu200791
> I am attempting to setup two columns. One column is adding charges. The
...Conditional Formatting for Dummies
Another stupid inquiry from me, this time regarding conditional
I have a cell that I would like to become shaded if any of four other
cells are not blank; however, I would like it to deshade if all four
cells are not blank. So I created conditional formatting with two
#1: =OR(A1<>"",B1<>"",C1<>"",D1<>"") [SHADE CELL]
#2: =AND(A1<>"",B1<>"",C1<>"",D1<>"") [DESHADE CELL]
Clearly this isn't working, but I can't seem to figure out how to