Tracker
HOME    TRACKER    Excel

# Displaying Percentage Values Over 100%

## I'm trying to make my spreadsheet display over 100% while calculating a long column of entries. Each entry is showing percentage cost per hour of a benchmark of \$65.00 per hour. When an entry for example is \$51.10 the percentage displays 0.79%, but if the entry is for example \$73.89 then the displayed value is 1.14%. How do I make this display the percentage over the benchmark of 65 as 110, 115 or whatever it calculates out?

Related Forum Messages:
Percent Change Of 100% To Show 100% In Pivot Table Not DIV/0
I want to make a calculation in a pivot table where a percent difference is calculated by year.

The % difference from calculation does not show an increase from the previous year as 100% but a DIV/0 error. Can i make a custom formula that will use the year base field.

Convert USD\$100 To 100
A1 = USD\$100, I want C1 = 100, What is the formula for C1,

Values Of Column Displaying In Rows
I have some values in the column and i want those values to be displayed in rows now. Example:

In the Excel sheet the data is in the form,
Column1 Column2 Column 3

I want in the format,
Column1
NE_TYPE

Displaying Values In An Entire Column In Another Worksheet
Is there a formula/function in excel that can take all the cells in a column, lets say Column A, and paste/display it in another file without the spaces between the cells with values and without duplicates?

Worksheet1
Column A

1 Name
2
3 Mike
4 Rob
5 Ryan
6
7 Mindy
8 Paul
9
10 Rob
11
12 Mindy
13 Chris

Worksheet2
Column A

1 Name
2 Mike
3 Rob
4 Ryan
5 Mindy
6 Paul
7 Chris
8
9
10
11
12
13

Increase Values By X Percentage
How do I change numerous numbers in several different columns by a constant amount? example reduce each number in each of these columns by 50%.

Percentage Difference Between Two Values
I am trying to create a formula that can work out the % difference between two values. The formula I am using is:

=(B1-A1)/ABS(A1)

So, where

A1=1, B1=2, = 100%
A1=-1, B1=2, = 300%

This seems to work perfectly, apart from where there are zero numbers involved. The answers I want to have are as follows:

A1=0, B1=0, = 0%
A1=0, B1=-1, = -100%
A1=0, B1=1, = 100%
A1=-1, B1=0, = 100%
A1=1, B1=0, = -100%

Coloring Percentage Values
I need to coloring percentage values in excel 2000. What I would like to do is have percentages greater than zero shown in green, while negative values are red, and zero values are black. I currently have the custom formatting so negative values are red and all others are black, but I cant' seem to find a way to change it so greater than zero numbers are green and zeros are black. All I can do is chang positive and zero at the same time. Here is what I currently have:

Displaying Multiple Values Without Using Macros Or ROWS Function
is to display a set of data based on filtered information.

My Data base:
Company Department Name
A X John
A Y Joe
A X Jane
B Y Bob
C Z Kate
A X Kerri

Based on user selection of Company and Department, I want to be able to display the relavent names.

If user chose Company A, and Department X, I want to be able to display
John
Jane
Kerri

I've used the ROWS, Index, Small combination that works perfectly (Please see sample below). However, since the this software doesn't support the ROWS function, and doesn't support Macros.

Displaying Cell Values With Worksheet_Change By Value If Target Is In Certain Range
I have a spreadhseet where columns I and J (range from I6 to J300) serve as input cells, off to the right, 23 columns over in AF and AG respectively I have a hidden array formula (Index, match) calculating values based on input in either column I or J and several factors embedded in reference table in the same sheet. That works fine. I want cells in columns I and J to be interdependent, in other words, input in column I drives calculations in a hidden formula and I want the value of that calculaton to display in column J (in a adjacent cell input in I6 results in display in J6), but if I input value in J then this value will drive calculation in a hidden formula and display in I (let's say I is centimeters and J is inches). I have a code that works (I set it up as a try just for few rows) but only one code section at a time, not together. If I choose column I (#9) to go first in code, values update in J, but not the other way around, if I choose column J (#10) to go first in code, values update in I, but not the other way around. What am I doing wrong, I tried Target.address case, I tried Intersect ... is nothing then etc. They all work one at a time but not together. Here is the code as it stands now

Deleting Rows With Non-percentage Values
What I would like to do is to delete the rows in the attached Excel file highlighted in yellow (whole numbers) and to keep the rows with percentage values. Ideally I would like to have only the rows with the string "Group:" remaining along with the rows that have percentage values in them.

What I'm going to be doing is then transferring these groupings of data to another excel sheet in order to create a time series of performance by "team leads". If anybody would be willing to help me out with the first part of this I would really appreciate it. I have some idea of how to go about it using conditional logic, but I'm just not comfortable enough in VBA to do this efficiently. It would probably take me hours just to get a simple version working....

Exclusion Of Values In SUM/Percentage
I am trying to exclude a certain value from an array of numbers to get a percentage. I have data that populates on its own and in the same columns regularly. The data contains a country name in column A, a year in columnB and a value in column C. I am trying to exclude the value for the U.S. in the totals so that I can get a percentage of values outside of the U.S.

So that the sum of all of the rows less the value corresponding to the U.S., divided by the sum of all of the values.
The problem is that the location of the U.S. value and U.S. designation varies with different inputs (this cannot be helped), so the formula must be able to follow it.

I have been able to do this but not without a lot of extra cells and rows, and I would like to have a single formula.

Finding Percentage Of A Range Of Values
I have a little table:
Columns A:B (separated by commas)
Row 1- Year One, 20
Row 2- Year One, 20
Row 3- Year One, 10
Row 4- Year One, 30
Row 5- Year One, 20
Row 6- Year One, 20
Row 7- Year Two, 10
Row 8- Year Two, 20
Row 9- Year Two, 20
Row 10- Year Two, 10
Row 11- Year Two, 20
Row 12- Year Two, 40

In the adjacent cells in columns C (C1:C12) I need a formula that would turn the value into a percentage of the total of the values for that year (e.g. in C1: 20 is 16.7% of 120, so the value displayed would be 16.7% (or 0.167)).

Adjusting Percentage Values On Cell Change
Good afternoon Gentlemen, I have a column of data, with a varying number of percentage values that add up to 100%, separated by "NA", i.e.

33%
33%
33%
NA
25%
25%
25%
25%
NA
100%
NA
20%
20%
20%
20%
20%
NA

Now... when I change one of the values I would like the others to even up, i.e. in the last example if I change a 20% to 50% I would like the others to change to 10%... any ideas?

Take Cell Values From Six Worksheets And Adds Them Up As A Percentage
How would I write a formula that takes cell values from six worksheets and adds them up as a percentage (quarterly updates)? I realize this is wrong, but here's what I'd tried (for three sheets).

=+SUM(IF(EVH!F7="Y",25,0)+IF(FUN!F7="Y",25,0)+IF(HES!F7="Y",25,0))/3

Return Range Of Numerical Values In Single Column Based On Frequency Percentage
I have a single column of numerical values that may repeat many times within
the column.

I require a flexible Formula:
Use an Input Cell for the specified and changeable Percentage(s) %.
Column may be filtered – so only take into account Visible Filtered Cells.

The Formula will calculate and Return the numerical range of values that fall
between the specified percentage % (using the Input Cell) e.g.; 70%.

The Formula should Return two numeric values: a Start Value and an End Value
– NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
values that appear 70% of the time in the column; therefore, taking into
account Repeat / Duplicate values.

The calculated Results: the two numeric values will be returned to separate
cells on a new Sheet.

Displaying And Un-Displaying Objects Using VBA..
Is It Possible to use VBA Coding to Display An Image (Object),
And Un-Display or Remove that Same Image (Object) using a VBA Code?

Example:

I have a Mailbox Picture that I would Like to Show, but only when a cell's
value = 5. If the Cell's Value is not 5, then don't display the mailbox.

Keep A Formula At 100% Or Less
i need a way to keep a formula at 100% or less, i get a number higher then 101% that is not my way i want it. i get like 120%...600% and so on, when i add a formula up i just need to keep at a full 100% even tho its more then 100%, if its less that fine like 79% ect, just dont want it to go over, if there's a ways you guys can find it out XD i just know it.

Popup Box Appear And Ask For A Value (1-100)
In cell a3 user selects yes or no. Then in cell b3, if they selected yes for a3, a pop up box should appear which asks for a value (1-100), and if they selected no for a3, then b3 automatically becomes zero. How can I do this.

Sum The Last 6 Numbers Below 100
I've been trying to come up with a formula for this for a few hours now, but I can't work it out.

I'm using Excel 2002.

In column C I have a list of 50 numbers. They range from cell C1 to C50.
The numbers are either above of below 100.

Now, in cell D51 I want to sum the last 6 numbers in the list below 100.

So if the list would be:

...
C40 = 19
C41 = 25
C42 = 3
C43 =234
C44 =0
C45 =12
C46 =345
C47 = 0
C48 = 129
C49 = 1
C50 = 12

Now, the last six numbers below 100 (going upwards) are: 12, 1, 0, 12, 0, 3.
These numbers need to be summed. The outcome in this case would be 28.

One Hundred Back To 100
I found a way (on this board) to spell out numbers...I.E.: 140 = "One Hundred Forty" .

But is there a way to convert "One Hundred Forty" to 140 and format as a number or general or pretty much anything but text? I thought would be as easy as Cell Format > Number

Autofilter Is Not 100% Unique
When I use the advanced filter like this:

Dim oRange As Range

Set oRange = ActiveSheet.Range("A:A")

Under certain conditions, I get one duplicated value on "Sheet2". As near as I can tell so far it is only when the first cell in my "oRange" variable has a duplicate(s) of itself elsewhere in the list. The said value then appears twice in the list (only twice out of 19 instances in my particular test).

Also, I noticed that it is automatically naming the first cell in my destination range "Extract". If I change "B1" (above) to something else and run it again it throws an error because of the duplicated name "Extract".

What am I missing? What is the purpose of the automatic naming?

It looks like I need more to guarantee that my filtered list is always 100% unique.

figures as whole percentages and that is getting me hot under the collar - as my little lists rarely add up to an exact 100%. Now, I can
understand where the discrepancy is creeping in with the rounding and all - but is there a sneaky way to get Excel to make the adjustment
for me so that the percentages always total 100%?

Here is an example from my sheet, where the actual figures are added to equal 200, and the cell showing the percentage figure has a simple
formula at present of for example "=(B71/B76)" where B71 is the actual figure (in this case, 12) and B76 is the sum of all the actual figures
(200 below) and the cell is then set to be a percentage with zero decimal places.

12 6% 105 53% 44 22% 31 16% 8 4% 200

So you can see that my percentages here total 101%!

I have spent hours trying to research a sneaky way and am starting to think I'm asking for the impossible - and will just have to 'adjust' the
percentages manually to make my 90 charts look accurate!

Divide Selected Cell By 100
I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).

These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.

Length Of Array With 100 Elements Possible?
I have a STRING array which is dimensioned with 100 elements possible. What i want to know is how to determine the "length" (not sure if this is the right word) of the array. For example: Suppose i have 10 strings in the array. Is there a command to determine that there are only 10 elements in the 100 possilble array?

Consonlidate 100 Different Spreadsheets Into One Workbook
How I can pull over 100 different spreadsheet files into one workbook? I have many diffent files saved in one directory and need to consolidate all of them into one file? Any better ideas to do it via VBA instead of opening up so many times to manually copy them?

Changing Worksheet Names Anywhere Between 50-100
I would like to change the worksheets names (many, anywhere between 50-100) to a cell (A2) value in each of the worksheets.

Limit Data To 100 Rows
I have a user form that writes data from textboxes 1, 2 and 3 into the next empty row in a speadsheet but what i want to do is write the data into rows 1 to 100 then start again at row 1 overwriting the data. The reason is that i have the rows linked to a graph and want to continually show the last 100 results and never have to manually delete old data.

Auto Round Up Or Down To Sum To 100%
I have sales figures from each region and need to convert those to percentage. and then when i try to add up those percentage shown on screen in excel, sometimes they will add up to 100.1% or 99.9%. Then i will have to manualy adjust the one that excel didn't round up but larger comparable to other regions in the 2 dp.

0/0 Formula - Return 100% In Next Cell
I need to return 100% in a cell for cells B2/A2. If B2 = 0, and A2= 0, I want the result to be 100% as a goal attainment returning 100% in C2.
How do I do this? I get a DIV error as usual.

Sumif That Excludes A Column If It As 100% In There
I am try do is a sumif that Excludes a column if it as 100% in there, I have managed to do the sumif (( =SUMIF(A:A,F3,E:E)+SUMIF(A:A,F3,F:F) )) but
I don’t know what to put if I want to ignore it, if it has 100% in a cell

I have attached a test sheet if someone could have a look and try point me in the right direction

It works out a percentage returned if column E / F = the full amount in column k

Make Numbers A Percent Of 100
I have a spreadsheet with a large list of plants. Each plant has a breakdown of colors by container size. Each cell contains a number that corresponds to a percent, e.g. a cell may contain the number 20, which would also mean this number is equal to 20%. I want to change all numbers to a percent of 100, or turn 20, for instance, into .20. There are many hundreds of numbers that I need to make a percent, so I was hoping I could do this in one fell swoop somehow. This percent number will be used in another spreadsheet for calculating on order. How do I do this?

Conditional Formatting Greater Than 100%
In a job workbook, Row 51 is to be filled with reimbursable expenses every month, beginning in Column E and continuing through Z. The numbers that are entered in that row need to be marked up 10%. Is it possible to format the row in such a way that each number entered in each cell is automatically marked up 110%, or multiplied by 1.1?

100% CPU Usage With Many Userforms
We have designed a data collection program in excel which use's userforms for the data entry and form printing. There are 20 userforms, each with a "Next" button which hides the current userform and shows the next userform. The first "next" button has a copy and paste code which copies a today() statement and paste's it in a different cell to record how long it took to complete the data entry. We are able to go through the program once, but when we try a second time it freezes and the cpu usage goes to 100% resulting in us having to End Task.

Alert In VBA If Amount Exceeds 100%
If I already have lets say 90% on B9 and I try to enter 15% on D9 it wont allow me because of the total being over 100%, but how can I have an alert to say Please enter 10% or less.

Or

If I already have lets say 50% on B9 and I try to enter 20% on D9 and I try to enter 50% F9 it wont allow me because of the total being over 100%, but how can I have an alert to say Please enter 70% or less (the sum of B9+D9).

Can this be done using VBA?

I've attached the file that has the data validation.

Macro To Sort 100 000 Rows
I've got up to 150000 rows in excel (2007) and i need to sort the data into 2 sheets.
Sheet1 contains all the data. Sample:

1000999999027FRESH PASTA FETTUCINE WITH TOM102002P N0809100000035000000012810000000+0000000+ 0000000000000000+0203001896409
2000999999027020300189640900000000000005740200070063

As you can see, the first row starts with 1000 and second with 2000. That would be the criteria. All other rows goes exactly the same.

I need macro that copies all the rows starting with 1000 onto sheet2 and all the rows starting with 2000 onto sheet3.

Filtering and copy/paste won't help, because the list is toooooo big. Thatswhy only hope is to find macro that loops threw the list.

VLookup Series Of Data Ranging + And - 100
let's get str8 to the point.
My data sheet comprises of projects in MW. So I have the name of a project and there power in MW.
I would like to fetch all projects that have a similar MW power within a range of + and - 100, and align them one under another.
i.e.:

project 1 = 1000 MW; year=2007
project 2 = 1200 MW; year=2008
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009
project 5 = 800 MW;year=2009

I need a formula that gives me fetches all projects that are =1000 or + and - 100 MW
the result should be the following :

project 1 = 1000 MW; year=2007
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009

I hope I made myself clear...in any case...I'll pass the torch to the Master that can work this dilemna out.

Change VBA Code In 100+ Workbooks
I know that there has got to be a post on here somewhere on this, but anytime I enter in "Import VBA Code" I get back loads of stuff that really isn't relevant.

In post [url] I had to change a line of code so that the formatting macros I made would be accessable to everyone.

Now that I have the fix in place I must import the fix in the one line of code into 100+ workbooks. I suppose I could do this manually, but I imagine that there is a much faster way to do this using VBA.

I was flipping through the "VBA and Macros for Excel" book by Mr. Excel and on page 334-335 I came across something that looked like it might work. However, it seems that this code is switching modules and my code is pasted in "ThisWorkbook".

All of the files are located in the same directory, so it would involve opening all the files in the specified directory, making the VBA code change, saving the file and closing.

Finding Best 5 Based On Result Value Without Exceeding 100
Each car has a value, and each car has odds.

I must choose 5 cars, and the value cannot exceed 100.
I typed in all my data, can Excel choose the best possible lineup for me?

In the end, I need the lowest result possible while only using 100 pts in
value.

I am using Excel 2003
Here is a sample of my data.
Car # Value Odds (#to1) (Result=Value x Odds)
123.5494.00
223.45117.00
322.85.75131.10
422.66.75152.55
522.212.5277.50
62315345.00
71523.5352.50
82217.5385.00
921.820436.00
1019.523.5458.25
1121.623.5507.60
122125525.00
1321.725542.50
141930570.00
1522.130663.00
1620.335710.50
1721.934744.60
182040800.00
1918.845846.00
2018.647.5883.50

Ensure TextBox Entries Add To 100%
I have five text boxes on a user form. I would like the 5th box to always equal 100 - the Sum of the other boxes, and never go below 0.

In other words, the text boxes are representing percentage breakdowns - so to validate the percentage entry in each box i want the last box to 'count down' from 100 as the percentages are distributed amoungst the other boxes. This box will also be a percentage figure, so is crucial it has its own box (rather than just saying these four boxes have to total 100) i hope this makes sense!

I have tried a code along these lines (see below) - (adapted from this forum but couldnt get it to work) I also found a version where one poster used a command to change the 'value' from string to numbers - but have been unable to find that again.

Private Sub txtbox1.change()
txtbox5 = 100 - (CCur(txtbox1) + CCur(txtbox2) + CCur(txtbox3) + CCur(txtbox4))
End Sub

Discount Formulas: Cells To Calculate \$40 X (100%-10%) = \$36
I would like to ask the formulas for this... for example:

\$35 .. discount 10% + 25%

instead of having so many cells to calculate \$40 x (100%-10%) = \$36
\$36 x (100% - 25%) = \$27

Standard Dev For A Whole Column, 100 Points A Time
I have a file, and I want to put in the standart deviation, over 100 points a time. So =C2:C102, next will be C3:C103 etc, but only if there are values in the Easting column. I ID the columns, but something is wrong about by code, it put's like \$ for the row and column.

List Out 6_49 Lottery Combinations Only Those With The Sum Of 100
I need a vba that can generate all set of 6 number combinations with the total sum of 100.
For example: 3, 6, 4, 15, 35, 37 = 100.

Simple Macro For Dividing Cell Content By 100 Needed
I'm only starting to get into the Macro side of excel, and I've created a couple of macros to automatically format cell contents and the like.
However I'm having trouble trying to divide some cell contents by 100.

I have 2 files I'm working with, one contains information regarding cd's and percentages, however the percentages in this file cannot be formatted to percentages (so the powers that be say) for whatever reason.
I copy all this information into another file which does have the percentages formatted as percentages, the result is that the values get multiplied by 100.

100
50
50.25

for example, appear on the new sheet as:
10000%
50000%
5025%

Is it possible to write a macro that will divide these percentages by 100 so the read correctly as:
100%
50%
50.25%

the macro will have to work on selected ranges.

Wrap Text If Input From Message Box Is Over 100 Characters
A user inputs a string into a message box. A particular cell on a worksheet is then given the value of this string. I would like to have this cell automatically format to Wrap Text if the string is longer than 100 characters and skip to the next line after each 100 characters. However, if 101th character is part of a word, then that word would be on the next line. If the string is less than 100 characters then the cell is simply given the value of the inputted string ( I know how to do this )

Timer Function; Macro That Do A Comparison Between 2 Cells Every 100 Milliseconds
What I am trying to do is have a macro that do a comparison between 2 cells every 100 milliseconds. One of the cell is constant updating from RTD (Real Time Data). And if its false, to nothing. However if true, I would want it to run some codes and exit the macro/procedure. I also need some way of stopping the macro at any time.

IF Function Formula Logic: For Every Increase Of Of Mhz, Give 25.00 Per 100 Increase
I have a test due in the morning, and I really need this question answered ASAP, if anyone could. I need to create an IF formula for this situation: the standard Mhz is 500. Give 55.00 for that standard. But for every increase of of Mhz, give 25.00 per 100 increase.

SUM Function - Return The Sum Of Quantities Ordered For Stores With ID Number Between 100 And 199
I have a table with client ID in column A (range A2:A200) and qty ordered by those clients listed in column B (range B2:B200). Clients’ ID numbers range from 101 to 999.

Except through filtering, how to return the sum of quantities ordered for stores with ID number between 100 and 199 ONLY?

Displaying Zero
I have tried different formula involving with decimal places.

If A1 has a whole number like 5141234 then the result would come out as a whole number =(A1-5148000)*1.000440935+48000

If A1 has three decimal places like 5142356.654 then the result would out as three decimal places

Number Formatting: Enter A Number It Seems To Divide It By 100
I having a bit of trouble entering numbers in Excel, every time I try to enter a number it seems to divide it by 100. For example : - 1 entered in to a cell becomes 0.01, 100 becomes 1....etc. I've tried formatting the cells and had no luck, tried Tool>Options but not sure where to look.

Text Keeps On Displaying ##############
1. What is the maximum characters an Excel cell can take?

I try to import a very large text string into an Excel cell. I notice that
it's truncated to a certain maximum length.

When I set the cell type to be TEXT it displays #########################
But when I set cell type to GENERAL then it displays ok.

I just did a Len() on my text cell and it appears the maximum characters
allowed is 330. Is it possible to extend this?