Formula to calculate 3 variables and return a 3 digit fraction/ratio

**kvargordi** · 02-21-2012, 02:16 AM

Hi, I need help converting 3 variables (seperate cells) into 1 cell as a 3 digit fraction/ratio i/e.

Cell A1 = 5000
Cell B1 = 3000
Cell C1 = 1000

I want a formula to return something like this 5:3:1.

Any help you can provide is much appreciated

**vlady** · 02-21-2012, 02:58 AM

Hello

=A1/GCD(A1,B1)&":"&B1/GCD(A1,B1)&":"&C1/GCD(B1,C1)

GCD
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in

**kvargordi** · 02-21-2012, 07:09 PM

Thanks for that - fantastic!!

Another component to make this formula work is for me to convert Cell C1 which represents 1000 hours, into "1000" as a number.

**Paul** · 02-21-2012, 07:22 PM

The solution depends on what you mean by "represents 1000 hours". Are you saying you have a text string stating "1000 hours" in C1? Or do you have a decimal number such as 41.6667 in cell C1, formatted as [h]:mm to show 1000:00? If the latter is the case, simply multiply C1 by 24 and format as Number.

**lalohuss** · 04-28-2016, 11:22 PM

Hi , i need the ratio for these 3 numbers

–6,000 52,000 122,600

**FDibbins** · 04-29-2016, 12:07 AM

lalohuss welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.