# Drop Whole Number And Leave Decimal Part Of Number For Calculations?

Feb 9, 2013
How can I drop the whole number part of a number and leaving only the decimal part of the number. Then multiply the decimal part of the number with a number. Then repeat this in a sequence. The object is to convert Lat and Long decimals to Hr. Min. Sec.

eg. 53.535663 .535663*60=32.13978 .13978*60=8 53 32 8

eg. 113.352640 .352640*60=21.1584 .1584*60=9 113 21 9

eg. 113.306579 .306579*60=18.39474 .39474*60=23 113 18 23

View 1 Replies
ADVERTISEMENT
Jan 7, 2009

Is there a way that I can remove the whole number to leave the decimal place.

1.69 should be 0.69

10.71 should be 0.71

0.48 should be 0.48

View 8 Replies
View Related
Oct 23, 2011

I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.

[URL]

View 4 Replies
View Related
Jun 3, 2013

I have a spreadsheet listing all my vehicles and their respective oil filter part number. Now, some vehicles share the same oil filter and I am trying to find a way to return the vehicle description when using the filter # as a value. In my example below, I am looking to fill the third column with the Vehicle that also shares the same oil filter number. i.e. in the Dodge row, toyota would appear in the third column and vice versa in the Toyota row.

Vehicle

Oil Filter #

Also used with

Dodge

51515

Honda

54565

Toyota

51515

I am not well versed in excel lingo which is probably why I have a hard time coming up with a method that works.

View 4 Replies
View Related
Aug 13, 2009

I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...

View 2 Replies
View Related
Jun 10, 2007

I need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.

View 2 Replies
View Related
May 14, 2008

I know the basics of excell, but haven't ever written any formulas or macros (if that's what I need?) that would perform the following opperations. Bare with me, it's a three step process.

1. I need any zero's to the right of the last number (1-9)

to be removed. (I think this can be done with the click of a button, somewhere?)

examples:

a. 173.17300 = 173.173

b. 231.11000 = 231.11

c. 53.00000 = 53.

2. I also need for the decimal to be moved to the right of the last number after the zeroes have been removed from the previous step. (This seems to be the most deficult step, but most important for me.)

examples:

a. 173.173 = 173173

b. 231.11 = 23111

c. 53. = 53

3. I then need to be able to cross reference those remaining numbers with a set of numbers I have.

examples:

Remaining Numbers / My List

a. 173173 / Not on list

b. 23111 / MATCH

c. 53 / Not on list

View 7 Replies
View Related
Sep 20, 2007

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

View 2 Replies
View Related
Nov 13, 2007

Each cell below contain a number and a time + ET. if like say i want to delete the time + ET and leave only the number inside all cell.

1.1425 8:20pm ET ON A1

3.0805 6:10pm ET ON A2

814.30 6:42am ET ON A3

798.70 11:27am ET ON A4

380.00 9:10pm ET ON A5

View 9 Replies
View Related
Mar 18, 2009

I was created an annual leave planner and I would like the box in Colum A to reduce the number of days they have left every time they book leave. I would like it to start off as 25 days leave including UK bank holidays.

View 4 Replies
View Related
Jan 15, 2014

I created this formula =G2*2.9%+.3

what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88

the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30

How do I prevent the formula from calculating if the G column is blank?

View 3 Replies
View Related
Dec 10, 2008

I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

View 3 Replies
View Related
Mar 6, 2014

I have a spreadsheet that contains given answers to a multiple choice test. I want to count the number of times each possible answer has been chosen at the bottom of the column. I have tried to use COUNTIF and that works fine to give me the number times each answer has been chosen but there is just one annoying thing. If the given answer hasn't been chosed by anyone, a "0" is automatically entered into the cell. This tends to really clutter up the spreadsheet and I would prefer for the cell to be left blank if the answer hasn't been chosen by anyone.

The closest I can come up with is: {=IF(D1:D10="","",COUNTIF(D1:D10,"A"))} but unless the answer "A" is chosed in D1, the cell remains blank.

If "A" is chosed in D1, then the formula works and counts all the rest of the cells that have "A" as an answer.

View 2 Replies
View Related
Jun 28, 2012

I have a list in Excel with several hundred 8 digit ID numbers in column D. Column C contains the total number of hours the student employee worked. Column E needs to calculate using two different equations depending on if the ID number in column D is a manager's. If the ID number is an employee the total hours worked is divided by 8. IF the ID number represents a manager the total hours worked is divided by 4. I've attempted this by using an array for the manager ID list and also using VLookup without success.

In essence I need to loop through column D, check which type of ID number is present, and then enter the correct formula in column E.

View 9 Replies
View Related
Oct 9, 2007

I've tried changing the cell format of some number values to text, so the numbers are not read and calculated in to certain formulas, but they're still included in the calculation. I need all values to display in the spreadsheet, but I need exclude a range that would vary.

Is it possible to just change the cell format, so numbers are ignored by a formula?

View 9 Replies
View Related
Jun 19, 2008

I have a formula that will be used by others to obtain a certain result by altering a number in the formula. What I would like to do is make that number obvious as the one that can be changed. The formula reads - "=(M48+P48+Q48)*0.08453-(((J48+K48)*$K$4)*0.1)" -what I would like to do is color the 0.08453 in the Formula Bar so it stands out.

View 4 Replies
View Related
Jul 23, 2008

I would like to enter whole numbers but have them convert to decimal. I have searched and found a solution, but it only references to one column and I need to reference other columns as well. I tried to edit but I’m not very knowledgeable with code. Here is an example of what I am looking for, columns E31:E52, F31:F52, L31:L52, M31:M52, N31:N52. Could someone provide a code to acquire these results?

View 9 Replies
View Related
Jun 15, 2014

I am trying to display result of risk assessment matrix in excel. I have three columns PROBABILITY , IMPACT and RISK RATING (probability *impact).

Probability = Very Low(1), Low(2), Medium (3), High (4), Very High (5)

Impact = Low(1), Medium(2), High(3)

Risk Rating = Low (value between 1 -3), Medium (value between 4 - 6), High (value between 7 - 15)

Is there any way that I just use TEXT in the cell (visible to user) and excel does its magic in the background using the numbers that I have for each TEXT? I don't want to display numbers.

View 2 Replies
View Related
Apr 2, 2014

I'm looking for a formula to break number in to decimal . Which means for 5 digits i.e 12555 to 12.5 and 4 digits i.e 1555 to 1.5

View 4 Replies
View Related
Nov 30, 2013

I am facing few problems with excel ever since I updated windows 8 to windows 8.1. Whenever i try to enter a decimal number in excel, say for eg. 3.4, it reformats to date- Apr03. I even changed the date and time formats for eg. short date is formatted to dd/MM/yy and long date to d MMMM yyyy. The format is set to enlgish (uk).. what should i do?

View 1 Replies
View Related
Dec 5, 2008

I am trying to figure out how to insert a decimal into a number. For ex: 1234 to become 12.34. I tried creating custom or special formatting but I cannot figure it out.

View 3 Replies
View Related
Jun 13, 2013

I am trying to format my lat and longs in order for Fusion Tables to be able to geocode them. I have it down to where I have separated the lat and longs into their own column. I started with this

>Point>-77.056124,35.552686,0.0

my long issues are -78.335,35 i need to get rid of ,35My lat issues are

434.011400

i need to get rid of the 4 in 434

View 6 Replies
View Related
Jan 25, 2014

I have column A which shows the quantity of a product that I have in stock

A1: 20

A2: 20

A3: 20

I also have column D which shows an increasing income, the amount of the increase varies daily but what I need to achieve is that every time cell D is greater than 50 then cell A4 should be the sum of A3 + the number of '50's that were in D3.

So in this example A4 would increase to 22 (because I can spend 100 on 2 items of stock) and cell E3 would show the balance. In this example its 7.35

D1: 18.23

D2: 42.84

D3: 107.35 E3: 7.35

View 6 Replies
View Related
Sep 4, 2009

I need a formula to take the numbers listed in cells D4:D50

If below 1,000,000 than just keep that number value

EXAMPLE DATA

If the Ten Thousands place is 5 or below leave the same if it is higher round up. (use this concept for ALL PLACE VALUES

1,500,000 to 1.5 million (if the rest are zeros)

4,000,000 to 4 million

1,564,535 than to 1.57 million

1,545,535 than to 1.54 million

1,555,000 than to 1.55 million

Same concept for the rest as the Millions (move the rounded place up as needed)

So for Billions it would be 10 Millions place (to round form) I think lol

1,000,000,000 to 1 Billion

1,500,000,000 to 1.5 Billion

1,560,000,000 to 1.57 Billion

1,578,000,000 to 1.58 Billion

1,700,000,000 to 1.7 Billion

I would like this to continue all the way up to place value 999 QUADILLION! (if possible) if the value can go bigger that would be great!

View 9 Replies
View Related
Sep 21, 2007

How do I seperate 1.2 and 1.02 miutes from the same column into Minutes and Seconds Correctly.

How do I get the first to seperate as (1) Min (20) Secs and the next to

seperate to (1) Min (2) Secs

I have 200,000 line of data in one column with times from .o1 mins to 1498.40 mins that I am doing this to. I need to convert it all back to seconds.

View 4 Replies
View Related
Jan 21, 2005

I want to show (in a cell) how much decimals there are after a comma.

View 9 Replies
View Related
Mar 22, 2008

how do i get the numbers after the point.

View 9 Replies
View Related
Jun 5, 2014

Is there a way to format a number into 2 decimal places and when you select the cell you wont see the true value?

For example:

316.2696

I still see the 316.2696 when I click the cell. Instead of 316.27 only.

View 4 Replies
View Related
Feb 3, 2014

How to make a date into a decimal number. For example, Dec 14 I need to convert to 11.45

Its basically the number of months and days that have gone by in that calender year. So the 11.45 is "11 months" have gone by and the .45 is the days that have gone by in the month of December. (the 14th day divided by 31 the total in December = .45)

So another example would be Feb 3rd. My conversion would be 1.10 (1 month has gone by in the calender and 3 days into the month, 3 / 28 = .10 so Feb 3rd is now 1.10)

June 20th = 5.66

Im creating a form so that my employees wont have to hand write these anymore and reduce all the human errors. Its to calculate income for my borrowers. I want them to be able to enter todays date and have excel convert that date into the decimal form. Then they can enter a Year to Date $ amount and that will calculate a monthly income.

< $ amount * decimal date = monthly income YTD>

View 11 Replies
View Related
Jul 10, 2014

Some of my numeric values are with single decimals .....

Eg:

1542.2

9856.5

659855.9

2589.7

When I format in Excel with Number with 2 decimals my results remain the same

But the result which is need is:

1542.20

9856.50

659855.90

2589.70

View 4 Replies
View Related