Negative percentage in data verification dropdown list?

 Is it possible to have a negative percentage a in data verification list as 
one of the choices in the dropdown list ? Negative percentages display 
incorrectly in a percentage-formatted cell, and don't calculate correctly in 
formulas. Positive .01 displays as 1% and correctly calculates, but negative 
-.01 dislpays as -.01 and creates an entirely incorrect value when run 
through my formulas. I've also tried .-01. Any secrets out there?
0
Utf
12/10/2009 6:32:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1005 Views

Similar Articles

[PageSpeed] 34

If you have formatted the dropdown cell to percentage format.. that would 
display all percentages in % format

-- 
Jacob


"Gary" wrote:

>  Is it possible to have a negative percentage a in data verification list as 
> one of the choices in the dropdown list ? Negative percentages display 
> incorrectly in a percentage-formatted cell, and don't calculate correctly in 
> formulas. Positive .01 displays as 1% and correctly calculates, but negative 
> -.01 dislpays as -.01 and creates an entirely incorrect value when run 
> through my formulas. I've also tried .-01. Any secrets out there?
0
Utf
12/10/2009 7:15:01 AM
It sounds as if the "number" you put in the cell wasn't -.01
You've probably got some text string.  Check for spaces or non-breaking 
spaces or other non-numerical characters.  Also check that you've got -.01, 
not -.O1
You can check whether it is number or text by using =ISNUMBER(cellref) and 
=ISTEXT(cellref).
--
David Biddulph

"Gary" <Gary@discussions.microsoft.com> wrote in message 
news:89AD3B1B-9B97-4DA3-8C2A-5024770CEBB1@microsoft.com...
> Is it possible to have a negative percentage a in data verification list 
> as
> one of the choices in the dropdown list ? Negative percentages display
> incorrectly in a percentage-formatted cell, and don't calculate correctly 
> in
> formulas. Positive .01 displays as 1% and correctly calculates, but 
> negative
> -.01 dislpays as -.01 and creates an entirely incorrect value when run
> through my formulas. I've also tried .-01. Any secrets out there? 


0
David
12/10/2009 8:28:36 AM
Thank you, both of you. It does work. Not sure what I was doing before.

"David Biddulph" wrote:

> It sounds as if the "number" you put in the cell wasn't -.01
> You've probably got some text string.  Check for spaces or non-breaking 
> spaces or other non-numerical characters.  Also check that you've got -.01, 
> not -.O1
> You can check whether it is number or text by using =ISNUMBER(cellref) and 
> =ISTEXT(cellref).
> --
> David Biddulph
> 
> "Gary" <Gary@discussions.microsoft.com> wrote in message 
> news:89AD3B1B-9B97-4DA3-8C2A-5024770CEBB1@microsoft.com...
> > Is it possible to have a negative percentage a in data verification list 
> > as
> > one of the choices in the dropdown list ? Negative percentages display
> > incorrectly in a percentage-formatted cell, and don't calculate correctly 
> > in
> > formulas. Positive .01 displays as 1% and correctly calculates, but 
> > negative
> > -.01 dislpays as -.01 and creates an entirely incorrect value when run
> > through my formulas. I've also tried .-01. Any secrets out there? 
> 
> 
> .
> 
0
Utf
12/13/2009 4:22:01 PM
Reply:

Similar Artilces:

Want use results of Drop down list to look up a value
I am using Excel 2002 and I have a multisheet workbook and on the first sheet I have created a series of 6 drop down lists pulling from predefined named ranges. I also have limited subsequent choices based on what is selected in the other lists. The end result is the user will be selecting parameters that identify a product and now I am stuck with an easy way to look up the associated part number based on the parameters they selected. Essentially, once they have chosen the parameters in the list, I would like to create a macro that uses those values to locate the associated part...

two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I download transactions into Microsoft Money for either account, all the transactions go to only one of the accounts set up in Money. I was prompted for the first download to our "main" account. Then when I went to download for the other account, I wasn't prompted where to download to... and all the transactions went into the first account. Did that make sense? My bank required we switch to Money from Quicken, so I am new to this. Tried to search in users guide and online for help. Help would be much appreciated - ...

summarizing worksheet data
Hi, I want to create what should be a relatively simple spreadsheet with, say, fifty worksheets in the workbook. Each worksheet would be identical in terms of number of rows & columns, as well as the type of data those columns contain. On the first worksheet, however, I'd like to be able to summarize the cumulative totals of all the other sheets. So, for instance, if cell a20 on worksheets 2 through 50 contained a formula that calculated a sum of the figures in a1:a19, I'd like to put a formula in a cell in the first worksheet that will total the cell a20 in all the other w...

Gathering and adding data from different rows
Hi all, I run a report at work every day that tells me how many shares of a company have been sold and from what year these shares were granted to the participant. The year and the amount of shares are on different lines. For instance, the year would be on D5 and the share amount would be on D10. This is a daily function and the data changes every day. I am required to manually scan these reports and add up the shares for each year separately. I have to separate the amounts for 2003, 1997 and 1996. I want to create a spreadsheeet that I can dump this data into and have the spreadsheet find...

Adding members to Marketing Lists whilst offline
Hi, When in Offline mode of CRM 3.0, the 'Manage Members' button in the Marketing List Members section of Marketing Lists screen is not available. Does anybody know if it is possible to change the configuration to display this button, and allow users to add members to marketing lists whilst working offline? Thanks Hi Steve, Sorry, but CRM 3.0 does not allow the managing of List members while in Offline mode. You must be online with your client or using the web client in order to manage the list members. This posting is provided "AS IS" with no warranties, and...

Access unwanted automatic data entry
when entering data in my Access table, if i use the tab ket to advance to the next field, it often enters a number in that field. Why does it do that nad how can I make it stop! Are entering data direcctly into Access table? If so, stop doing that, unless you only do it occasionally to repair data in database and you know database design and manage it. "Pat the biologist" <Pat the biologist@discussions.microsoft.com> wrote in message news:6B724974-C937-4C5C-BDBA-1A086C0712A1@microsoft.com... > when entering data in my Access table, if i use the tab ket to advance to...

Macro to pull every Nth row of data
I have a spreadsheet of data and I need to pull every 60th row out onto another sheet. Any simple macros? Thanks. Amy How about Sub Test() Source_Sheet = "Sheet1" Target_Sheet = "Sheet2" n = 5000 ' your last line of data on Sheet1 Target_Row = 1 Sheets(Target_Sheet).Select For nCount = 1 To n Step 60 Worksheets(Source_Sheet).Cells(nCount, 1).EntireRow.Copy Worksheets(Target_Sheet).Cells(Target_Row, 1).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ F...

Example using MFC WinInet classes with POST of FILE and other INPUT data
I am looking for a good example for POSTing both FILE and other INPUT data to a URL. I understand that the FILE must use a multi-part encoding but I do not understand how the atcual file contents get POSTed or how the INPUT data gets POSTed also. I am guessing the INPUT data can be added as part of the query parameters to the URL. ...

Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front. After every week, when new data is entered, I want the formula at the front to use the cell with the new data instead of me having to change the existing formula. For example, my data looks like this: A B C 276 300 421 175 0 0 0 I need a formula that will automatically detect the last number >0 in column C. Thank you for your assistance! =Countif(C2:C1000,">0") so your main page formula might be something like: =...

Data Validation in XL2007 suddenly stops functioning
I created a workbook in XL2003 that included some named ranges on Sheet2 that fed some data validation lists on Sheet1. Everything worked as expected. My colleague opened up the workbook in XL2007 and used it several times, and everything worked as expected. Then she called me, saying that it was broken. Of the 6 named ranges and related data validation columns on the other sheet, none of them were working anymore. I went over to her PC, and checked everything I could think of- I could set up working data validation test cells on Sheet2, but could not get the ones on Sheet1 to...

dropdown list problem for newbie
i have a column(A) where each cell is a dropdownlist(same list). if person chooses an item from that list...can it return a correspondin value? example: my drop down list corresponding value animal 005 building 1174 vehicle 3345 so for a specific cell...lets say A1, i choose building from th dropdown. i want it to return a value of 1174 in cell A1. is this possible? if so please help? thx eri -- enag ----------------------------------------------------------------------- enags'...

Positive and negative graph
Hi, How can I do so that my columns above zero be green and the ones below zero be red. Thanks Hi, In versions prior to 2007 you are use invert if negative. http://www.andypope.info/charts/Invertneg.htm http://peltiertech.com/Excel/ChartsHowTo/InvertIfNegative.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Construk" <Construk@discussions.microsoft.com> wrote in message news:6F662E82-C06C-45FB-B29E-46A6181DA829@microsoft.com... > Hi, > > How can I do so that my columns above zero be green and the ones below > zero > be...

Appending data in cells that utilize a Dropdown list.
Hello all, I would like to append entries in cells that utilize a dropdown list. Currently, when I select (in this case names) from my dropdown list I cannot append them if I want to add more than one to a cell. If I select another name from the dropdown, it erases the first entry. Is there some way to append entries within cells that use this form of validation so that I can enter more than one in a cell from the dd list? -- Thanks, Geek using Office XP Professional Hi Geek Debra Dalgleish has an example workbook showing how to do this http://www.contextures.com/excelfiles.html...

Importing vcalendar data to multiple users
I'm running Exchange 5.5. Several times a year I have to create a large group of users. I'd like to be able to pre-populate each user's calendar with pertinent calendar events. Is there a way to import this type of data in batch mode? See if http://www.slipstick.com/calendar/holidays.htm#company helps. ME wrote: > I'm running Exchange 5.5. Several times a year I have to create a > large group of users. I'd like to be able to pre-populate each user's > calendar with pertinent calendar events. > > Is there a way to import this type of data in batch...

Pivot Table: showing data as % of subtotal
Hi, I have pivot table data in this format: Name Type Amount ABC 1 $100 2 $200 3 $300 4 $400 ABC Total $1,000 DEF 1 $150 2 $260 3 $310 4 $420 DEF Total $1,140 and so on .... .... .... I want to see the data as follows: Name Type Amount % ABC...

Exchange 2003 All Address Lists
In Exchange 2003, under System Manager | Recipients | All Address Lists, I do not want users in Outlook to able to see the default address lists: All Contacts, All Groups, All Users, Public Folders. I would rather create my own Address Lists which contain specific grouping of users. What is the best way to go about this? For example, should I delete the default address lists, and create my own? Yes you can do that. When you say "Groups", let's clarify something. You cannot filter them based on AD domain group membership. You must use an LDAP filter to identify groups...

Lost "to" email dropdown?
Hello! I'm trying to troubleshoot a weird problem with Outlook 2003 for someone in my company. This morning, seemingly out of the blue (nothing was intentionally changed on her machine), the dropdown with recently-used email addresses that appears when you start typing in the "to" box on a new message has disappeared. Anyone ever seen this before, and/or any ideas how to fix it? Thanks! Eric Law that's the autocomplete cache. It's probably corrupt and was replaced with a new cache file. You can't recover the old one but as she sends messages it wil...

Auto insert data to seperate sheets?
Hi, I have a Excel (2003) workbook consisting on 4 sheets, sheet 1 has 9 columns, sheet 2 has 4 columns, sheet 3 has 2 columns. Sheet 4 is used for new data which will usually stay on this sheet for a few weeks before I manually copy it over to the relevant sheet then manually arrange it. The whole workbook is arranged alphabetically. I would like to be able to insert new data in sheet 4 but at the same time the entered data inserting into another sheet of my choice and in its relative alphabetical row, Is this possible? any help much appreciated many thnx It is possible, but can be rath...

dist list
I have a list of people distributed on week days, such as: Monday: alex, rose, lisa Tuesday: george, gary, mitch wed: ...... Th:..... Fr:.... I would like to create a dist group say named "meeting" and when i send email to "meeting" address on monday, alex, rose, lisa will receive the mail only and if i send on tuesday george, gary, mitch will receive the mail only..... Any suggestions are highly appreciated... Thanks... -- renomero "renomero" <renomero.59201dc@outlookbanter.com> wrote in message news:renomero.59201dc@outlookbanter.com... >...

Financial institution list incomplete
One of the institutions listed in Money 2004 financial institutions list is no longer being listed. The institution is listed as an institution that Money supports. I've tried updating the list using the "Get the latest Money updates" to no avail. Does anyone know how to force an update of the fipartnr.ini file or know where I can get an updated version? I've tried the suggestions in Microsoft Knowledge Base Article - 247570 and they didn't work. In microsoft.public.money, Chris wrote: >One of the institutions listed in Money 2004 financial >institutions ...

How do I print a font list using font name in the fonts?
I want to print a list of all of the fonts that I have using that particular font. See http://word.mvps.org/FAQs/Formatting/FontSampleGenerator.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Rich641" <Rich641@discussions.microsoft.com> wrote in message news:AB860C6E-32E3-4C5F-B695-0B479B9BE4CF@microsoft.com... >I want to print a list of all of the fonts that I have using that >particular > font. > ...

data validation
my excel skills is no more than having a certain cell multiplyin another cell. scenerio: i have 2 cells, with drop down box. box2 is dependant to selections o box1. after i chose the item in box2, i would like, let say, cell A3, A4, A to display certain data about the item i selected in box2. lets say, i selected "Peter" on box2, i would like A3 to display hi "sex", A4 being "age", A5 is "height". thank -- Message posted from http://www.ExcelForum.com Hi have a look at http://www.contextures.com/xlDataVal02.html for dependent data validation lis...

Sample Data Question
Does the sample data installed using the Microsoft.Crm.Tools.SampleDataWizard.exe inlude sample accounts, contacts, leads, opportunities... I need this data for testing and an none of these was installed, knowing that I installed Microsoft CRM Server 3.0 and the sample data successfully. Hi , Yes the sample data includes all you mentioned. Mike "Mr. Bean" <mkhiami@gmail.com> wrote in message news:1141117198.422438.167040@i39g2000cwa.googlegroups.com... > Does the sample data installed using the > Microsoft.Crm.Tools.SampleDataWizard.exe inlude sample accounts, > ...

publishing lists in excel
I'm trying to publish a list from excel to sharepoint window services. I get an error: cannot connect to the server at this time. Your list cannot be published. I've checked off all the troubleshooting alerts except one (You cannot create a list in a shared workbook). Since I'm on sharepoint windows, I don't have the option of sharing or not sharing my workbook (its greyed out) and I CAN create a list in Excel. I just can't published it to my sharepoint site. I want to set-up a synchronized list with my excel worksheet and sharepoint site. The TEST data is only 1 l...

How can I fill 2nd worksheet (in different order) with data from 1st worksheet? #2
Please help. I have a large amount of data where the 1st worksheet serves as the basis for the second. I want to have the second sheet filled automatically, based on information in the first. This is my set up. This Sheet is Called Payroll Data A B C D ............ H I Name Job Description Status Reg RegFB OT-FB 5 Adams, John Butcher FT $ 36.23 0.4321 0.0974 6 Mason, George Baker PT $ 18.56 0.4367 0.0974 7 Richter, Thomas CndlestkMkr T $ 22.11 0.4021 0.0732 8 Wilson, James President FT $ 66.99 0.5355 0.0974 This sheet is called FAL - Analysis A B C D ............. A...