Free Microsoft Excel 2013 Quick Reference

# ratio formatting

In an older version of Excel (and I can't remember which one) I was able to
do a numerical format for ratios x:xx. I don't remember what I did and no
longer have the workbook/sheets that contained the formatting. I am
currently using Office XP and Office:Mac 2003 -- I cannot figure out how to
format for ratios -- I don't want to use fractions (for esthetics) as my
display.

Any clues?

## Related Results

### Ratio Format in Cells

Hi All,

i am being asked to do some ratio's and can get the information into a %age format, but can't get the numbers into a ratio format, ie if i have 10 shots at goal and score 3 times i want the cell to say 10:3.

I can just do =cell&":"&cell, however where its 10:5 i'd like excel to recalculate to 2:1.

Is this possible?

Rgds

Hibster

### How do I calculate and display ratio ( format 3.5:2)

I'm trying to calculate a ratio then print the ratio in a report. The format
I want to use is 4.5:1.

### Line Graph Data Recognition

I have an array (about 15 x 15) with years 1990-2003 along the top that is
missing data in some years, i.e. one series begins at 1990 and goes to 2000
while other series begin in 1998 and goes to 2003. The array content is in
number format to the first decimal place, e.g. 14.1. These values were
originally created by a formula and were in ratio format, e.g. 0.141, but I
converted them by multiplying them by 100 and then removed the fomula by the
"copying and paste value" command. When line graphing the array using the
ratio values, the image turns out as anticipated, i.e. the graph line
associated with a particular data series stops when the spreadsheet data
stops and similarly starts when the spreadsheet includes data. However, when
using the array containing the converted values, e.g. 14.1, the line graph
always begins and finishes a partial data series, e.g. one going from
1990-2000 or from 1997-2003, by going to zero. For example, if a data series
goes from 1990-2000 in the spreadsheet, the graph will plot a line that goes
from the value in 2000 to zero in 2001, even though the cell for 2001 for
that data series is empty.
I have created a "test" array in the same spreadsheet and entered some test
values that approximate those in the original array and am able to graph them
without any problem, i.e. the graph wizard doesn't automatically begin and
end data series with zero values.
I have tried every trick I can think of to get around this problem with no
success. I have copied the original array into different spreadsheets but
the problem seems to follow this data array. I have tried retyping the data
in the "offending" cells of the original array - no luck. However, I have
copied the array to a new location and "gutted" the data included in the
array (keeping only the column and row headers) and repopulated the array
data with "test" data that appears to be in the same format as the deleted
data and have been able to graph successfully.
Thus, the problem appears to reside with the data in the original array -
but I can't figure out how to resolve the issue.
All suugestions would be most welcome. Thank you in advance.

### Line Graph Data Recognition

I have an array (about 15 x 15) with years 1990-2003 along the top that is
missing data in some years, i.e. one series begins at 1990 and goes to 2000
while other series begin in 1998 and goes to 2003. The array content is in
number format to the first decimal place, e.g. 14.1. These values were
originally created by a formula and were in ratio format, e.g. 0.141, but I
converted them by multiplying them by 100 and then removed the fomula by the
"copying and paste value" command. When line graphing the array using the
ratio values, the image turns out as anticipated, i.e. the graph line
associated with a particular data series stops when the spreadsheet data
stops and similarly starts when the spreadsheet includes data. However, when
using the array containing the converted values, e.g. 14.1, the line graph
always begins and finishes a partial data series, e.g. one going from
1990-2000 or from 1997-2003, by going to zero. For example, if a data series
goes from 1990-2000 in the spreadsheet, the graph will plot a line that goes
from the value in 2000 to zero in 2001, even though the cell for 2001 for
that data series is empty.
I have created a "test" array in the same spreadsheet and entered some test
values that approximate those in the original array and am able to graph them
without any problem, i.e. the graph wizard doesn't automatically begin and
end data series with zero values.
I have tried every trick I can think of to get around this problem with no
success. I have copied the original array into different spreadsheets but
the problem seems to follow this data array. I have tried retyping the data
in the "offending" cells of the original array - no luck. However, I have
copied the array to a new location and "gutted" the data included in the
array (keeping only the column and row headers) and repopulated the array
data with "test" data that appears to be in the same format as the deleted
data and have been able to graph successfully.
Thus, the problem appears to reside with the data in the original array -
but I can't figure out how to resolve the issue.
All suugestions would be most welcome. Thank you in advance.

### USD \$20.00 for Simple Conditional Formatting with Ratio Formulas

Financial Ratios Workbook.xlsxGood morning,
I would like assistance in conditional formatting. I've attached a spreadsheet that would need the changes/formulas applied to. Basically, the columns (there could be more columns inserted depending on the type of ratio used and the variables that are to be included) consist of financial statement values. These values can be manually plugged in. Once they are plugged in I would like the column that reads "Result" to automatically populate the answer in either red or green. Green means that the value is positive. Red means the value is negative. If it is zero then it can be black. Please see if this can be done. I will pay \$20USD. Additionally, more importantly, I would like instructions on how you did the application. I want to learn how it is done so I can do it in the future.

Thanks!

### Defined format to show ratios.

I want to express ratios as in
100 Employee in Factory : 10 Employees in Headoffice.

I just need to type 1:10 and it should remain as that without MS Excel considering it as time..

It gives a problem while typing 1:9 , it changes 1:09 which i dont want any suggestions of havingthis format saved in Excel-- fromat cells-Custom..

### Conditional Formatting based on a ratio

I have a cell returning a ratio based on the values in 2 other cells. The calculation is a fraction, and the formatting is a custom formatting of 0.0":1"

My goal is a ratio of 4:1. I want the "actuals" cell to be red if it is showing 4.1:1 or more....and green if it is 4:1 or less. I know how to create conditional formatting, but because it's a ratio, I can't seem to get the right formula in my conditional formatting that will return the right colour.

Any ideas? See attached for a very simple example.

### Excel 2007 ratio formula

Trying desperately to find a formula which will return a ratio outcome for excel 2007. Have used the gcd calc and that does not seem to work, for lord only knows what reason, it returns a #value. What is this thicko doing wrong? I have ad-in's active and still nothing. In my calc i am trying to pull information from two cells that in themselves are the result of a formula, so maybe i need to format the ratio calc cell differently, i don't know. Hope someone can put me out of my misery otherwise i am going to end up with repetitive strain injury from typing, and we all know that leads to an even worse condition called typers cramp.

Regards

### Ratio Calculation

How can you calc porportion/ratio on a spreadsheet

we are talking about student to computer ratio

Students Computers Ratio
200 50
100 20
300 60

the answer should show as a ration with the two little dots

for example 4:1

Is there a formula (I could not find)?
a custom format ?
Combination of both?

Many thanks

### Need to change format based on cell value

Morning from the UK,

Depending on the value of a Cell, I have a IF formula that SUMs or Averages the Sutotalled values from an Autofiltered list.

This works well ...however the bit I'm stuck on is,

IF c2 = A,B or X then sum is straight numbers added (as in Subtotal(9,...)) together,

IF c2 = 'Y', then then numbers are currency and I need to show the € euro symbol,

IF c2 = AVE,RATIO,AVY2D or 'Z' then its an Average % that is being returned (as in Subtotal(1,...) )

The formula at the moment in H2 is:-

=IF(OR(\$C\$2="=A",\$C\$2="=B",\$C\$2="X"), SUBTOTAL(9,H6:H389),IF(OR(\$C\$2="=AVE,\$C\$2="=RATIO",\$C\$2="=Z",\$C\$2="=AVY2D"),SUBTOTAL(1,H6:H389) *1%,IF(\$C\$2="=Y", "€"&SUBTOTAL(9,H6:H389),"")))

The " *1%" doesn't return the Ave % and the "€"& , works but shows all 5 decimal places and I can't get it to show none.....should be in Format €0,000,000

....tried variations on Format(SUBTOTAL(9,H6:H389),€0,000,000) ..but to no avail

Thought maybe Conditional formatting would help ...but couldn't see how either..

...probably missing something very simple after all this

Any help apreciated

thanks

### Figuring Photographic sizes from Ratios

Here is one for you guys that are into photography.

Depending on the digital camera that you use, you are taking pictures in one of several formats as follows

Aspect Ratios Camera Crop factor
3:2 ------------ 1.5
4:3 ------------ 1.3
5:4 ------------ 1.25
7:5 ------------ 1.4

Taking the 5:4 aspect ratio as an example you can produce
the following sizes of photos WITHOUT CROPPING
4x5, 8x10, 16x20 etc

I want to create a sheet where each of the aspect ratios is at the top of a column
and when I put in one length of a side of a pic the other length is calculated to it's accurate
proportion.

Example on the 5:4 column, I could put in 8" and have the computer return the answer of 10" (THE APPROPRIATE SIZE TO FIT)

### Different Formatting within one cell - help, please.

Hi, I have several tables of results in which some cells contain a percentage value followed by an odds ratio in brackets - e.g. - 14.2 (1.3); some other cells would have ns within the brackets to indicate it is not significant. I would like to have the info in the brackets (and including the brackets) to be a slightly smaller font size than the percent value. Aside from manually highlighting each OR and changing the font size, is there a formula/format/function/macro I can create and then simply apply to the relevant cells? Thanks for any help you can provide. This whole area of functions and formulas is new to me!

Ruth-Ann

### Format Numbers as Ratios ie. 6.7:1

How do you show percentages as ratios in excel. I only seem to be able to generate fractions

### Converting time formats in Excel

I am trying to get a throughput value from a time to volume ratio.

For example;
I have the working time of 1:15 and want to divide my volume of 3200 into it
to get a through put value.

What i would really like is to figure out a way to convert time formats from
the standard HH:MM to just minutes, ie: 1:15 (1 hour 15 minutes) to be 75
minutes.

### Data conversion/Cell formatting

Hello all,

I'm having a little trouble figuring out how to do some conversions.

For instance, I would like to know how to convert a decimal number like this:
1.800

which is a ratio of 1.800:1 to display like this:
9:5

So, when the 1st value changes to something like this:
1.778

It would be displayed like this:
16:9

Likewise, a ratio of 1.333:1 would be displayed as 4:3.

I also don't want it display in Time format as Excel has a habit of converting values with colons to time format.

Thanx,
ziggy1971

### Displaying Ratios

Hi,

I am using the below formula to display ratios
=CONCATENATE("1:",(F6/C6))

Is it possible to only display this to 4 d.p.?
I have tried formating cells to only display 4 d.p. but it isn't working.

Steve

### Conditional formating based on other cell value

hI, i have a spreadsheet with the following:

Cell C9 contains a dropdown validation box and could contain:
Volume
Rate A
Rate B
Ratio A

Dependent on the option chosen, range D9:AM9 changes to show the correponding values based on a vlookup.

However the format can sometimes be wrong, for example:

Volume is an integer with no decimal place,
Rate A is a percentage with 1 decimal place
Rate B is also percentage with 1 decimal place
and Ratio A is an integer with 2 decimal places.

How can i get the format of A9:AM9 to alter depending on the value in
cell C9?

Thanks

### How do I stop excel from changing my ratios into time format?

I'm trying to type ratios such as "0:1:0" into my excel spredsheet. Excel,
in its infinite wisdom, keeps changing it to "12:01:00 AM." How do I tell
excel to leave the cell alone after I type what I WANT to go into it?

### Pivot table drill down formatting

I am using a pivot table with the right formatting but everytime I
double-click to drill down to see the details, all my formattings are not
retained. For example, I have a column formatted as currency \$ and it reverts
to just default Excel number. My ratio column is set to 2 decimal points but
when I drill down, it shows 9 decimal points. Is it possible to keep the same
format when drilling down?

Thanks

### Split Cell Format

Hi

I am looking for a method of combining 2 pieces of data into 1 cell

ie 2 Cells merged? and/or formatted? as one split cell as per the mock-up in the attached word document.

120 Enquiries, Closing Ratio of 40%

to look something like the attached

Thanks

Steve

### Paste changes ratios to dates

I'm cutting a table from Yahoo! Finance and pasting it into a worksheet. One
of the columns is called Ratio, and it's expressed as 3/2, 2/1 etc. Excel
thinks it's a date, formats it as March 2, 2005, Februrary 1, 2005 etc. and
stores it as a date number. I've tried preformatting the paste target as
text, but Excel is very stubborn, and persists in storing the data as date
number. If a do a paste special, I lose all the tabs, so can't do
text-to-table. How do I force it to paste everything as text?

### Ratio function

I have a series of numbers I have to convert to a ratio. Does have a
function to do this? I have tried the following formula=B26/c26 and then
converting the number to (format>cells>number>custom>general). Do you have
any other suggestions to my problem?

### How do I express a ratio (1:4 meaning 1 adult to every 4 children.

I am trying to find a way to express a fraction as a ratio 1:4, I have tried
programming it simply to look like a fraction (using the format cells/custom
option) but this doesn't work. I am using the 2000 version.

### Custom formatting ???/????

I would like to see a fraction of two numbers from two different cells
exactly as it is.
measn the ratio of 2000 to 4000 as 2000/4000 in another cell. WIth the
custom way of cell formatting ( ????/????) I am unable to see this kind of
display.