Free Microsoft Excel 2013 Quick Reference

multiple ratio to whole number fraction conversions

I am using Excel 2003.

The situation:
I have instructor to student ratios of 1:36; 1:18; 1:9; 1:4; 1:2; 1:1.

My course requires a total amount of man-hours to be utilized, regardless of the ratio.

All classes going through the course have 36 students.
So, for 1 hour of time spent at a ratio of 1:36 there is only one man hour spent. In 1 hour of time spent at a ratio of 1:18 there are two man hours spent. In 1 hour spent at a ratio of 1:1 there are 36 man hours spent; and so on and so on.

Something else that makes this tricky is that every hour is actually only 50 minutes.

What I need is a way to automatically convert multiple hours of one ratio to the proper amount of hours in another ratio.

I need Excel to convert 5 hours of 1:18 time to 1:4 time. I need it to look and see that 5 hours at 1:18 is 10 man hours then convert those hours to 1:4man hours, which is 22.5 man hours (I think).

Something else that makes this tricky is that every hour is actually only 50 minutes. so after it gets the 22.5 hours (60 minute hours) I need it to convert that to 50 minute hours.

Jeez I hope this made any sense.

Post your answer or comment

comments powered by Disqus

I've been working on a project for work for the last couple weeks and this forum has proven to be invaluable when it comes to working through issues. I couldn't find this one through searching, so alas i will post.

One element of this project is converting a length from textbox "txtLength." I have this box setup so it can be only whole numbers, no fractions/decimals/letters/etc. However, i need to manipulate this number and change it's length so i can later print the length to a Bill of Materials. It's not practical to print this length as inches, because the length could be as high as 360 inches.

Here's where i'm at, i'm having a difficult time wrapping my head around the correct way to approach this.

    Dim sLength As String 'This is the final format of "XXFT XXIN"
    Dim iLength As Integer 'This is the original format of "XXIN"
    iLength = txtLength.Value 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any direction you could provide would be a big help!

Edit: Working through this a little more and it should be something like this, i just don't know the correct functions in excel:

Public Sub LengthConverter() 
    Dim iLengthFT As Integer 
    Dim iLengthIN As Integer 
    Dim sLength As String 
sLengthFT = (iLength/12) With a method To drop the remaining decimal To the Next calculation of: 
    sLengthIN = (Remainder(iLength/12))*12 'Round this up to whole number
    Then would need a method To concatenate the "FT" And "IN" units To the End of the integers. 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Am i getting closer?

Hi all,

i want to do something that should be relatively simple, and yet it is not... has me stumped.

what i'm doing:
taking a range of numbers... from 1-19, 20-39, and so on, and using a vlookup function to pin a rating for said value off a reference table. heres a example of what i'm doing:
A | B
1 1
2 1
3 1
4 1
5 1
... ...
19 1
20 2
... ...
40 3

and so on... cell x = vlookup ... if cell y = 22, use A1:BXX to reference and show rank (pulled from column b)

so heres my problem:

when that calculations are done, i get fraction values for my referenced column. this does not work with my vlookup function.


if i FORMAT a cell to show contents rounded to 1, it does not matter as the actual reference value is .89934143352212

so there is my dilema. is there a function where i can run it on the column to actually ROUND OFF the contents, turning it into a whole number, or is there possibly another way i can swing this vlookup?

thanks for the help!

I look for a function.....maybe in excel....maybe I have to program it. That deducts two dates from eachother - return the difference in years - and round this number (in case of decimals) to the nearest whole number.
Thanks, Lars


Can someone help me please? I work in a finance dept and we often need to divide our numbers into 12 months but I need to only work with whole numbers, not decimals. Is there a way for example of forcing either in a formula or with VBA code to get 10,000 to divide equally by 12 months? At the moment it comes out at 833.333333. What I want is something that randomly assigns 833 to any one of the 11 months of the year and for the other remaining month put in the difference which is 837 to get the total to add up to 10,000exactly.

Any help would be very much appreciated as I am truly stuck.


Hi, I am need help in restricting the input in one of the ComboBox in userform as a whole number.
I have a range of values for ComboBox1 from 12-100. I have set up the following to prevent them from going to the next page-HeartRiskFactor from leaving it blank or entering a digit that is out of range by having a pop-up msgbox.

However I also want to prevent people from going to the HeartRiskFactor page with a msgbox if they enter anything that is not a whole number. How can I made the code in red work?

I've tried my code, the msgbox works fine if I input anything that is not a whole number into combobox1. But when I input everything correctly, the msgbox still pops up and says MsgBox "Must choose from the drop down list". How do i fix my code to make it work properly?

The following is my code:

    If ComboBox1 = "" Or ComboBox2 = "" Or ComboBox3 = "" Or OptionButton1 = False And OptionButton2 = False Then 
        MsgBox "You have not filled in all the appropriate details" 
    ElseIf ComboBox1 < 12 Or ComboBox1 > 100 Or ComboBox2 < 1.5 Or ComboBox2 > 2.4 Or ComboBox3 < 40 Or ComboBox3 > 200 Then 
        MsgBox "One or more than one of the details are out of range" 
        [COLOR=red]ElseIf ComboBox1  "0" Then[/COLOR] 
        [COLOR=red]MsgBox "Must choose from the drop down list"[/COLOR] 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a formula in column E that returns a number, sometimes the number is an exact number and other times with decimals. I cant figure out a formula that would add 1 to numbers with decimals and remove the decimals to make it a whole number irregardless of the decimal value.

here is what i want to accomplish.
3.45 ---> 4
12 ----->12
0.33 ----> 1
2.03 ----> 3
2.67 ----> 3

I have a row of numbers which are the result of a formula in each cell. I
now want to copy those results and use in another spread sheet. How can I
convert the numbers to whole numbers that are not dependent on the formula?

What is the formula for rounding whole numbers down (ie not decimals to
whole numbers)?
I want to round numbers like 14427 to 14420. The numbers are in a formula
such as:
=SUM(C6:C22)*.08 and I want to round the result to the nearest 10.
Thanks for any help.

I have looked and cant find this question anywhere. Lets say I have 49
employees who over 4 days have worked 1,572 hours and 19 minutes. How
do I enter the 1,572 hrs and 19 min in Excel? Once I can do that, then
I believe I multiply x 24 to get a whole number - correct?

I run into this problem quite a bit and haven't been able to find an easy
solution. I have a series of numbers that represent a percentage of the
total, ie 15.8, 67.7 and 16.5 which adds up to 100% and is fine. I need these
to be whole numbers though so after rounding or formatting (I've done both) I
end up with 16, 68 and 17 which adds up to 101 because of the rounding. I
have over 500 lines and I need to flag the ones that add to either 99 or 101
so that I can go in and manually adjust them (unless you can think of some
formula that will force them to 100) but I can't even begin to think of how I
can get it to use this whole number in the formula NOT the original 15.8

How do I change the value of a cell to a whole number? The closest thing I can find on the net is int and it says to do like so
So I tried to program that in to my loop where i is equal to the row number
int("C" & i)
and it says to me
"Compile Error: Expected Identifier"

I'm not even sure if int is what I want to do because after doing further research on it, it seems that int causes the value to round down. So a value of 210.832169 will be 210 instead of 211. Anyone have any insight on what I can search for?

Hello everybody,

Somehow, I've got Excel 2007 to add 000 to every whole number I put in a cell, for instance 2 becomes 2000. . All decimal numbers stay the same, i.e. 1.5 stays 1.5.

I want it to stop doing this. I am sure it is something simple and obvious I but can't seem to find the answer.

Thanks in advance for any help


I'm trying to write an if statement that checks if a value is a whole integer value or not.

I was initially using the Mod operator but realized that the Mod operator rounds values to whole numbers before determining the outcome.

Initially I tried:
But it doesn't have the desired effect as the initial value is rounded to a whole number thus making this statement always

Then I tried:
But the context is incorrect.

Please help!

If you put the number 1800:00:00 in to a cell, then click on it - the formula shows as "15/03/1900 12:00:00 AM"

Is there a way to convert 1800:00:00 to the number 1800? Currently I cannot manipulate anything in that column.


Hello all. I am not exactly a wizard on excel so this may be easy but I can't figure out how to do this.

I have a formula where the result ends up being a fraction, e.g 27.6666667. I can obviously remove the decimal places to round the number display up to 28. However, when I reference that cell in another formula it treats the number as the fraction not the whole number. Is there a way I can get it to treat the number as the displayed number?

Hi Guys, like so many new Excel users I am in the process of trying to learn how to program with VBA.

I am trying to figure out how to convert any whole number to a letter string.

A=1 B=2 .... Z=26 etc AA=27.

I would like to extend the process to accept any whole number. e.g 1000, 50,000, 1,000000 etc....

Is there a standard algorithm for the conversion ? I can then try to write the code for this.



I want to round up a number to the next whole number Ex: Round up 1.2 to 2.0


I am working on a spreadsheet to work out quantities of scaffolding. In the cell C15 I am left with a whole number and a fraction of 1/4, 1/2 or 3/4. I can change the fraction to a decimal if needed. I want to test to see if a fraction is present and depending on what fraction it is then one of three boxes has a 1 placed in it.
I may not have explained this very well, this is my first post here.
If any other info is needed please let me know


Finally got back to this today and it worked!! Yipee
Thank you


I am trying to find a way in excel to round to the nearest whole number ending in 9. For example:
158 should round to 159
296 should round to 299
212 should round to 209

Can anyone help? Thanks!

I want to convert the number in a cell, G7, from a whole number into a decimal and then divide that decimal into a whole number in cell E7 and give me the quotient in cell E8.

ie: 312 = 6.0
286 = 5.5
260 = 5.0
234 = 4.5
208 = 4.0
182 = 3.5
156 = 3.0
130 = 2.5
104 = 2.0
78 = 1.5
52 = 1.0

Could this also be done with the entire range of numbers from 312 to 52 and giving decimals of 6.0, 5.9, 5.8 etc.

Can someone help me do this?

Thank You

Here's my situation. I have a whole number value in cell F18. In cell F24 I want to enter a formula that allows me to divide the value in cell F18 by 4 and if the result is not a whole number, round it up to the next whole number.


I exported a file from a telephone switch reporting system as a "tab separated text" file, because when I export the data in Excel I have to deal with merged cells and other formatting problems.

I have a couple of columns that have time values like 160:02:05 for logged in time, and 34:16:46 as the waiting time… Both are time “DURATIONS” or time in a telephone activity.

I used the Format, Cell, custom and [ss] to get the Time?? value to seconds, and I get 123406 and 576125 respectively. The 1338 is the number of telephone calls handled in the 160+ hours.

I then use these numbers in a calculation. This calculation looks like this: 1338 / ((576125 - 123406) /3600) and Excel gives me 919271.1594 as the answer. The problem is that this is not the right answer!!!

If I do the same calculation on a calculator. The same formula 1338 / ((576125 - 123406)/3600) gives 10.639 as the answer...

I know that the 9.14 is in the right range for my answer.
What is it that I do not know?
What do I need to do to get excel to give me the right number.

Note: all of the other Duration values that I am using in my calculations match the calculator answer. This is the only one that does not!!

Thanks to all for reading my first post, and a very special thank you to the "WIZARD" that can solve this mystery for me!!!

Cell A1: 7
Cell A2: A1/2=3.5. but i have formatted the cell to show the whole number 4
Cell A3: A1-A2. which gives me 4 again. because it is actually 3.5. but what I want is 3. so i want A3 to recognize A2 as 4 not 3.5.

how do i do this?


Good Day,

I am trying to do a data match in excel. I have two excel spreadsheets. Both spreadsheets have mulitple references to a single unique number. There are multiple references to a single unique number because each entry is associated with a different dollar value. For example:

xyz - $489.90
xyz - $9.00
xyz - $85.00
xyz - $1000.00
xyz - $250.00

My unique number is "xyz" but as you can see "xyz" has multiple values associated with it therefore, referenced multiple times on both excel spreadsheets. In this case there are 50+ different unique numbers, i.e. there are 20 different references to "xyz", 25 different references to "abc", 15 different references to "def"..and so on. All due to multiple values associated with each unique number. I need to do a comparison. I need a formula that take the unique number in one spreadsheet and find its match in the second spreadsheet. For example:

Spreadsheet 1 has "xyz" for $489.90. I need a formula that would find only "xyz for $489.90" in Spreadsheet 2 and similar to a VLOOKUP, pull the results, which in this case would be my dollar value, into Spreadsheet 1.

Is this possible?

No luck finding an answer? You could always try Google.