Percentage Problem E.g 1.2 Showing As 120.0%

Feb 1, 2007

I know there is a simple to solution to this, i helped a colleague with it years ago but i can't figure out what i did to change it!

About a week ago my work computer was upgraded from Office 97 to Office 2003. My Excel now does the following:

When i type 1.2 in to a cell them change the format to percentage, i expect to see it as 1.2% BUT what happens is, Excel changes it to 120.0%

One possible solution is to type .12 - Excel changes it to 1.2% but this is very difficult to get used to and impossible when trying to calulate formula based %s

View 9 Replies


ADVERTISEMENT

Showing Correct Percentage Based On Monthly Figures

Mar 21, 2013

I have the following three columns of Data and I'm using this formula to calculate the %age difference cells:

=IF(ISERROR((F14-F13)/F13), " ", IF(ISBLANK(F14)," ",IF(F14=0,"-100%",(F14-F13)/F13)))

A - No. of Hits B - %age difference C - Weekly Average

25
50 100%
25 -50%
0 -100%
20
0 -100%
0
50

I have left blank the "cells" which are giving me trouble, and my troubles are as follow:

-Where the value of the previous month was 0 and the value of the current month is higher my output is Blank, ideally I would like this to show as 100%
-Where the value of two months in a row are 0 I would like the value to show as 0% for the second month

I did add extra into the formula so it would show the 100%, unfortunately it did this for any consecutive months at 0 also which was incorrect.

View 3 Replies View Related

Pivot Showing Percentage In Grand Total Grouped By First Column?

May 16, 2014

i'm trying to crack what should surely be a simple problem. Attached is my spreadsheet.

As you can see I have 3 columns of detail, with totals spread over a number of months.

I want to be able to display the percentage of the Resource total for each other grouping.

For example, in my spreadsheet you can see resource Mick Arber at the top. I want to be able to see each of his rows as a percentage of HIS total.

View 1 Replies View Related

Conditional Formatting Based On Percentage And Insert Cell Based On Percentage

Apr 3, 2013

I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.

For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.

I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.

View 2 Replies View Related

Calculate Percentage Of A Percentage?

Apr 26, 2013

calculating the percentage of a percentage and writing the formula for excel.

There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.

35 is what percent of 25%
32 is what percent of 25%
10 is what percent of 25%

View 3 Replies View Related

Showing Any Changes On Tab

Mar 10, 2009

I have a tab within a workbook where i want the user to copy a different spreadsheet into and then press a macro button to run some code, however after this button has been pressed i want this one tab to show any changes that have been made to it, i.e make the cell red or something just so i can keep track of manual adjustemnts.

View 9 Replies View Related

UDF Showing #Value

Dec 5, 2006

I am trying to write a UDF that uses the TRANSPOSE and MMULT functions, but I keep getting #VALUE! errors...

View 3 Replies View Related

Showing 0 For The Min And Max Formulas

Apr 27, 2014

I am currently having an issue with the formula below showing a zero when there is no data, now i am referencing from this cell to another sheet but even with the IF(ISBLANK inside the formula on the other page it still shows a zero which is messing up what i am trying to do on another page.

This is what i am using

=(MAX(P5:P47,P60:P102,P115:P157,P170:P212,P225:P267,P280:P322,P335:P377,P391:P433,P447:P489,
P503:P545,P559:P601,P615:P657,P671:P713,P727:P769,P783:P825,P839:P881,P895:P936)

This is the cell number that has this formula which it is telling me the max number for the column - P938

I am referencing to another sheet like this =IF(ISBLANK('sheet 2'!P938),"",'sheet 2'!P938)

How i get this to stop showing a 0 if there are no data for it to search for the max?

View 7 Replies View Related

Showing New Worksheet

Dec 22, 2006

I have a cell that contains the =Day1!G4. It reveals what is on another worksheet, but I want to be able to have the next cell be: =Day2!G4. Is there any way that I can code this without having to physically type in every cell? I tried, =Day1+1!G4 but it didn't like that.

View 12 Replies View Related

#NUM! And #VALUE Showing As Result

Feb 2, 2010

The formula that I came up with perfectly works well with clear values. Everytime there's a #NUM!, #VALUE! in my array, the result I want wont show up.

=(SUMPRODUCT(--($B$17:$B$22=C4),--($C$17:$C$22=$D$3),--($G$17:$G$22)))/D4

When I tried average+if and it still gave me the same error!

=AVERAGE(IF($B$17:$B$22=$C$4,IF($C$17:$C$22=$D$3,$G$17:$G$28)))

View 4 Replies View Related

Only Showing Certain Graphs?

Dec 18, 2011

I am creating a spreadsheet system for storing school grades, and I have a sheet on my system which shows graphs of the students data, and on this sheet there are rather a lot of graphs, and its sort of a visual overload to see so many graphs on one sheet. Im just wondering if there is any thing which I could insert which would give the person using the system a choice of which class's graph they want to show.

View 1 Replies View Related

Showing #n/a In Vlookup

Jun 9, 2007

I have a column in which all cells do a vlookup. how can i not show the #n/a ?

View 9 Replies View Related

Showing Max With Color

Nov 23, 2007

My mind is going, I can not remember how to use the background color to show the highest number in a group of cells automatically.

View 9 Replies View Related

Formula Not Showing Zero's

Jul 23, 2006

I can't get the zero's to show up in the part number formula in the wooksheet. Also how do you get the zero's to stay when you type numbers such as (03,04,ect.) the zero always disapears. I need it to stay as a number and not text.

View 6 Replies View Related

Add-in Not Showing Under Tools | Add-ins

Oct 31, 2006

I placed an add-in (.xla file) with a " function" and a "sub" into C:Microsoft Office XPOffice10Library. When I go to Tools-->Add-Ins the Add-In doesn't show up. When I browse the file it says it already exists and still doesn't show up. The strange thing is that the "function" does work, but the sub (which is a sub worksheet_change) doesn't run. I also tried doing the steps as they are laid out in http://www.ozgrid.com/VBA/excel-add-in-create.htm and ended up with the same problem. The function works when I open up a brand new Excel application and use Book1, so I know the function isn't embedded in the sheet.

View 4 Replies View Related

Vlookup Showing #N/A

Jul 19, 2007

I have a vlookup that I have almost working but I'm a little stumped my the last bit.
the formula I have is this =VLOOKUP(J4,AW:AY, 3, 0) and the only issue I have is that if there is nothing in J4, then I want nothing to appear in my vlookup cell, but it is currently showing #N/A.

View 6 Replies View Related

Value Showing Up As 0 After Calculating It Through A Loop

Apr 5, 2013

Why the value is showing up as 0. I know it should be 4 but it is returning the value 0 when I run my code clean through. When I step through the code using F8 it shows up as 4 as long as I am on the tab where the worksheet is. Why my code is returning a 0.

VB:
StartRow = 5
Dim CountX As Integer
Dim LastRowX As Range
CountX = 0

[Code] ......

View 2 Replies View Related

Showing Values In A Cell

May 29, 2014

I am using dglinfo with if statements, and I know that the spreadsheet is updated but the only way I can show the value in particular cell is by hitting the fx. Is their a way to show all the values at once instead of going cell by cell?

View 2 Replies View Related

Stop #N/A Showing On A Spreadsheet

Sep 29, 2008

I have a spreadsheet gathering information on vehicles. One of the pieces of information is a 'Date Off Strength'. For the vehicles still on strength I have the symbol '#N/A' showing,, this is linked to a sheet using a VLOOKUP formula, is there a way to stop this symbol showing

View 4 Replies View Related

Showing Duplicate Values

Nov 6, 2008

I have to compile spreadsheets as tables of various values. One column in particular is a column of ID numbers and the worksheet is very very long. I need a method for checking this one column for duplicate ID numbers.

View 4 Replies View Related

Showing WithOUT A Dollar Sign

Nov 12, 2008

I am a new user both to Excel and these forums, but I am very glad they both exist! What I have is this (it is C13 for those playing at home):

View 4 Replies View Related

Cell Limits Showing ####'s

Nov 17, 2008

I am entering text into a cell and as soon as I go over 241 chars, give or take, the data turns to a row of ####'s when I get out of the cell. Help says the limit is 32K and I have wrap text on. What am I missing? The data displays OK when the cell is current, but goes away when the cell is not . When I preview a print I have the ####'s. I am using Excel 2000.

View 9 Replies View Related

Filter Not Showing All Results

Feb 19, 2009

I have a very large spreadsheet (almost 9000 rows). I have filters on all columns, however when I click to see all the results for the column under the filter, just to view the contents of the row, some are not there. Does a filter have a limitted amount of rows it can hold.

View 2 Replies View Related

Override '#DIV/0' Errors From Showing?

Mar 2, 2009

I've got a bunch of formulas that often are trying to divide by zero, hence this error in the cell. Is there an easy way to modify the forumla or format so that if a division by zero is attempted, the cell can show a zero instead of this error?

View 4 Replies View Related

Showing The Top 3 Most Popular Names

Jul 22, 2009

I have a long list of names in a Sheet and would like to show on another sheet what the most common name is and also the top 3 names.

I've tried using various functions but they only appear to show the most common for numbers and chuck back errors with names.

It would be good if I could present the result like this:

Row1: Top Name 1
Row2: Top Name 2, Top Name 3

View 9 Replies View Related

Formula's Showing All The Time

Aug 6, 2009

why formula's I type in to a particular worksheet show all the time(this is a workbook containing numerous macro's). I have checked under 'Options' that the show formula's box is not checked

View 5 Replies View Related

Showing A Difference Between Values

Sep 14, 2009

I have a spreadsheet with a lot of rows and there are three columns that can have a one of two values in it. These values are constantly changing from row to row, not just like a yes or no sort of thing. However 80% of the time they are the same values in all 3 columns, but I want some way of quickly identifying when there is a difference between column G, I, & K which are my three column names. So is there a way of conditionally formatting this to make a difference highlight in red or something like that?

View 9 Replies View Related

Printing With Functions Showing?

Nov 22, 2009

I may be mistaken, but I recall there's a way of printing an excel worksheet with the individual cell functions showing instead of the normal data.

ie;
A1 = "63" (normally)
A1 = "=60+3" (when printed)

View 5 Replies View Related

Formulas Showing As Text

Feb 4, 2010

I am trying to link a form to a spreadsheet that will be used as an import into Access. When I tried to create the form however, all my links are showing as Text and not the information that is in the linked cells. How do I get rid of this?
I am trying to set up some combofields on the form to make entry easier.
Here is what I am working with.

View 2 Replies View Related

Columns Showing As Numbers

Jun 27, 2013

How do I get Excel back to letter column headings and normal A1 cell references rather than C1R1?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved