Counting the number of rows between identical data.

Hello,

  I have a spreadsheet that lists the number of F1 races and the drivers who 
won those races.

I'm trying to count the races that elapsed between wins.

Column B contains the race #:  (from 1 through 754)

Column L has the race winners.

In Column M, I want to count how many races it's been since a driver last 
won a race.

For example:  Giancarlo Fisichella won his 2nd race at the 2005 Australian 
G. P. (Race # 732)

He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had 
elapsed.

Is there a formula I can use in Column M that will display 20?

Thank you for your help,

Art.

P.S. - I tried using "Match", but that only counts the number of races 
between the 1st win and the most recent. This works when the most recent win 
was the 2nd career win, but it won't work when a driver has had more than 2 
wins.





0
artmacneil (88)
4/22/2006 2:23:51 AM
excel 39879 articles. 2 followers. Follow

7 Replies
582 Views

Similar Articles

[PageSpeed] 36

Hi!

Try this: (I guess you want a progressive total):

Joe 1
xx
xx
xx
Joe 4
xx
xx
Joe 3

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))

Copy down as needed.

Biff

"Art MacNeil" <artmacneil@shaw.ca> wrote in message 
news:Xsg2g.58824$WI1.770@pd7tw2no...
> Hello,
>
>  I have a spreadsheet that lists the number of F1 races and the drivers 
> who won those races.
>
> I'm trying to count the races that elapsed between wins.
>
> Column B contains the race #:  (from 1 through 754)
>
> Column L has the race winners.
>
> In Column M, I want to count how many races it's been since a driver last 
> won a race.
>
> For example:  Giancarlo Fisichella won his 2nd race at the 2005 Australian 
> G. P. (Race # 732)
>
> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had 
> elapsed.
>
> Is there a formula I can use in Column M that will display 20?
>
> Thank you for your help,
>
> Art.
>
> P.S. - I tried using "Match", but that only counts the number of races 
> between the 1st win and the most recent. This works when the most recent 
> win was the 2nd career win, but it won't work when a driver has had more 
> than 2 wins.
>
>
>
>
> 


0
biffinpitt (3172)
4/22/2006 4:59:42 AM
>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
> news:Xsg2g.58824$WI1.770@pd7tw2no...
>> Hello,
>>
>>  I have a spreadsheet that lists the number of F1 races and the drivers 
>> who won those races.
>>
>> I'm trying to count the races that elapsed between wins.
>>
>> Column B contains the race #:  (from 1 through 754)
>>
>> Column L has the race winners.
>>
>> In Column M, I want to count how many races it's been since a driver last 
>> won a race.
>>
>> For example:  Giancarlo Fisichella won his 2nd race at the 2005 
>> Australian G. P. (Race # 732)
>>
>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had 
>> elapsed.
>>
>> Is there a formula I can use in Column M that will display 20?
>>
>> Thank you for your help,
>>
>> Art.
>>
>> P.S. - I tried using "Match", but that only counts the number of races 
>> between the 1st win and the most recent. This works when the most recent 
>> win was the 2nd career win, but it won't work when a driver has had more 
>> than 2 wins.
>>


>"Biff" <biffinpitt@comcast.net> wrote in message 
>news:%23vhaSmcZGHA.3992@TK2MSFTNGP05.phx.gbl...
> Hi!
>
> Try this: (I guess you want a progressive total):
>
> Joe 1
> xx
> xx
> xx
> Joe 4
> xx
> xx
> Joe 3
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))
>
> Copy down as needed.
>
> Biff



Thanks Biff.

  I tried it and it seems like it should work but it didn't.  When I dragged 
the formula down, every row displayed a 1 as a result. I changed your "L1" 
and "B1" references to "L2" and "B2" because my 1st row is used for column 
titles. The weird thing is, when I look at the function, the TRUE answer is 
displayed as 1, and the FALSE answer is correct, but is doesn't appear in 
the cell, a "1" does.. So the formula works, but it always displays a 1 when 
the FALSE condition applies. Any ideas?

Thank you,

Art. 


0
artmacneil (88)
4/22/2006 5:32:58 AM
The formula is an array formula. For it to work properly you MUST enter it 
using the key combination of CTRL,SHIFT,ENTER.

Select the first formula cell in column M.

Double click that cell to be in Edit mode.
Hold down both the CTRL key and the SHIFT key then hit ENTER.
When done properly Excel will enclose the formula in squiggly braces { }. 
You cannot just type the braces in, you MUST use the key combo. Also, if you 
edit an array formula it MUST be re-entered as an array using the key combo.

Then copy the array formula down as needed.

The first result has to be 1!

Biff

"Art MacNeil" <artmacneil@shaw.ca> wrote in message 
news:eej2g.59075$WI1.54626@pd7tw2no...
>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
>> news:Xsg2g.58824$WI1.770@pd7tw2no...
>>> Hello,
>>>
>>>  I have a spreadsheet that lists the number of F1 races and the drivers 
>>> who won those races.
>>>
>>> I'm trying to count the races that elapsed between wins.
>>>
>>> Column B contains the race #:  (from 1 through 754)
>>>
>>> Column L has the race winners.
>>>
>>> In Column M, I want to count how many races it's been since a driver 
>>> last won a race.
>>>
>>> For example:  Giancarlo Fisichella won his 2nd race at the 2005 
>>> Australian G. P. (Race # 732)
>>>
>>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had 
>>> elapsed.
>>>
>>> Is there a formula I can use in Column M that will display 20?
>>>
>>> Thank you for your help,
>>>
>>> Art.
>>>
>>> P.S. - I tried using "Match", but that only counts the number of races 
>>> between the 1st win and the most recent. This works when the most recent 
>>> win was the 2nd career win, but it won't work when a driver has had more 
>>> than 2 wins.
>>>
>
>
>>"Biff" <biffinpitt@comcast.net> wrote in message 
>>news:%23vhaSmcZGHA.3992@TK2MSFTNGP05.phx.gbl...
>> Hi!
>>
>> Try this: (I guess you want a progressive total):
>>
>> Joe 1
>> xx
>> xx
>> xx
>> Joe 4
>> xx
>> xx
>> Joe 3
>>
>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>
>> =IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))
>>
>> Copy down as needed.
>>
>> Biff
>
>
>
> Thanks Biff.
>
>  I tried it and it seems like it should work but it didn't.  When I 
> dragged the formula down, every row displayed a 1 as a result. I changed 
> your "L1" and "B1" references to "L2" and "B2" because my 1st row is used 
> for column titles. The weird thing is, when I look at the function, the 
> TRUE answer is displayed as 1, and the FALSE answer is correct, but is 
> doesn't appear in the cell, a "1" does.. So the formula works, but it 
> always displays a 1 when the FALSE condition applies. Any ideas?
>
> Thank you,
>
> Art.
> 


0
biffinpitt (3172)
4/22/2006 6:18:12 AM
Here's a slight variation of Biff's solution...

First, define the following name...

Insert > Name > Define

Name:  BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try the following formula, which needs to be confirmed with 
CONTROL+SHIFT+ENTER...

M2, copied down:

=LOOKUP(BigNum,CHOOSE({1,2},0,SUM(LARGE(IF($L$2:L2=L2,$B$2:B2),{1,2})*{1,
-1})))

Hope this helps!

In article <Xsg2g.58824$WI1.770@pd7tw2no>,
 "Art MacNeil" <artmacneil@shaw.ca> wrote:

> Hello,
> 
>   I have a spreadsheet that lists the number of F1 races and the drivers who 
> won those races.
> 
> I'm trying to count the races that elapsed between wins.
> 
> Column B contains the race #:  (from 1 through 754)
> 
> Column L has the race winners.
> 
> In Column M, I want to count how many races it's been since a driver last 
> won a race.
> 
> For example:  Giancarlo Fisichella won his 2nd race at the 2005 Australian 
> G. P. (Race # 732)
> 
> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had 
> elapsed.
> 
> Is there a formula I can use in Column M that will display 20?
> 
> Thank you for your help,
> 
> Art.
> 
> P.S. - I tried using "Match", but that only counts the number of races 
> between the 1st win and the most recent. This works when the most recent win 
> was the 2nd career win, but it won't work when a driver has had more than 2 
> wins.
0
domenic22 (716)
4/22/2006 12:46:46 PM
Right you are.  It works now, thank you.


"Biff" <biffinpitt@comcast.net> wrote in message 
news:ubNDKSdZGHA.3972@TK2MSFTNGP04.phx.gbl...
> The formula is an array formula. For it to work properly you MUST enter it 
> using the key combination of CTRL,SHIFT,ENTER.
>
> Select the first formula cell in column M.
>
> Double click that cell to be in Edit mode.
> Hold down both the CTRL key and the SHIFT key then hit ENTER.
> When done properly Excel will enclose the formula in squiggly braces { }. 
> You cannot just type the braces in, you MUST use the key combo. Also, if 
> you edit an array formula it MUST be re-entered as an array using the key 
> combo.
>
> Then copy the array formula down as needed.
>
> The first result has to be 1!
>
> Biff
>
> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
> news:eej2g.59075$WI1.54626@pd7tw2no...
>>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
>>> news:Xsg2g.58824$WI1.770@pd7tw2no...
>>>> Hello,
>>>>
>>>>  I have a spreadsheet that lists the number of F1 races and the drivers 
>>>> who won those races.
>>>>
>>>> I'm trying to count the races that elapsed between wins.
>>>>
>>>> Column B contains the race #:  (from 1 through 754)
>>>>
>>>> Column L has the race winners.
>>>>
>>>> In Column M, I want to count how many races it's been since a driver 
>>>> last won a race.
>>>>
>>>> For example:  Giancarlo Fisichella won his 2nd race at the 2005 
>>>> Australian G. P. (Race # 732)
>>>>
>>>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had 
>>>> elapsed.
>>>>
>>>> Is there a formula I can use in Column M that will display 20?
>>>>
>>>> Thank you for your help,
>>>>
>>>> Art.
>>>>
>>>> P.S. - I tried using "Match", but that only counts the number of races 
>>>> between the 1st win and the most recent. This works when the most 
>>>> recent win was the 2nd career win, but it won't work when a driver has 
>>>> had more than 2 wins.
>>>>
>>
>>
>>>"Biff" <biffinpitt@comcast.net> wrote in message 
>>>news:%23vhaSmcZGHA.3992@TK2MSFTNGP05.phx.gbl...
>>> Hi!
>>>
>>> Try this: (I guess you want a progressive total):
>>>
>>> Joe 1
>>> xx
>>> xx
>>> xx
>>> Joe 4
>>> xx
>>> xx
>>> Joe 3
>>>
>>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>>
>>> =IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))
>>>
>>> Copy down as needed.
>>>
>>> Biff
>>
>>
>>
>> Thanks Biff.
>>
>>  I tried it and it seems like it should work but it didn't.  When I 
>> dragged the formula down, every row displayed a 1 as a result. I changed 
>> your "L1" and "B1" references to "L2" and "B2" because my 1st row is used 
>> for column titles. The weird thing is, when I look at the function, the 
>> TRUE answer is displayed as 1, and the FALSE answer is correct, but is 
>> doesn't appear in the cell, a "1" does.. So the formula works, but it 
>> always displays a 1 when the FALSE condition applies. Any ideas?
>>
>> Thank you,
>>
>> Art.
>>
>
> 


0
artmacneil (88)
4/22/2006 4:36:47 PM
That is an interesting idea.  I may not use it for this problem but 
hopefully I can use it to solve another one.  Thank you for your time.


"Domenic" <domenic22@sympatico.ca> wrote in message 
news:domenic22-EE0689.08464322042006@msnews.microsoft.com...
> Here's a slight variation of Biff's solution...
>
> First, define the following name...
>
> Insert > Name > Define
>
> Name:  BigNum
>
> Refers to:
>
> =9.99999999999999E+307
>
> Click Ok
>
> Then try the following formula, which needs to be confirmed with
> CONTROL+SHIFT+ENTER...
>
> M2, copied down:
>
> =LOOKUP(BigNum,CHOOSE({1,2},0,SUM(LARGE(IF($L$2:L2=L2,$B$2:B2),{1,2})*{1,
> -1})))
>
> Hope this helps!
>
> In article <Xsg2g.58824$WI1.770@pd7tw2no>,
> "Art MacNeil" <artmacneil@shaw.ca> wrote:
>
>> Hello,
>>
>>   I have a spreadsheet that lists the number of F1 races and the drivers 
>> who
>> won those races.
>>
>> I'm trying to count the races that elapsed between wins.
>>
>> Column B contains the race #:  (from 1 through 754)
>>
>> Column L has the race winners.
>>
>> In Column M, I want to count how many races it's been since a driver last
>> won a race.
>>
>> For example:  Giancarlo Fisichella won his 2nd race at the 2005 
>> Australian
>> G. P. (Race # 732)
>>
>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
>> elapsed.
>>
>> Is there a formula I can use in Column M that will display 20?
>>
>> Thank you for your help,
>>
>> Art.
>>
>> P.S. - I tried using "Match", but that only counts the number of races
>> between the 1st win and the most recent. This works when the most recent 
>> win
>> was the 2nd career win, but it won't work when a driver has had more than 
>> 2
>> wins. 


0
artmacneil (88)
4/22/2006 4:37:56 PM
You're welcome!

Biff

"Art MacNeil" <artmacneil@shaw.ca> wrote in message 
news:zYs2g.60021$P01.38667@pd7tw3no...
> Right you are.  It works now, thank you.
>
>
> "Biff" <biffinpitt@comcast.net> wrote in message 
> news:ubNDKSdZGHA.3972@TK2MSFTNGP04.phx.gbl...
>> The formula is an array formula. For it to work properly you MUST enter 
>> it using the key combination of CTRL,SHIFT,ENTER.
>>
>> Select the first formula cell in column M.
>>
>> Double click that cell to be in Edit mode.
>> Hold down both the CTRL key and the SHIFT key then hit ENTER.
>> When done properly Excel will enclose the formula in squiggly braces { }. 
>> You cannot just type the braces in, you MUST use the key combo. Also, if 
>> you edit an array formula it MUST be re-entered as an array using the key 
>> combo.
>>
>> Then copy the array formula down as needed.
>>
>> The first result has to be 1!
>>
>> Biff
>>
>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
>> news:eej2g.59075$WI1.54626@pd7tw2no...
>>>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
>>>> news:Xsg2g.58824$WI1.770@pd7tw2no...
>>>>> Hello,
>>>>>
>>>>>  I have a spreadsheet that lists the number of F1 races and the 
>>>>> drivers who won those races.
>>>>>
>>>>> I'm trying to count the races that elapsed between wins.
>>>>>
>>>>> Column B contains the race #:  (from 1 through 754)
>>>>>
>>>>> Column L has the race winners.
>>>>>
>>>>> In Column M, I want to count how many races it's been since a driver 
>>>>> last won a race.
>>>>>
>>>>> For example:  Giancarlo Fisichella won his 2nd race at the 2005 
>>>>> Australian G. P. (Race # 732)
>>>>>
>>>>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had 
>>>>> elapsed.
>>>>>
>>>>> Is there a formula I can use in Column M that will display 20?
>>>>>
>>>>> Thank you for your help,
>>>>>
>>>>> Art.
>>>>>
>>>>> P.S. - I tried using "Match", but that only counts the number of races 
>>>>> between the 1st win and the most recent. This works when the most 
>>>>> recent win was the 2nd career win, but it won't work when a driver has 
>>>>> had more than 2 wins.
>>>>>
>>>
>>>
>>>>"Biff" <biffinpitt@comcast.net> wrote in message 
>>>>news:%23vhaSmcZGHA.3992@TK2MSFTNGP05.phx.gbl...
>>>> Hi!
>>>>
>>>> Try this: (I guess you want a progressive total):
>>>>
>>>> Joe 1
>>>> xx
>>>> xx
>>>> xx
>>>> Joe 4
>>>> xx
>>>> xx
>>>> Joe 3
>>>>
>>>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>>>
>>>> =IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))
>>>>
>>>> Copy down as needed.
>>>>
>>>> Biff
>>>
>>>
>>>
>>> Thanks Biff.
>>>
>>>  I tried it and it seems like it should work but it didn't.  When I 
>>> dragged the formula down, every row displayed a 1 as a result. I changed 
>>> your "L1" and "B1" references to "L2" and "B2" because my 1st row is 
>>> used for column titles. The weird thing is, when I look at the function, 
>>> the TRUE answer is displayed as 1, and the FALSE answer is correct, but 
>>> is doesn't appear in the cell, a "1" does.. So the formula works, but it 
>>> always displays a 1 when the FALSE condition applies. Any ideas?
>>>
>>> Thank you,
>>>
>>> Art.
>>>
>>
>>
>
> 


0
biffinpitt (3172)
4/22/2006 5:17:30 PM
Reply:

Similar Artilces:

Long numbers show up as Scientific Notation
Hello, We have a web based asset tracking system, and some assets have long serial numbers; for instance, 3100600300030147987 or 3101600300100020220 When we import, cut and paste, or open an HTML format page in Excel with these serial numbers, they all show up as: 3.1016E+18 and the last 6 digits get rounded up. How do we show the correct value for these long numbers in excel? Thanks On Mon, 7 Feb 2005 08:59:07 -0800, berryware421243 <berryware421243@discussions.microsoft.com> wrote: >Hello, > >We have a web based asset tracking system, and some assets have long seria...

WriteXml, DateTime, and Timezone data
This is a multi-part message in MIME format. ------=_NextPart_000_0040_01C5D4C2.CF4AB1D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Greetings. I am using a myDataSet.WriteXml(StringWriter) function (and then using = the ToString() function to convert it to a string) to build an XML = string based on the contents of my dataset. This in itself works great. I am then passing this string back to a PowerBuilder module that is = taking this XML string and doing what it needs to do with it. This is = almost working great as well. It wo...

entering new data in a saved spreadsheet without losing formulas?
How do I do this? Hi Jackie, As long as you do not overwrite the formulas, then any new data will not affect the formula. I suspect though that there is more to this question. Are you concerned about writing new data into a cell that has a formula in it? The way to stop a cell's data being overwritten is to put protection onto it. This means taking protection off all the other cells otherwise you will not be able to write data to any cell. First select the whole worksheet concerned, then go to Format\Cells and select the protection tab and unselect the 'locked' tick box,...

Help with using data
Hi I would really appreciate some help with extracting data from Outlook. I have been building up a database of clients/prospectives, these are in various categories. I would like to extract/search on various fields as well as my own categories, all i can do with export is extract the whole lot to a tab delimited file. Or export categories to an rge file which I can't use. Any ideas out there? thanks Outlook's Search doesn't suffice? There are 3rd party search applications that might help. What sprcifically stops you from exporting to a format other than TSV? There are other...

How to diffrentiate Text & number from a single cell????
Hi ther guyz............i a have small problem with the execl sheets........... my problem is e.g. i have alot of cells with a number and some text in it........and they both are in one/single cell (e.g. "878 queen st" in A1)....now what i want is to remove let's say "queen st" from each cell so that there is only number left in it.....................i cannot figure out any formula........if there is any..............need help imediately -- pack_card2000 ------------------------------------------------------------------------ pack_card2000's Profile: http://www...

Highlight Rows
Is there a way to program an expression close to... If cell in column E equals string of text "Pending", select entire row and change background color/patern to yellow? Hi, Conditional formatting. Select the row(s) to which this applies then Format|Conditional format - Select 'Formula is' from the dropdown and enter the formula =$E2="Pending" Click Format and on the 'Pattern' tab and choose yellow. OK Note that you should change the 2 to the top row of your selection -- Mike When competing hypotheses are otherwise equal, adopt th...

Data format for pages
I want to change the format control properties "DataFormatAs" to a currency in Data Access Page (access 2000). Its asking for a code but I dont how?. Its easier in newer versions Select the Data Access Page > choose design > double click the field/cell whose format you want changed > Hit Design tab > Format > Change to Currency. -- HTP Adnan "Newguy" wrote: > I want to change the format control properties "DataFormatAs" to a currency > in Data Access Page (access 2000). Its asking for a code but I dont how?. Its > easier in newer ...

Clean all "data" from a database
Access 2007 on Win XP I have an existing database that I want to use for a new function. I do want to start out with empty tables after the demonstration and training phase. Therefore I am looking for a way to empty all data records from all tables. I have read and understand the process of creating a new database and exporting the tables as structure or definition only. My question is in Access 2007 is it possible to execute this procedure on more than one table, query,form, or report at a time? I have 10 tables, 20 queries, 22 forms, and 32 reports. I have attemptted to se...

COUNT expression in a report
> Hi > I have a field, named [Type]. > In a report, what is the expression to count this field “[Type]” where > Type="Truck" > ??? =Count([Type]) where type = “Truck” > Thanks Trying, In the Control Source of an unbound textbox in the Footer or Header of the report... =Sum(IIf([Type]="Truck",1,0)) -- Steve Schapel, Microsoft Access MVP Trying wrote: >> Hi >> I have a field, named [Type]. >> In a report, what is the expression to count this field �[Type]� where >> Type="Truck" >> ??? =Count([Type]) wher...

hide row/cols
How can I hide my row/col numbers, like the built-in Invoice Template does? Thanks, Andre Hi goto 'Tools - Options - View' -- Regards Frank Kabel Frankfurt, Germany Andre wrote: > How can I hide my row/col numbers, like the built-in Invoice Template > does? > > Thanks, Andre perfect - thanks! ...

FRx 6.7: Accounts Not Displaying In Row File
I have successfully created Financial Statements for two of our three companies. However, I’m having a problem with the third one. In creating the row file, I specify a range from account 30000 through 99999. Most accounts show up in the list. Some don’t. The following account string will be my example of an existing account, with a balance, that does not show up in the row file: 00000-30035-00000 (30035 is the main account) If I manually insert a row into the row file, type in the description, and click the down arrow for the GL Link, the account does not display in the drop down. Ho...

Chart with merged rows
Another question for EXCEL gurus ! I have a chart with 56 rows (corresponding to 56 weeks in a year), each of which is composed of merging 7 rows (corresponding to 7 days/week). How can I get a graph with the values in these merged columns ? Selecting these and clicking the chart icon does not produce anything ! X-axis C, Y-axis - D -------------------------------------------------------------------------------- A B C D --------------------------------------------------------------------------------- 13. Aug. Sunday ...

Visio Text (Number) Automation
Hi. I'm creating an Organization Chart for my company. I have the org charts already setup with names, shapes, etc; however, I would like to add the total number or employees in each group, which would add up to larger group, and finally all the way to the top which will have the total number for all employees. i would like to somehow automate this process like excel does wtih a formula (=techdept+accoutningdept) which would add the numbers i manually filled in the tech and accouting dept, but would add those number together for the group above that. Thanks. ...

ROUNDED NUMBERS IN FORMULA'S
Can anybody help me to set up Excel so that it uses rounded numbers in formulas? I have got the cell formatted to 'show' the number rounded to two decimal places but the complete root number (shown in the formula bar), is used! Eg. 1.5642 (1.56 shown) x 4 = 6.2568 (6.25 shown) I want to multiply 1.56 x 4 = 6.24, without going into each individual cell and removing the unrequired numbers. I hope that somebody can help me - even if it is to tell me to stop trying because it's maybe not possible. I thank anybody in advance for any constructive response. bing1080 =round(A1...

Number of rows in a worksheet
I have a very large data file. It has 110,000 records approximately. want to import it into an excel worksheet. I did so using the Impor Text function and defining the field widths with !. Now the most I can seem to import onto a worrksheet is approximatel 65,500 records. The wizard then advises me to import the rest ont another data sheet, excluding the data already imported on to the firs sheet. When I try to do this, the wizard will only allow me to exclude th first 32,000 records from the second import and then will only impor approxiately 32,000 more records. In effect the second impor...

Problem importing data from text file
Hi, I have a comma delimited file that has twenty column names followed by six rows of data. I am trying to import this into Excel. When I do this, the data imports entirely into row one. It does import into difference cells but there is one row with 120 cells of data. I was expecting it two "Wrap" and start importing the data into cell A2. Can anyone help with this? How do I show the break between column headers and data? Thanks, Chuck ...

How do I transfer email data from WinXp to Windows 7
I just set up a new computer with Windows 7 and need to transfer my email folders and emails contained therein from my old computer which has outlook express in WinXP. I would prefer to transfer the data with a USB flash drive as I have no cables or other perhipherals to utilize another method. I've checked the internet and tried several methods suggested therein but nothing has worked. Moreover, theres no reference with regard to transferring from Outlook Express to Windows Live Mail (Windows 7), if that has any bearing on my ability to effect the transfer. This may not b...

protect row color
I have a spread shhet that I have alternating row colors. If I do a data sort it moves the color along with the data. How do I protect the rows format so this does not happen Sounds as if the rows were coloured manually. Remove the colours, select the rows to shade using Format|Conditional Formatting. One way to specify every second row is MOD(ROW(),2)=0 Bernard "KIRK" <awm@cyberport.net> wrote in message news:1c4bd01c38850$83f15ba0$a601280a@phx.gbl... > I have a spread shhet that I have alternating row colors. > If I do a data sort it moves the color along with the &...

Data Validation
What I am trying to achieve is a drop down list that would look something like this: D1a - suipplies F1g - cell phones C2a - travel E4b - computers and so on and so forth. I can do that. What I would like to happen though is in the same cell that the drop down box is in (a29) I would like to select one of those options and only have D1a, or F1f, ect show up in the cell. The words are just references to my budget, and I don't nned them after I selected the right one. Is this possible? I am somewhat new to excel and have never went into the visual basic editor and I don't have...

How I can set highlighted active cell in row & colmun cross
I want to set active cell highlighted in row & colmun cross as it was possible in Lotus 123. Is it possible? if yes, how is it? Option Explicit '---------------------------------------------------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target With .EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) ...

excel data base modification
hi there. i need some help please guys. i have exported some names & telephone numbers from a software packag (sms centre) & im trying 2import them in2 a package called e-txt. thes packages reads csv files. ive encountered a problem my exported files r saved in excel lik this... name - country code - area code - phon number joe bloggs - 44 - 07977 - 797898 but the e-txt database fields r as follows 1st name - surname - phone number joe - bloggs - 447977797898 ...

Importing data into lead with custom attribute
I have created a custom attribute (gg_category) for the lead entity. The attribute exists on the default lead form. The customizations have been published and IISRESET has been issued. When I try and import data from a "clean" CSV file, I get the following error: 'Lead' entity doesn't contain attribute with Name = 'gg_category'. What am I missing here? Is there an extra step that I have left out? Thanks. Or ...

Create List/Add Row
Hello, I used the "Create List" function for a number of columns in a worksheet - but not all. I need to add a row, and I keep getting a message that says, "This operation is not allowed. The operation is attempting to shift cells in a list on your worksheet." I can add a row IN a list, but it only adds it in one column and doesn't carry it across the worksheet. It's when I try to add a row outside of a list that I get the error. Is there a way to correct this, so I can add/delete rows as needed? Thank you, in advance. Maybe you can convert e...

Merging Data into Word and Want Checkboxes (Yes/No data type)
I am trying to merge data from my access database into word. I have several items that are checkboxes in access. How do i get those checkboxes to merge into word so that i see them. Right now they just produce numbers if they are checked or not. Thanks I'm not sure you can do with with a straight mail merge. The following is a snippet of code from one of my applications. The app relies on documents that have already been bookmarked with simple bookmarks (for fields longer than 255 characters), checkboxes (for y/n fields), and textboxes for everything else. The merging is d...

MS Query Data Source Change
I have a series of saved queries that I use on an ODBC data source. I would like to put them on other computers that may not have the same data sources but have different ones (tables are the same in each data source). Right now I am editing the query in Notepad to change the data source and path to the correct one on the other computers but am wondering why it is not evident how to change this in the query itself. Can it be done? How? TIA I had a similiar problem which I solved with a form/macro. (I don't think you can point to a different source other than manually changing t...