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.

Example:

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.

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.

Example:

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.

- Convert Whole Number Inches to Format "xxFT xxIN"
- Round Off Decimal Number To Whole Number
- Rounding to whole number?
- Round Division Result To Whole Number
- Restrict input in comboBox to whole numbers only
- Rounding up decimals to whole numbers
- How can I convert the result of a formula to a whole number?
- How to Round Whole Numbers?
- Convert Total Hours and Minutes to Whole Number
- After rounding/formatting to whole numbers...
- Change the Value of cell to Whole Number
- Add 000 to whole numbers??
- IF _ is Whole Number
- Change H:MM:SS to whole number
- Getting a formula to treat a fraction as a whole number
- Convert whole numbers to Letters
- How do I round up fractions to the next whole number?
- Extracting Fractions from whole numbers.
- Round to nearest whole number ending in 9
- Whole Numbers to Decimals & Calculate
- Rounding up a cell value to next highest whole number
- Date and time (duration) conversion to whole number
- Recognize a fraction as a whole number
- Data Comparison with Multiple References to One Unique Number

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.

VB:Any direction you could provide would be a big help!LengthConverter() 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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

Nate

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

VB:Am i getting closer?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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

EXAMPLE:

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!

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.

Thanks.

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:

VB:CommandButton3_Click() 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] Else Profile.Hide HeartRiskFactors.Show End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

here is what i want to accomplish.

3.45 ---> 4

12 ----->12

0.33 ----> 1

2.03 ----> 3

2.67 ----> 3

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?

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.

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?

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

number.

int(C1)

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?

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

Barn-Sour

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

Then I tried:

and

But the context is incorrect.

Please help!

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

Thanks

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?

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.

Thanks

Megazoid

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

Ta

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!

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

Thanks!

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

Thanks,

Adi

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?

Thx,

Nika11706

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