1 Cell 2 Values?

Hello All,

Is it possible in excel to have it return two values in one cell separated 
by  a comma and spaces?

For example I want it to count the number of times in a month the value was 
positive and the number of times a value was negative and in the cell I want 
it to show

A1: +,  -

Is this possible?
Thanks to all,
Shhhh 


0
Shhhh
3/1/2006 8:34:58 PM
excel 39879 articles. 2 followers. Follow

6 Replies
1046 Views

Similar Articles

[PageSpeed] 47

Shhhh wrote...
>Is it possible in excel to have it return two values in one cell separated
>by  a comma and spaces?
>
>For example I want it to count the number of times in a month the value was
>positive and the number of times a value was negative and in the cell I want
>it to show
>
>A1: +,  -
>
>Is this possible?

As text, yes.

=COUNTIF(MonthRange,">0")&","&COUNTIF(MonthRange,"<0")

What about times in the month when the value was zero?

0
hrlngrv (1990)
3/1/2006 8:40:34 PM
Harlan,

Thanks for your reply... the days that are 0 I want it to ignore. is that 
going to complicate things?

Thanks again,
Shhhh

"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:1141245634.385226.20130@j33g2000cwa.googlegroups.com...
> Shhhh wrote...
>>Is it possible in excel to have it return two values in one cell separated
>>by  a comma and spaces?
>>
>>For example I want it to count the number of times in a month the value 
>>was
>>positive and the number of times a value was negative and in the cell I 
>>want
>>it to show
>>
>>A1: +,  -
>>
>>Is this possible?
>
> As text, yes.
>
> =COUNTIF(MonthRange,">0")&","&COUNTIF(MonthRange,"<0")
>
> What about times in the month when the value was zero?
> 


0
Shhhh
3/1/2006 8:47:07 PM
HI Shhhh,

=(COUNTIF(A1:A20,"<0"))&"- "&(COUNTIF(A1:A20,">0"))&"+"

This ignores 0's.

HTH
Regards,
Howard

"Shhhh" <123@456.com> wrote in message 
news:SfSdnZEKJv3umJvZRVn-sw@comcast.com...
> Hello All,
>
> Is it possible in excel to have it return two values in one cell separated 
> by  a comma and spaces?
>
> For example I want it to count the number of times in a month the value 
> was positive and the number of times a value was negative and in the cell 
> I want it to show
>
> A1: +,  -
>
> Is this possible?
> Thanks to all,
> Shhhh
> 


0
lhkittle (223)
3/1/2006 9:36:26 PM
Thank you all so very much! This group is amazing!

Shhhh

"Shhhh" <123@456.com> wrote in message 
news:SfSdnZEKJv3umJvZRVn-sw@comcast.com...
> Hello All,
>
> Is it possible in excel to have it return two values in one cell separated 
> by  a comma and spaces?
>
> For example I want it to count the number of times in a month the value 
> was positive and the number of times a value was negative and in the cell 
> I want it to show
>
> A1: +,  -
>
> Is this possible?
> Thanks to all,
> Shhhh
> 


0
Shhhh
3/1/2006 9:57:23 PM
Hello All,

I'm now working on another section of this same sheet, my question is 
related to ths one... is it possible to have one number remain formatted as 
a number while the second number is formatted as currency?

I've tried, haven't been able to come up with anything... any thoughts?

Thank you,
Shhhh

"Shhhh" <123@456.com> wrote in message 
news:SfSdnZEKJv3umJvZRVn-sw@comcast.com...
> Hello All,
>
> Is it possible in excel to have it return two values in one cell separated 
> by  a comma and spaces?
>
> For example I want it to count the number of times in a month the value 
> was positive and the number of times a value was negative and in the cell 
> I want it to show
>
> A1: +,  -
>
> Is this possible?
> Thanks to all,
> Shhhh
> 


0
Shhhh
3/6/2006 5:39:41 PM
Shhhh wrote...
>I'm now working on another section of this same sheet, my question is
>related to ths one... is it possible to have one number remain formatted as
>a number while the second number is formatted as currency?
>
>I've tried, haven't been able to come up with anything... any thoughts?

You could use a formula like =TEXT(x,"General")&",
"&TEXT(y,"$#,##0.00"), but what you seem to want to do leads to more
problems than it solves. Is there any good reason you're not using two
cells with each number in a separate cell?

0
hrlngrv (1990)
3/6/2006 8:43:43 PM
Reply:

Similar Artilces:

Suppressing categories with 0 values
Hi all I'm trying to build a dynamic chart for sales data. The output file contains two columns, A and B. Column A contains categories, i.e. Apples, Oranges, Pears, etc.,. and B contains sales data, i.e. 5, 0, 10, etc.,. respectively. The ouput data comes out monthly. Some months will contain sales from all categories, some ten, some fifty. I want the chart to only graph categories with non-zero values, i.e. if there are 100 categories and only 30 with sales numbers there is no point in showing the other 70 categories with zero values. I can format column B with zero values to show ...

Having 2 workbooks open simultaneously
I have a wide screen laptop that can accommodate 2 Excel workbooks side by side. I used that feature for a while until sometime about two weeks ago something happened (I don't know what) and now I a only able to keep one workbook open at a time. When I try to open a second one, the first one minimizes. Can anyone tell me how to get it back so that I can once again view 2 workbooks side by side? Many thanks for your help -- Royal Did yesterday's response work: "How about just window|Arrange|Tiled (or whatever you want)" Royal wrote: > > I have a wide screen l...

Convert Pivot Table to Values in Macro
The code below converts multiple worksheets to values. Except, as I discovered recently Pivot Tables. Can you help modify the code to also convert Pivot Tables to Values? ============= Sub SetAllSheetsToValues() Dim shtSheet As Worksheet, shtActive As Worksheet Dim rngR As Range, rngCell As Range Application.ScreenUpdating = False Set shtActive = ActiveWorkbook.ActiveSheet For Each shtSheet In ActiveWorkbook.Sheets With shtSheet If .ProtectContents = False Then ' skip protected sheets On Error Resume Next Set rngR = .UsedRange.SpecialCells(xlCellTypeFormulas, _ xlErro...

Update for Microsoft XML core service 6.service pack 2
Updates are ready. Message bubble shows constantly. Updates won't load millypede wrote: > Updates are ready. Message bubble shows constantly. Updates won't > load Operating System? Service Pack level? Architecture? Anything that you have tried? -- Shenan Stanley MS-MVP -- How To Ask Questions The Smart Way http://www.catb.org/~esr/faqs/smart-questions.html ...

Find a value in list 2 that is not in list 1
I have text values in column A and also in column B, most of which match. I would like to take the values in column B that are not in column A and put those values only in column C. Any help would be greatly appreciated. Maxxwell, Put this formula in column C, and copy down. =IF(ISERROR(MATCH(B2,$A$2:$A$20,0)),B2,"") To get them contiguous, use Autofilter, filter on nonblanks in column C, and copy/paste to somewhere. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "maxxwell2" <maxxwell2@discussions.microsof...

How do I stop 1 day tasks splitting over 2 days?
I am using Project 2003 and it has got an annoying habit of splitting most tasks with a duration of 1 day into 7 hours of work on one day and 1 hour of work the following day when you look at them within the Resource Usage view. This is mucking up my Gantt chart as the tasks effectively look as if they are 2 day tasks (although the duration cell definitely says 1 day). Any idea what is causing the problem and how do I fix it? Things to note: - The project calendar and resource calendars are definitely showing 8 hour working days (8-12 then 1-5) so that isn’t causing the probl...

Countif and values greater than zero for a named range
I have a workbook with 30+ worksheets. For each of the sheets, there is a cell (F51) that is part of a named range (PPG). On Sheet 1, cell c9, I want to sum the range (PPG) and then divide by ONLY the number of cells on the other sheets (F51) that are greater than 0. I am able to sum and then divide by count (=sum(PPG)/Count(PPG) but I cannot seem to get it to only count the cells that are greater than zero. When I attempt to use suggestions from other posts: (=sum(PPG)/COUNTIF(PPG)>0) I get error. Any help is appreciated. If you need Excel help on how the COUNT...

Cell comments
I have added cell comments to my worksheet, but they have always bee static, meaning if you put your cursor on that cell it always display the same text. Is there a way to create some sort of comment tha would change depending on the information in the cell? When the curso passes over the cell, the code would go pull in data from a MS Acces table using the value in that cell as criteria -- LA ----------------------------------------------------------------------- LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965 View this thread: http://www.excelforum.co...

Confidentiality Notice #2
How do i configure Exchange 2003 to add a Confidentiality Notice at the bottom of all outgoing emails?? Can this be done without using Visual Basic? Thanks, Greg ...

Creating a chart with varying data value ranges
I have a chart that I want to show home price data with. The chart will have data that has very significantly varying data values. For example, one line graph will be 50-100, one will be 400,000 to 500,000, one will be 90% to 110%. I would like to show these line graphs on one chart. How to I make a chart with differing ranges on the x (vertical) axis? Take a bit of seutp work, but the end result here is impressive: http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html BTW, the vertical axis is the y-axis. -- Best Regards, Luke M "Anthony Blackburn&quo...

How do I modify the value of a checkbox in a word document using C
Hi all, I am a bit of a newbie at C# and am trying to set up a document that I will be modifying based on values from a database. I am using a word document, because it is the "official" reporting form required by my company. I can open the document, and write to bookmarks that I am setting in each table field, but I need to know how to manipulate a checkbox. Can anyone help me? I have edited this post because I have added some other code. The document file I am using is a document that has everything inside a table. I have bookmarked all the text locations, and...

Inserting a value in a table when a connector is added between two shapes
Hi everybody.. I have a Visio drawing containing six different shapes in my stencil. When adding a connector between two shapes, is there a way other than by VBA to make Visio insert the shape IDs of the two connected shapes in a relationship table between the two shapes.. An example.. if I have the two tables Course and Objective, and I want to add an objective to a course, I would create a table between the two tables, say RelationCourseObj where the primary key would be the two primary keys from the two tables... Now is there a way with the database wizard to make Visio automatically...

eliminating blank cells
I have two sheets in a workbook. Say sheet January and Summary. Sheet January looks like this DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 104 19.01.2007 11/A2 329 839 292 88 Now I want to gather these information in sheet "Summary" eliminating the blank rows, so Summary looks like this: DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 10...

How do you change data values by dragging graph lines in excel 07?
Firstly, please ask your question in the body of the message, not the subject line. Secondly, you don't in Excel 2007; that was one of the "improvements" they put in. Most experts prefer the charting facilities in Excel 2003 over those in Excel 2007. -- David Biddulph "Mannymeister" <Mannymeister@discussions.microsoft.com> wrote in message news:3616E56A-A8BF-44D4-9E38-8746B39DCD7E@microsoft.com... > ...

Removing the (1) figure before phone numbers
How can I delete the (1) figure in front of some of my phone numbers? -- phildee Delete it. Was there more to your question that you did not post? -- Russ Valentine "Phil Dee" <PhilDee@discussions.microsoft.com> wrote in message news:299FB137-E06E-43A8-825F-498EF923BB30@microsoft.com... > How can I delete the (1) figure in front of some of my phone numbers? > -- > phildee ...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

Copying text value based on long (comlex) calculation
I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past... Here is my situation: - I have a workbook that contains multiple sheets. - Each sheet contains auto parts from different suppliers, sorted by part number. - Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change. - I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price ac...

Outlook Express #2
I have Outlook Express installed on two computers at two separate residences. Both are set to check the same comcast email address. One works fine but the other I keep getting errors when I try to send out emails etc. Should I be able to use Outlook express at two separate locations to check the same email at different times without a problem. Sounds a bit like the problem I am having (see my post from earlier today). Both my PCs are configured the same: one keeps prompting me for the POP3 password for each e-mail account, the other I have been able to successfully save the pa...

2 Axes, same scale wanted
Hi Is it possible to set my second y axis so that the scale is identical to the first y axis. (My chart needs to have 2 axes as one plots daily values and one plots annual) but I find that excel automatically assigns diferent scales to each? This same reference should apply to dual axis charts. You can apply scales to both primary and secondary axes using worksheet cells via Jon's explanation below. http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html -- John Mansfield http://cellmatrix.net "jonny" wrote: > Hi > > Is it possible to set my sec...

Data validation for 2 valid values
I want a cell to allow only 2 valid values but not have a drop-down list appear. How do I do that with Data->Validation? Data>validation>allow>list, put the 2 values separated by a comma in the source box, uncheck in cell dropdown -- Regards, Peo Sjoblom "Hall" <hall@garp.org> wrote in message news:ujuB%23w52DHA.2000@TK2MSFTNGP11.phx.gbl... > I want a cell to allow only 2 valid values but not have a drop-down list > appear. > > How do I do that with Data->Validation? > > Using a list, as Peo suggested, will force users to match the ...

CRM 3.0 Installation Error #2
Receiving the following error during "System Requirements" step of the server installation: "One or more Microsoft CRM groups are not valid. The group scope must be Domain Local or Global. The group type must be Security." Environment: SQL Server 2000 CRM Server: Server 2003 SP2 Details: I am trying to reinstall CRM on one of our redundant app servers. This server was already running CRM but was uninstalled in attempt to fix an issue. CRM is still running on the primary app server. I am therefore connecting to an existing DB. The AD groups exist, have the correct ...

connecting cells with connector lines
Hello! Is it possible in Excel (2007) to draw a connector line (with or without an arrow ...) between two cells in a worksheet, so that the line-tips will follow their cells even when the latter are moved ? Thanks Michael On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote: > Hello! > > Is it possible in Excel (2007) to draw a connector line (with or > without an arrow ...) between two cells in a worksheet, so that the > line-tips will follow their cells even when the latter are moved ? > > Thanks > > Michael The line will automatically a...

NDR in 2 Exchange Servers 2003 on 2 domains
Hi, 2 Exchange servers 2003. 2 domains. a.fr and b.fr. The Active Directory domain is xxx.local. Each domain is hosted on a distinct server, on the same Exchange organization. When an email arrives from outside the non delivery receipt is sent from postmaster@xxx.local. As almost every MTA now is checking if the 'from' belongs to a valid domain, this does not work. It is possible to change the NDR originator to postmaster@a.fr or postmaster@b.fr. But this is valid for the whole organisation. And I need the a.fr server to answer with postmaster@a.fr and the b.fr server answer from po...

2 different fonts in the same cell?
Hello! If I want (Times New Roman) "This is a red Wingdings 3 triangle ([change font] Wingdings 3: 123 [back to Times New Roman]").", I can do that in a cell. But if I refer to that cell (=A1) in B1, the Wingdings font doesn't come over and it winds up all TImes New Roman and looks like "This a red Wingdings 3 triangle ({)." How can I "=A1" for a cell that has two fonts in it and have the two fonts show up?? Thanks! VR/ Lost You can't have that in a FORMULA. You would have to change it to text first. -- Don Guillett Microsoft MVP Excel Sales...

how do i delete everything after a particular character, after each such character? #2
thank you, everyone, for your great suggestions. i tried paul corrado's very simple solution below: Find What: enter " - *" (without the ""s) Replace With: enter " -" (if you still want the dash otherwise leav that field blank) that worked great except that many of the lines that continued onto second line were unaffected and need to be deleted. fortunately tha data that needs to be kept has an asterisk and some spaces in front o it (i.e., "* ") so perhaps there's some way to have excel chec each cell for the existence of this text and,...