#### number of cells

```Hi there;
1-What is the equation to find the number of cells in a range which their
values are more than 0.0?

2- How to count the number of cells in range which for every 3 consqueative
cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count
= 1 then next 3 cells, ... and if one 3 consequative cells have (even one
cell) a 0.0 value then that 3 cells donot add to our count number?

Appreciate any help.
Best
Darius

```
 0
Darius (16)
9/10/2005 2:50:02 PM
excel.newusers 15348 articles. 2 followers.

10 Replies
411 Views

Similar Articles

[PageSpeed] 48

```Is this a homework assingment.

look in help index for COUNTIF

--
Don Guillett
SalesAid Software
donaldb@281.com
"Darius" <Darius@discussions.microsoft.com> wrote in message
news:A65E1303-66AB-4656-A052-8D8EC585D790@microsoft.com...
> Hi there;
> 1-What is the equation to find the number of cells in a range which their
> values are more than 0.0?
>
> 2- How to count the number of cells in range which for every 3
consqueative
> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
count
> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> cell) a 0.0 value then that 3 cells donot add to our count number?
>
> Appreciate any help.
> Best
> Darius
>

```
 0
Don
9/10/2005 3:50:16 PM
```Hi Darius,

I'm a Newbie and Dummy here, but maybe this is (can be) a solution.

Lets say your numbers are in A1 - A50.
Use Colom B.
B1 = if(a1>0;1;0)
Copie this from B1 to B50

Then count (B1:B50).  (Zigma)

Maybe....

Just Try it ................

:cool:

--
skrol
------------------------------------------------------------------------
skrol's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27126

```
 0
9/10/2005 4:18:59 PM
```I wish I was that much young, no it is not homework,
Skrol I am afraid you way is not that much efficient. hope I get some other
response
Best
Darius
"Darius" wrote:

> Hi there;
> 1-What is the equation to find the number of cells in a range which their
> values are more than 0.0?
>
> 2- How to count the number of cells in range which for every 3 consqueative
> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count
> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> cell) a 0.0 value then that 3 cells donot add to our count number?
>
> Appreciate any help.
> Best
> Darius
>
```
 0
Darius (16)
9/10/2005 8:05:01 PM
```Hi Darius,

For the first task use the following formula:

=COUNTIF(A1:A10,">0")

Regards,
KL

"Darius" <Darius@discussions.microsoft.com> wrote in message
news:A65E1303-66AB-4656-A052-8D8EC585D790@microsoft.com...
> Hi there;
> 1-What is the equation to find the number of cells in a range which their
> values are more than 0.0?
>
> 2- How to count the number of cells in range which for every 3
> consqueative
> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> count
> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> cell) a 0.0 value then that 3 cells donot add to our count number?
>
> Appreciate any help.
> Best
> Darius
>

```
 0
9/10/2005 8:16:14 PM
```Hi again,

For task 2 try the following:

=SUMPRODUCT(--(COUNTIF(OFFSET(A1:A10,(ROW(INDIRECT("1:"&ROWS(A1:A10)/3))-1)*3,,3),">0")=3))

Reagards,
KL

> Hi Darius,
>
> For the first task use the following formula:
>
> =COUNTIF(A1:A10,">0")
>
> Regards,
> KL
>
> "Darius" <Darius@discussions.microsoft.com> wrote in message
> news:A65E1303-66AB-4656-A052-8D8EC585D790@microsoft.com...
>> Hi there;
>> 1-What is the equation to find the number of cells in a range which their
>> values are more than 0.0?
>>
>> 2- How to count the number of cells in range which for every 3
>> consqueative
>> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
>> count
>> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
>> cell) a 0.0 value then that 3 cells donot add to our count number?
>>
>> Appreciate any help.
>> Best
>> Darius
>>
>
>

```
 0
9/10/2005 8:25:17 PM
```thanks but the second equation (below)which I use for "E3:E94" and contains
numbers, results in:
#REF
Any suggestion
=SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))

Best
Darius
"KL" wrote:

> Hi Darius,
>
> For the first task use the following formula:
>
> =COUNTIF(A1:A10,">0")
>
> Regards,
> KL
>
> "Darius" <Darius@discussions.microsoft.com> wrote in message
> news:A65E1303-66AB-4656-A052-8D8EC585D790@microsoft.com...
> > Hi there;
> > 1-What is the equation to find the number of cells in a range which their
> > values are more than 0.0?
> >
> > 2- How to count the number of cells in range which for every 3
> > consqueative
> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> > count
> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >
> > Appreciate any help.
> > Best
> > Darius
> >
>
>
>
```
 0
Darius (16)
9/10/2005 9:04:01 PM
```I assumed the number of cells in your range would be a multiple of 3 :-( Try
this:

=SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))

KL

"Darius" <Darius@discussions.microsoft.com> wrote in message
news:DABC80BC-9494-4AC8-B677-A179D3E9DDDC@microsoft.com...
> thanks but the second equation (below)which I use for "E3:E94" and
> contains
> numbers, results in:
> #REF
> Any suggestion?
> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
>
> Best
> Darius
> "KL" wrote:
>
>> Hi Darius,
>>
>> For the first task use the following formula:
>>
>> =COUNTIF(A1:A10,">0")
>>
>> Regards,
>> KL
>>
>> "Darius" <Darius@discussions.microsoft.com> wrote in message
>> news:A65E1303-66AB-4656-A052-8D8EC585D790@microsoft.com...
>> > Hi there;
>> > 1-What is the equation to find the number of cells in a range which
>> > their
>> > values are more than 0.0?
>> >
>> > 2- How to count the number of cells in range which for every 3
>> > consqueative
>> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
>> > count
>> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even
>> > one
>> > cell) a 0.0 value then that 3 cells donot add to our count number?
>> >
>> > Appreciate any help.
>> > Best
>> > Darius
>> >
>>
>>
>>

```
 0
9/10/2005 9:10:42 PM
```Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
which of those "3" should be replaced by 5?
Thanks a lot.
Besr
Darius

"KL" wrote:

> I assumed the number of cells in your range would be a multiple of 3 :-( Try
> this:
>
> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
>
> KL
>
>
> "Darius" <Darius@discussions.microsoft.com> wrote in message
> news:DABC80BC-9494-4AC8-B677-A179D3E9DDDC@microsoft.com...
> > thanks but the second equation (below)which I use for "E3:E94" and
> > contains
> > numbers, results in:
> > #REF
> > Any suggestion?
> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
> >
> > Best
> > Darius
> > "KL" wrote:
> >
> >> Hi Darius,
> >>
> >> For the first task use the following formula:
> >>
> >> =COUNTIF(A1:A10,">0")
> >>
> >> Regards,
> >> KL
> >>
> >> "Darius" <Darius@discussions.microsoft.com> wrote in message
> >> news:A65E1303-66AB-4656-A052-8D8EC585D790@microsoft.com...
> >> > Hi there;
> >> > 1-What is the equation to find the number of cells in a range which
> >> > their
> >> > values are more than 0.0?
> >> >
> >> > 2- How to count the number of cells in range which for every 3
> >> > consqueative
> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> >> > count
> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even
> >> > one
> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >> >
> >> > Appreciate any help.
> >> > Best
> >> > Darius
> >> >
> >>
> >>
> >>
>
>
>
```
 0
Darius (16)
9/10/2005 9:27:02 PM
```actually, all 4 of them :-)

KL

"Darius" <Darius@discussions.microsoft.com> wrote in message
news:A6067091-0E23-4D7F-AB37-7FD27AEC07A4@microsoft.com...
> Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
> which of those "3" should be replaced by 5?
> Thanks a lot.
> Besr
> Darius
>
> "KL" wrote:
>
>> I assumed the number of cells in your range would be a multiple of 3
>> :-( Try
>> this:
>>
>> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
>>
>> KL
>>
>>
>> "Darius" <Darius@discussions.microsoft.com> wrote in message
>> news:DABC80BC-9494-4AC8-B677-A179D3E9DDDC@microsoft.com...
>> > thanks but the second equation (below)which I use for "E3:E94" and
>> > contains
>> > numbers, results in:
>> > #REF
>> > Any suggestion?
>> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
>> >
>> > Best
>> > Darius
>> > "KL" wrote:
>> >
>> >> Hi Darius,
>> >>
>> >> For the first task use the following formula:
>> >>
>> >> =COUNTIF(A1:A10,">0")
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >> "Darius" <Darius@discussions.microsoft.com> wrote in message
>> >> news:A65E1303-66AB-4656-A052-8D8EC585D790@microsoft.com...
>> >> > Hi there;
>> >> > 1-What is the equation to find the number of cells in a range which
>> >> > their
>> >> > values are more than 0.0?
>> >> >
>> >> > 2- How to count the number of cells in range which for every 3
>> >> > consqueative
>> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0,
>> >> > then
>> >> > count
>> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have
>> >> > (even
>> >> > one
>> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
>> >> >
>> >> > Appreciate any help.
>> >> > Best
>> >> > Darius
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

```
 0
9/10/2005 9:32:41 PM
```Thank you so much, works excellent
Darius

"KL" wrote:

> actually, all 4 of them :-)
>
> KL
>
>
> "Darius" <Darius@discussions.microsoft.com> wrote in message
> news:A6067091-0E23-4D7F-AB37-7FD27AEC07A4@microsoft.com...
> > Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
> > which of those "3" should be replaced by 5?
> > Thanks a lot.
> > Besr
> > Darius
> >
> > "KL" wrote:
> >
> >> I assumed the number of cells in your range would be a multiple of 3
> >> :-( Try
> >> this:
> >>
> >> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
> >>
> >> KL
> >>
> >>
> >> "Darius" <Darius@discussions.microsoft.com> wrote in message
> >> news:DABC80BC-9494-4AC8-B677-A179D3E9DDDC@microsoft.com...
> >> > thanks but the second equation (below)which I use for "E3:E94" and
> >> > contains
> >> > numbers, results in:
> >> > #REF
> >> > Any suggestion?
> >> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
> >> >
> >> > Best
> >> > Darius
> >> > "KL" wrote:
> >> >
> >> >> Hi Darius,
> >> >>
> >> >> For the first task use the following formula:
> >> >>
> >> >> =COUNTIF(A1:A10,">0")
> >> >>
> >> >> Regards,
> >> >> KL
> >> >>
> >> >> "Darius" <Darius@discussions.microsoft.com> wrote in message
> >> >> news:A65E1303-66AB-4656-A052-8D8EC585D790@microsoft.com...
> >> >> > Hi there;
> >> >> > 1-What is the equation to find the number of cells in a range which
> >> >> > their
> >> >> > values are more than 0.0?
> >> >> >
> >> >> > 2- How to count the number of cells in range which for every 3
> >> >> > consqueative
> >> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0,
> >> >> > then
> >> >> > count
> >> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have
> >> >> > (even
> >> >> > one
> >> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >> >> >
> >> >> > Appreciate any help.
> >> >> > Best
> >> >> > Darius
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
```
 0
Darius (16)
9/10/2005 10:03:03 PM

Similar Artilces:

how do i delete cells or duplicate cells in a table ?
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 ...

CELL FORMATTING #9
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...

Hyperlinks not moving with cells when data is sorted
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 ...

change date in a sql statement from a cell
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...

Unique Entries/Cell Back Color
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 ...

Return the name of cell to sheet name
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 =...

NUMBER FORMAT #9
CREATE A NEW NUMBER FORMAT SO THAT THE SELECTED DATES WILL APPEAR ONLY AS THE FULL NAME OF THE DAYS OF THE WEEK. ...

No account number shown in printed checks
Using Money 2007 Deluxe. Printed a series of checks from MS Mmoney this morning, but the account numbers didn't print on the checks. Each of the payees has an account number entered in the appropriate place, in the "Go to Payees" detail listing. Can't figure out what's going on here! Thanks for any assistance. Dave On Sat, 17 Mar 2007 08:37:03 -0700, Dave M. <DaveM@discussions.microsoft.com> wrote: >Using Money 2007 Deluxe. > >Printed a series of checks from MS Mmoney this morning, but the account >numbers didn't print on the checks. Each of...

Chart Title or Text Box Linked to cell & sheet
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...

Why are my numbers disappearing in excel yet it totals them?
I have a spreadsheet that I have filled out the individual cells with number. These cells are totaling correctly, however when I open the spreadsheet the individual number I entered are showing blank.... I moved my mouse around in the spreadhsheet and all of a sudden the numbers appeared and then disappeared. Check the font color. The default is black. Also check the cell color. Default is "no fill". If the font color was changed to white, you would only see the content after you select the cell. Remember to Click Yes, if this post helps! "Donna S...

Free Cell card game
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...

Export data from the same cell from multiple spreadsheets into a n
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 ...

sorting cells
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...

Split text cell into seperate colums without splitting up a word
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...

limit number of rows 7 colloms in a worksheet
is there a way to limit or set the number of rows & collums in a worksheet ? thanks david --- Message posted from http://www.ExcelForum.com/ "davidbrowne17" <davidbrowne17.ya1sm@excelforum-nospam.com> wrote in message news:davidbrowne17.ya1sm@excelforum-nospam.com... > is there a way to limit or set the number of rows & collums in a > worksheet ? No. All worksheets have 256 columns by 65536 rows. You can hide unused rows/columns. But why bother? Hi David, Put this in the ThisWorkbook code module. Adjust to suit the area. Private Sub Workbook_Open() Wo...

How to remove dashes and slashes form a sequence of numbers & lett
Hi I have a sequence of numbers in column D and I require to extract just the numbers and letters to column E. D 190/0-01 31-0014 pp7/44-1 uf-744-5 E 190001 310014 pp7441 uf7445 Any pointers would be much appreciated. Kind Regards Celticshadow Put this in E1: =3DSUBSTITUTE(SUBSTITUTE(D1,"/",""),"-","") and copy down as required. Hope this helps. Pete On Oct 14, 11:34=A0am, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > > I have a sequence of numbers in column D and I require to extract just th= e > ...

Hyperlink problem to cell in same workbook
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...

create a flyer with vertical names and numbers at the bottom?
hi check out MS Publisher - it has a couple of templates to do this (under publications for print - flyers in ver 2003) Cheers JulieD "meow" <meow@discussions.microsoft.com> wrote in message news:F4C71CDE-4D91-4A08-B9E4-BBD0D5F48F5D@microsoft.com... > ...

Rename Cell Name
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...

Need a ZERO as the first character in a cell
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...

need some help with: formatting of x2 dates in 1 cell
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...

A2 cell reference increment
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...

finding differing numbers.
How do I in a column of numbers some in duplication, how can i get a list off the entries which reflects these numbers but not in duplication. ie "numbers" 1, 1, 2, 3, 4, 5, 1, 5, 3, 5, 2, 1, 2. "result" 1, 2, 3, 4, 5 Thanks Chris Hi one way: - select your column - choose 'Data - Filter Advanced Filter' - choose a new range and 'unique entries' -- Regards Frank Kabel Frankfurt, Germany curleyc wrote: > How do I in a column of numbers some in duplication, how can i get a > list off the entries which reflects these numbers but not in > duplicat...

cell format update problem #2
say, c1, c2 is formatted as text. and a1,a2,b1, b2 are formatted as general. a1=1,a2=2, b1=1, b2 =2 and I entered c1 =a1+b1, c1 shows =a1+a2 as it is, not 3. I drag the corner of c1 and copy c1 to c2, c2 is a2+b2, So I have to F2 and enter all the cells, c1, c2. Is there a way other than "F2" and "Enter"? Consider formating c1 and c2 as general. Good Luck. "news.microsoft.com" wrote: > say, > c1, c2 is formatted as text. > and a1,a2,b1, b2 are formatted as general. > > a1=1,a2=2, b1=1, b2 =2 > > and I entered c1 =a1+b1, > c1 shows...