stevestojan Posted February 22, 2005 Posted February 22, 2005 Ok, I want to have a cell be the ratio of two other cells. I can not for the life of me figure this out and most stuff on the net requires a plug in. For example I want cell C1 to contain "B1:A1" But with the proper division. So, if A1 was 50 and B1 was 100 I want C1 to read 2:1. I was thinking: =B1/C1"&":1" but nadda... any idea?
Astrobot Posted February 22, 2005 Posted February 22, 2005 For example I want cell C1 to contain "B1:A1" But with the proper division. So, if A1 was 50 and B1 was 100 I want C1 to read 2:1. I was thinking: =B1/C1"&":1" but nadda... any idea? Do you want to express the result as a percent, an integer, or a decimal?
stevestojan Posted February 22, 2005 Author Posted February 22, 2005 Do you want to express the result as a percent, an integer, or a decimal? 248368[/snapback] That's the thing. If i wanted to express it as one of those, I could do that with no issue. I want to express it as a ratio... I want the cells to read 1:2, or 4501:1, etc, etc...
Rico Posted February 22, 2005 Posted February 22, 2005 Ok, I want to have a cell be the ratio of two other cells. I can not for the life of me figure this out and most stuff on the net requires a plug in. For example I want cell C1 to contain "B1:A1" But with the proper division. So, if A1 was 50 and B1 was 100 I want C1 to read 2:1. I was thinking: =B1/C1"&":1" but nadda... any idea? 248361[/snapback] An easy way around this would be to use 3 columns of very small width:1st column has the formula 2nd column has ":" 3rd column has "1"
Astrobot Posted February 22, 2005 Posted February 22, 2005 I think you need to use the Data Analysis. Do you have the Data Analysis Tool Pak installed? It will appear as Data Analysis under Tools in the Menu Bar.
SouthernMan Posted February 22, 2005 Posted February 22, 2005 Ok, I want to have a cell be the ratio of two other cells. I can not for the life of me figure this out and most stuff on the net requires a plug in. For example I want cell C1 to contain "B1:A1" But with the proper division. So, if A1 was 50 and B1 was 100 I want C1 to read 2:1. I was thinking: =B1/C1"&":1" but nadda... any idea? 248361[/snapback] Don't know if this is what you're looking for , but try this: Click on the desired cell, then type in =SUM(B1/A1) That's the 'divided by' symbol from your keypad seperating A1 and B1 in this case. Hope this works. There may be other ways to get the same formula, but I use the =sum , parenthisis style and it always seems to work out fine.
Matt in KC Posted February 22, 2005 Posted February 22, 2005 The general format of a ratio by concatenating the cells is as follows: =A1&":"&B1 But it sounds like you want to do any available division then post the result in ratio format. I assume you do not want to do any rounding or dropping of coefficients so you only do the division when one number is a multiple of the other.... I'll look at this for a few minutes before lunch starts. I always like a good puzzle....
stevestojan Posted February 22, 2005 Author Posted February 22, 2005 An easy way around this would be to use 3 columns of very small width:1st column has the formula 2nd column has ":" 3rd column has "1" 248374[/snapback] That's kind of what I have now. I have two columns. 1st contains: "1:" 2nd contains: "B68/C68" It look ok, since i took the border seperating them out, but I would really like to know how to fix it, and not just use a band aid.
Mehser Posted February 22, 2005 Posted February 22, 2005 =QUOTIENT(A1,A2)&":1" Should do it. It worked with a1=100 and a2=50. The result was 2:1.
webtoe Posted February 22, 2005 Posted February 22, 2005 Ok, I want to have a cell be the ratio of two other cells. I can not for the life of me figure this out and most stuff on the net requires a plug in. For example I want cell C1 to contain "B1:A1" But with the proper division. So, if A1 was 50 and B1 was 100 I want C1 to read 2:1. I was thinking: =B1/C1"&":1" but nadda... any idea? 248361[/snapback] In the cell with the formula, go to Format Cell and select Custom Select the number format you want and then type ":1". . I just checked it and it works.
stevestojan Posted February 22, 2005 Author Posted February 22, 2005 The general format of a ratio by concatenating the cells is as follows:=A1&":"&B1 But it sounds like you want to do any available division then post the result in ratio format. I assume you do not want to do any rounding or dropping of coefficients so you only do the division when one number is a multiple of the other.... I'll look at this for a few minutes before lunch starts. I always like a good puzzle.... 248383[/snapback] The first way you recomend would force me to reference two new cells. I could make a cell way off the spread sheet and just put "1:" in it, then make a new row with the division, and make that "B1" (in your example above). And I do the division no matter what. I am looking at conversions. So if A1 is 13 and B1 is 5100, I would want it to say "1:392.3" or even rounded to "1:392"
stevestojan Posted February 22, 2005 Author Posted February 22, 2005 =QUOTIENT(A1,A2)&":1" Should do it. It worked with a1=100 and a2=50. The result was 2:1. 248386[/snapback] This is pretty good except many times i need to ratio to be 1:XXXXX
Mehser Posted February 22, 2005 Posted February 22, 2005 This is pretty good except many times i need to ratio to be 1:XXXXX 248398[/snapback] OK, then use it this way: ="1:" "IENT(A1,A2)
Rubes Posted February 22, 2005 Posted February 22, 2005 use the conditional IF statement. so if A1=50 and B1=100, then set C1=A1/B1 and D1=B1/A1 now set E1 = IF(C1>D1, C1 & ":1", "1:" & D1) This way it will check to see which is greater, and use either XXX:1 or 1:XXX.
Rubes Posted February 22, 2005 Posted February 22, 2005 by the way, the IF statement is: IF (conditional statement, result if TRUE, result if FALSE)
Astrobot Posted February 22, 2005 Posted February 22, 2005 QUOTE(stevestojan @ Feb 22 2005, 12:09 PM)This is pretty good except many times i need to ratio to be 1:XXXXX OK, then use it this way: ="1:" "IENT(A1,A2) This will work.
DC Tom Posted February 22, 2005 Posted February 22, 2005 Ok, I want to have a cell be the ratio of two other cells. I can not for the life of me figure this out and most stuff on the net requires a plug in. For example I want cell C1 to contain "B1:A1" But with the proper division. So, if A1 was 50 and B1 was 100 I want C1 to read 2:1. I was thinking: =B1/C1"&":1" but nadda... any idea? 248361[/snapback] Yeah, I have an idea. Do your own friggin' homework. :I starred in Brokeback Mountain:
stevestojan Posted February 22, 2005 Author Posted February 22, 2005 This will work. 248408[/snapback] hmmm... that didn't work #NAME? (god, i hate seeing that)
Rubes Posted February 22, 2005 Posted February 22, 2005 use the conditional IF statement. so if A1=50 and B1=100, then set C1=A1/B1 and D1=B1/A1 now set E1 = IF(C1>D1, C1 & ":1", "1:" & D1) This way it will check to see which is greater, and use either XXX:1 or 1:XXX. 248405[/snapback] Actually, if you want it to be nicer (sometimes you can get results like 3.03030303:1), you can change the formula for E1 to: E1 = IF(C1>D1,LEFT(C1,4)&":1","1:"&LEFT(D1,4)) This will effectively round fractions to two decimal places. You can change this, of course, by using a number other than 4.
Recommended Posts