Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
I need to take information in 2 different cells, divide it, and show the
result as a ratio 2:1. I know how to set the formula to divide the 2 cells, but the result is a percentage, not in the format I need to see, which is 2:1. Help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
Hi,
Assuming Cells A2 divided by B2, how about: =concatenate(A2/B2,":1") HTH, Alain "Cin" wrote: I need to take information in 2 different cells, divide it, and show the result as a ratio 2:1. I know how to set the formula to divide the 2 cells, but the result is a percentage, not in the format I need to see, which is 2:1. Help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
I think this will do what you want...
=A1/GCD(A1,B1)&":"&B1/GCD(A1,B1) The GCD (greatest common divisor) function requires the Analysis ToolPak add in (Tools/Add-Ins from Excel's menu bar). Rick "Cin" wrote in message ... I need to take information in 2 different cells, divide it, and show the result as a ratio 2:1. I know how to set the formula to divide the 2 cells, but the result is a percentage, not in the format I need to see, which is 2:1. Help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
one way:
Format/Cells/Number/Custom 0\:\1 In article , Cin wrote: I need to take information in 2 different cells, divide it, and show the result as a ratio 2:1. I know how to set the formula to divide the 2 cells, but the result is a percentage, not in the format I need to see, which is 2:1. Help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
=A1/B1
Then: Format Cells... Number Custom General":1" -- Gary''s Student - gsnu200750 "Cin" wrote: I need to take information in 2 different cells, divide it, and show the result as a ratio 2:1. I know how to set the formula to divide the 2 cells, but the result is a percentage, not in the format I need to see, which is 2:1. Help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
That doesn't seem to work correctly... try it with 12 and 8 as the two
numbers. Rick "JE McGimpsey" wrote in message ... one way: Format/Cells/Number/Custom 0\:\1 In article , Cin wrote: I need to take information in 2 different cells, divide it, and show the result as a ratio 2:1. I know how to set the formula to divide the 2 cells, but the result is a percentage, not in the format I need to see, which is 2:1. Help? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
Just add the provision for more digits.
#.#\:\1 #.##\:\1 OR #.#":1" #.##":1" BTW - try yours with 100 and 33 Not wrong ... but doesn't equate to "1" -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" wrote in message ... That doesn't seem to work correctly... try it with 12 and 8 as the two numbers. Rick "JE McGimpsey" wrote in message ... one way: Format/Cells/Number/Custom 0\:\1 In article , Cin wrote: I need to take information in 2 different cells, divide it, and show the result as a ratio 2:1. I know how to set the formula to divide the 2 cells, but the result is a percentage, not in the format I need to see, which is 2:1. Help? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
What do you mean by work correctly"? The key thing is the
\:\1 You could prefix it with any other number format, e.g.: 0.0\:\1 or 0 #/#\:\1 or even ##0.0E+0\:\1 In article , "Rick Rothstein \(MVP - VB\)" wrote: That doesn't seem to work correctly... try it with 12 and 8 as the two numbers. Rick "JE McGimpsey" wrote in message ... one way: Format/Cells/Number/Custom 0\:\1 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
The format you posted will return 2:1 for 12 and 8, but that is not their
correct ratio. Adding the extra decimal places in the format solves that problem; but I still have a question about the ':1' result. Okay, now, perhaps I misread the OP's request (or more than likely, read too much into it); but in my experience when presenting the x:y ratio (odds?) of two numbers, you do that with whole numbers for the x and y. So, my example of 12 and 8 would be shown in a ratio of 3:2 and "pronounced" three-to-two; whereas your result would be 1.5:1 (one-and-one-half to one). So the question is whether the OP wants the ratio to always be against ':1' or not. Unfortunately, she did not give an example by which we could decide. Rick "JE McGimpsey" wrote in message ... What do you mean by work correctly"? The key thing is the \:\1 You could prefix it with any other number format, e.g.: 0.0\:\1 or 0 #/#\:\1 or even ##0.0E+0\:\1 In article , "Rick Rothstein \(MVP - VB\)" wrote: That doesn't seem to work correctly... try it with 12 and 8 as the two numbers. Rick "JE McGimpsey" wrote in message ... one way: Format/Cells/Number/Custom 0\:\1 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
BTW - try yours with 100 and 33
100 and 33 are what is called relatively prime, so they do not contain and common divisors; hence, they cannot be reduced any further. Not wrong ... but doesn't equate to "1" That may be a misunderstanding on my part. See my response to JE in this sub-thread for an explanation of why I posted what I did. Rick |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
Ah. You're absolutely right. I made the opposite assumption from you,
and there's no good reason for it. If the OP wants integral divisors, and doesn't mind the result being Text rather than a number (probably a good assumption): =SUBSTITUTE(TEXT(A1/A2,"0/0"),"/",":") (I don't know if that's been suggested already - didn't see it in *this* thread). In article , "Rick Rothstein \(MVP - VB\)" wrote: So the question is whether the OP wants the ratio to always be against ':1' or not. Unfortunately, she did not give an example by which we could decide. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with ratios
Ah. You're absolutely right. I made the opposite assumption from you,
and there's no good reason for it. Actually, there can be a good reason for that format too. I was a Civil Engineer during my "working life" and side-slopes were always given as ratios against ':1'... 1.5:1 side slope for example; but that was a specialized usage. As I said, the real problem is the OP gave us no context to base an answer on. If the OP wants integral divisors, and doesn't mind the result being Text rather than a number (probably a good assumption): =SUBSTITUTE(TEXT(A1/A2,"0/0"),"/",":") I like that formula better than mine as it does not rely on the Analysis ToolPak the way mine does. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ratios in a range | Excel Discussion (Misc queries) | |||
ratios | Excel Discussion (Misc queries) | |||
Ratios | Excel Discussion (Misc queries) | |||
Formula for Ratios | Excel Worksheet Functions | |||
How do I compute ratios? | Excel Discussion (Misc queries) |