how to count cells with equal sign

Hi guys please help me to write this formula:
if the integers of the A1 and A2 are of the same sign(+ or -) =1
if not =0

I need to count cells with equal sign and i don't know how to expres
in formula "equal sign"
Appreciate your help

Message posted from

7/21/2004 11:54:35 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies

Similar Articles

[PageSpeed] 35


Perhaps use the fact that, if you multiply together two numbers wit
the same sign the answer will be positive. And if you multiply two wit
opposite signs, the answer will be negative.

So: =if(A1*A2>0,1,0).

This doesn't cover the cases of A1 and/or A2 being zero. Does tha
matter? If it does, what "answer" would you want to display?


Message posted from

7/22/2004 12:11:08 AM
I'm not clear about the cells with an "=" sign.  Usually
when you enter "=" as the first character, Excel insists
you are writing a formula, then trys to evaluate it.  The
other case is that you are storing strings of text data
some of which begin with an "=" sign.   If the latter is
the case, the use the CountIf function.  
Use Left(celladdr,1) to isolate the "=".  Sorry I can't be
more detailed, but I can't remember the syntax for
complicated CountIf's.
>-----Original Message-----
>Hi guys please help me to write this formula:
>if the integers of the A1 and A2 are of the same sign(+ or
-) =1
>if not =0
>I need to count cells with equal sign and i don't know how
to express
>in formula "equal sign"
>Appreciate your help.
>Message posted from
anonymous (74722)
7/22/2004 3:38:19 AM
You can use the SIGN function to compare the integers, e.g.:


Then SUM the column.

There's information on the SIGN function in Excel's help.

Val29 < wrote:
> Hi guys please help me to write this formula:
> if the integers of the A1 and A2 are of the same sign(+ or -) =1
> if not =0
> I need to count cells with equal sign and i don't know how to express
> in formula "equal sign"
> Appreciate your help.
> ---
> Message posted from

Debra Dalgleish
Excel FAQ, Tips & Book List

dsd1 (5911)
7/22/2004 10:13:46 PM

Similar Artilces:

formatting numbers in cells
Hi. I tried to format a group of horizontal cells by going to format celss, then entering the following: number, 2 decimal places, $ sign and then (2,184.10). I clicked on okay. What I ended up with was the correct formatting in the last cell, and the rest of them have #### in the cells. Can someone tell me why this happened and how to correct it? Thanks! one reason for #### is that the cell isn't wide enough for the format specified. Try widening the columns. In article <2ee201c40087$e561bfb0$a601280a@phx.gbl>, "Mary Jo" <

Filling cells in between pairs of numbers
Hello all, I am trying to find an easy way to fill in cells in between pairs of numbers each of which is exactly the average of the pair's difference averaged by the number of these cells greater than the previous. So I have 1 1 2 3 4 8 5 6 7 8 20 9 10 12 13 14 15 45 I want: 1 1 2 3.333333333 3 5.666666667 4 8 5 11 6 14 7 17 8 20 9 23.57142857 10 27.14285714 11 30.71428571 12 34.28571429 13 37.85714286 14 41.42857143 15 45 I know I can do a Fill, Series, but then even with a macro that does a "control shift up fill series enter",...

Merge Cells not available
I am attempting to merge cells in a spreadsheet, but the Option to merge both on the toolbar and in 'Format'-'Cell' are greyed out. My worksheet is not protected. Every Worksheet in the document acts the same. If the workbook is shared (Tools, Share Workbook) merging cells is not allowed. -- Jim Rech Excel MVP "Flash24" <> wrote in message |I am attempting to merge cells in a spreadsheet, but the Option to merge both | on the toolbar and in 'Format'-'...

lock first row and first column WITHOUT selecting Cell "B2" first?
Hi, I'm new to this group. From a VB6-Program I create a new workbook with 1 worksheet. After writing the title row I want to set the first column and the first row non-scrollable. This is my actual code in the VB6 program: ws.Range("B2").Select ActiveWindow.FreezePanes = True I tested it, it works. My Question: is there another way to do this WITHOUT selecting the cell first? Doing it with an non-active window? I've searched to no avail, but probably haven't asked the right questions or used the correct terms. (English isn't my first lang...

I need to split a cell into 4 cells and keep in the same column
Data | Text to Column ? -- Bernard V Liengme remove caps from email "dc" <> wrote in message > With the amount of detail provided............... How about a guess? You have 4 words in A1 and want them placed in A:A4 Select A1:A4 and Edit>Fill>Justify Gord Dibben MS Excel MVP On Tue, 19 Sep 2006 13:02:02 -0700, dc <> wrote: Not much to go on. You say "split" so I presume that you want only a p...

Sort Treats Empty Cells As Largest Value??
I have several projects that sort data from greatest to least (descending). That is, nothing is considered greater than a million. Of about 4000 rows between a third and a half are blank (no count). I need the maximum values/count at the top of the list, but Excel puts these empty cells at the top of the list. As a result I have to do a lot of moving of large groups of data around to put the empty/blank cells at the bottom of these lists -- a real time consuming pain. Is there a way to have Excel treat empty/blank cells as having lower values than cells with values? I assume I could f...

Count if cell contains a comma
I want to count the cells which contain a comma. Is it possible to do something like: =COUNTIF(B2:B53,FIND(",",B2:B53)>0) -- Thanks, Fred Please reply to newsgroup, not e-mail =COUNTIF(B2:B300,",") Try this out ------------------------------------------------ ~~ Message posted from ~~View and post usenet messages directly from ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements On Sun, 7 Dec 2003 20:02:29 -0600, "Fred Smith" <>...

Problem with Active Accessibility and OLE lock count
Hello! I've noticed an irritating behaviour of the Windows Magnifier and a standard single document MFC application generated by the new project wizard of VC++ 8.0 with enabled support for Active Accessibility. As soon as the Windows Magnifier is running, an accessible proxy is created through CWnd::CreateAccessibleProxy. The creation of a CMFCComObject increases the OLE lock count by calling AfxOleLockApp. However, this lock count is not decreased any more. As a consequence, the call to AfxOleCanExitApp in CFrameWnd::OnClose returns false and prevents the application from closing...

HIdden Cells?
I would like to be able to hide columns automatically in a spreadsheet if no data was entered in the cells for that column without having to highlight the column and choosing hide. Eg: The spreadsheet has column headings, but for printing purposes I would like to hide that column if no data has been entered under any number of headings. Is there anyway this can be done automatically? Thanks for your help I wouldn't do it automatically when printing, but I would use a macro that I could run on demand: Option Explicit Sub hideCols() Dim iCol As Long With ActiveSheet ...

Exact number of digits in a cell
Sorry to post simple question in this forum but I need to use a function key to check if the correct number of digits are in a cell. I presume it would work such as 6 digits entered True, 4 or 7 False. I have tried a number of books but am struggling a little. Help very much appreciated. Roger You can use the len function to return the # of characters in a cell. i.e. =if(len(a1)<>6,"You entered the wrong # of characters","You entered the right # of characters") ----does the following if a1 does not equal 6, says you entered the wrong # otherwise says you en...

excel locks up after selecting a cell
I have a the problem is not a scrolling mouse wheel.....when I select a cell in the workbook it will not let it I move the mouse around it keeps selecting all the sells as if i have the mouse held not appear to have a key stuck either......however...when the cell is selected...(before i click the cursor in a cell) i can move around to different cells with the arrow keys and it only selects one cell at a it is suppose soon as I click with the mouse the whole spreadsheet continues to be selected wherever i move the mouse...even if i mov...

Continuall Add ing values in a cell
Hi Everyone- I suspect that this question has been asked before but it is difficult to know how to find the correct thread. That being said, here it goes. I would like to make a cell continually add values entered into them. For example, I have a cell that has a value of $12.37 and I have to add to it a new value as I encounter it, lets say $8.73. As it stands know, I pull out my trusty calculator andsum up the two values and key in the sum into the field. I have a pile of recites that I need to add as they come up. Any help is greatly appreciated. Manuel A. Ayala CAD Concepts...

Save as CSV file leaves CELLS content between quotation marks
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Oke I have something really weird, <br><br>Normally when you &quot;save as&quot; and choose CSV or Txt, each cell is divided by a comma (period) but the contents of a Cell is not between quotation marks. (windows) <br><br>Now on mac: When I choose UFT-16, TXT windows format, CSV windows format or Mac format. All these options leave a file where my cell contents are between quotation marks. I do not want that!!! How can I get rid of them....? <br><br>gr <br><br...

Count down to Words / Text
Hello, Is there a way to have a cell that is counting down from "10" and when it hits "0" spell the word FULL and in Red? example: cell BB10 has a formula in it: =COUNTA(H10:AV10) cell BB8 has a formula in it: =SUM(10-BB10) NOTE: there are only 10 entities max in cells H10 through AV10 and that is why I subtract BB10 from 10. I want cell BB8 to reflect the word ALL when there is 10 and FULL when the countdown hits "0" Can this be done? Thanks, Champ Try this....... =IF(SUM(10-BB10)=10,"ALL",IF(SUM(10-BB10)=0,&qu...

Counting consecutive numbers in a list
I'm looking to take a list of twenty numbers and count the number of times in that list two or three consecutive numbers appear in it. Any ideas? Can you post some sample data and let us know what result you expect? -- Biff Microsoft Excel MVP "Wiseguy91" <> wrote in message > I'm looking to take a list of twenty numbers and count the number of times > in > that list two or three consecutive numbers appear in it. Any ideas? ...

How can I count the number of months between 2 dates?
For instance, I am trying to compute how many months are left in leased equipment contracts. I have a spreadsheet with the ending dates for all the individual contracts. What I would like to be able to do is to calculate the number of months between now and the end of the contract. I appreciate any help. Thanks Tom One way: =DATEDIF(TODAY(), A1, "m") In article <>, Tom Bradstreet <> wrote: > For instance, I am trying to compute how many months are left in leased &...

count memo characters
Hi, how can count charactrs in memo filed? thanks In what context? In a query, you could type something like this into the field row in query design: Len([MyMemo]) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - Reply to group, rather than allenbrowne at mvps dot org. "--- MSh ---" <> wrote in message news:u7kJor29HHA.5464@TK2MSFTNGP02.phx.gbl... > Hi, > how can count charactrs in memo filed? > thanks --- MSh --- wrote: > If Len(Me.CC) = Null Then > is it tr...

using named cells across worksheets
I've named various cells after producing a multi-worksheet model. The named cells can be applied in the worksheet they are in but I can't see how to apply them across other worksheets. At the moment, the only option I can think of is manually updating every formula in all the connected worksheets! Any ideas. Nick You defined the names so that the names included the worksheet name, too? Sheet1!test sheet2!test .... Sheetx!test Then you can just use them by making sure you include the complete name: =sum(sheet1!test,sheet2!test,sheetn!test) Is that what you meant??? Nick Malon...

Calculating from last inputted cell
I am trying to write a formula to input the data from the last inputted cell in a range to another cell. Reading through some earlier threads I found a formula that works in 2 stages, the first finds the last inputted cell to return the cell reference, and the 2nd stage reuses the result, the formulas were =MAX(ROW($A$1:$A$200)*($A$1:$A$200<>"")) If the above formula was entered into cell B1 the 2nd formula would be entered in the cell where I wanted the data displayed and would be =INDEX($A:$A,B1) The 2 formulas work providing the entered data starts at row 1, my p...

How to auto-enter date when cell is clicked?
My spreadsheet is on a shared server, and has several columns of data entered by various people. These are columns D through L. In the far right column, I want them to just be able to click on the cell and that day's date appears. It needs to be static - not auto-update every time the file is opened. It's in Column L. The top cell is in Row 4. When they enter or change data on a row, the date they did it needs to appear in column L in that row. The point is to keep their data entry time to an ABSOLUTE minimum. I know they can select the cell and type control-;, but I'd like to e...

count cells in autofilter
while working in an excel spreadsheet, i use the autofilter tool to sort data. on some spreasheets it will list the total number of items found from the autofilter (ie., 10 of 15 records found. How do i get this to display automatically each time? If your list has many formulas, the Status Bar might show "Filter Mode" instead of a count of the visible records. You can use the Subtotal function to count the visible rows. The Status Bar may also show 'Filter Mode' if a cell in the list is changed, after a filter has been applied. The following MSKB article has inform...

cell value plus text
I would like to know how to get the value from one cell (C81) entered into another cell (N81) and then have text after that value. For example, if C81=2, I need N81 to have 2 plus text (2 oranges). If this is not possible, I do have the text in the first row and could reference that cell, but then would need to know how to string the two cells of text together (i.e. C81=2 and C1=oranges so N81=2 oranges). Try one of these: =C81&" oranges" Or, using a cell to hold oranges: C1 = oranges =C81&" "&C1 -- Biff Microsoft Excel MVP "Deanna" <...

Color of Comment Flags for Individual Cells
Is it possible to change the color of the little triangles at the top right corner of individual cells that indicate someone has placed a comment on the cell? They're currently red, but that becomes a problem when the cell has to be colored red (and, no, it's unfortunately not possible to use a different color for the cell) because you can't see the flag. Any help would be intensely appreciated. >>no, it's unfortunately not possible to use a different color for the cell No, it's unfortunately not possible to use a different color for a cell comment. -- Jim...

counting nonblank rows
I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. Try this: =Counta(C6:Z29) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "stumped" <> wrote in m...

Making a cell mandatory if another cell has a particular value
Hi, any help would be appreciated! I am trying to create a template where people fill out information about a user. I want cell C6 to be filled out (NOT empty) if cell C4 = "XX" or C4 = "YY". Then, I want to be able to save the template where C6 is blank so that people can fill it out later; Thank you! Lisa I would use an adjacent cell and a formula: =if(and(or(c4="xx",c4="yy"),c6=""),"Please answer C6","") And format this cell in big, bold, red, letters. If you have lots of values to check in C4...