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?

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?

- Ratio Format in Cells
- How do I calculate and display ratio ( format 3.5:2)
- Line Graph Data Recognition
- Line Graph Data Recognition
- USD $20.00 for Simple Conditional Formatting with Ratio Formulas
- Defined format to show ratios.
- Conditional Formatting based on a ratio
- Excel 2007 ratio formula
- Ratio Calculation
- Need to change format based on cell value
- Figuring Photographic sizes from Ratios
- Different Formatting within one cell - help, please.
- Format Numbers as Ratios ie. 6.7:1
- Converting time formats in Excel
- Data conversion/Cell formatting
- Displaying Ratios
- Conditional formating based on other cell value
- How do I stop excel from changing my ratios into time format?
- Pivot table drill down formatting
- Split Cell Format
- Paste changes ratios to dates
- Ratio function
- How do I express a ratio (1:4 meaning 1 adult to every 4 children.
- Custom formatting ???/????

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

I'm trying to calculate a ratio then print the ratio in a report. The format

I want to use is 4.5:1.

I want to use is 4.5:1.

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.

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.

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!

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..

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.

Regards

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

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

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)

Thanks for your help!

Ruth-Ann

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

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.

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.

Any advice is greatly appreciated,

Thanx,

ziggy1971

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.

Thanks in advance,

Steve

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?

All help gratefully received.

Thanks

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?

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

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

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?

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?

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.

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.

please write me about this issue