Rounding/ "precision As Displayed"

Dec 4, 2009

I have a data output from MS Query that outputs full date/ time stamps, that is dd/mm/yyyy hh:mm:ss. I've tried =round(), =left() and setting the "precision as displayed" option in the advanced options for the workbook, but the full value remains.

I've also in the past seen other outputs imported into Excel (though not my own) come through with strange formats that can only be properly changed having used the "text to columns" tool, but this has made no difference either.

View 4 Replies


ADVERTISEMENT

Precision Through Queries

Apr 28, 2009

I have a program that uses a query to grab data from overseas. I have an issue where some precision is apparently lost and I was curious if there was a way around this.

As an example of what is going on I have a specific piece of data I am tracking. The value is 14.9 in the SQL database and it's datatype there is a real of length 4. After grabbing this item from the query I checked the record set and the value is still 14.9. After it reaches the worksheet though it is displayed as 14.89999962. The destination cell is currently formatted as General but I have tried Text as well. Both produce the same results. As this tool is very general I would not have it formatted as a number as I don't know what I am getting back exactly.

I know computers store real/single values inprecisely but I see it everywhere else as 14.9. I would think Excel would display what I am seeing. Is there anyway around this short of formatting the cell as number with 1 decimal place?

View 4 Replies View Related

Formula Calculation Precision

Dec 14, 2006

I am getting a multiplication error in excel when I multiply 1796 X 156 the answer should be 280,176. However, I am getting an answer of 280,187 any suggestions? I am working with mutiple cells ect.

View 9 Replies View Related

Precision With Identical Variables

Jun 10, 2007

This "If" wont work, does anyone have an idea why? I have try hard coded, with "" and without, i tried to assgind the field value to another double patameter and still nothing. This is my last try where i converted it to double type

View 7 Replies View Related

Solver Change Cell Precision

May 17, 2008

I want to have a restriction that the cell that is to be changed (not the target cell) must have a certain precision eg 0.1 or 0.01 cause i wouldnt want a solution where it changes to 73,67,I would rather it changed to 73,70

View 9 Replies View Related

Banker's Rounding Or Scientific Rounding

Jul 9, 2008

I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals.

Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512.

Here is a list of sample numbers along with desired results:
.0785 should be .078
.5115 should be .512
.5035 should be .504
.0005 should be 0
.0025 should be .002
.0194 should be .019
.0195 should be .02
.0135 should be .014
.0115 should be .012
.8115 should be .812

I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded)

1) =MROUND(A2,0.001)

3) =ROUND(A2,3)

4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOO R(A2,0.001)))

5) =EVEN(A2)

6) =ROUNDUP(A2,3)

7) =ROUNDDOWN(A2,3)

View 9 Replies View Related

Excel 2007 :: Limit To 2 Decimal Places Precision

Oct 14, 2013

Is there a way I can get excel (2007) to limit itself to 2 decimal places for all numbers entered? So effectively I don't want use the formulas TRUNC or ROUND (or any other formula) but I would like rather excel to behave like my calculator where I can limit the decimals to 2 no matter how many I input.

In other words, every time a input a number, excel will round it up automatically to 2 decimal places and the number available will have 2 decimals only. Is there any option hidden somewhere in the Excel options that I couldn't find?

View 2 Replies View Related

Precision, Or My Maths (Each Water Plant Can Provide A Certain % Water Coverage To All Properties)

May 15, 2009

I have a number of houses & a number of water plants (as in water processing plants, not the green kind!) Each water plant can provide a certain % water coverage to all properties. 'All Properties' is classed as the number of houses + the number of water plants. The 'coverage' as a percentage is given by the formula:

View 5 Replies View Related

Using Displayed Values Only

Nov 9, 2009

After searching this board, I am using a check for this issue to alert when the problem occurs, but I'm hoping to tell Excel to fix the problem itself. The issue occurs when a set of data equals 100%, but when fewer decimals are displayed, the displayed total equals 101% or 99%.

Example:
0.6%
1.6%
97.8%

(Now when the display is changed to no decimal points)
1%
2%
98%

As you can see, the data appears to total 101%, and my boss is being a (insert any comment you probably have about your boss) about me not manually reviewing every single report that goes out for this 1% difference. As there are probably 400 different points at which this situation can occur on a daily basis, working harder is not the solution. /rant

I thought I once heard about a formula or setting where Excel will only calculate the displayed values instead of the actual value or formula. Ideally, the formula would know to take the three values, figure out the 100% limitation, and round the largest one down so the other two can round up. The example above is probably 99% applicable, as we would be rounding the largest value down in order to round the other values up.

Meaning intended result:
1%
2%
97%

View 9 Replies View Related

Sum Displayed Cell Only?

Nov 27, 2011

I've data from A1 till G1. Now I will hide column B & D I just want to have total of displayed columns that must ignore values written in column B & D

View 1 Replies View Related

Forumla Result When Displayed

Jun 18, 2009

I have a worksheet I use at work that calculates volume proccesed hourly, and also shows a running total as you enter hourly figures into the table, my question is can you hide everything in the result cell until the figures for each hour are entered ? currently all the result cells have irrelevant numbers in them until you enter the hours figure and the formulas do there thing, I could relly do with them being blank until the relevant hours information is input?

View 2 Replies View Related

Type In 0908 And Have It Displayed As 09:08?

May 14, 2013

I want no time functionality nor any calculation of the entries. I simply want to type into a cell for example: 0908 and have it displayed as 09:08. I tried custom formatting the cell to hh:mm but then it displays as 00:00.

This is for a real-time incident logging sheet and the less the operator has to type, the better. I know it is only an extra Shift+: but it will be so much easier without having to do it.

View 6 Replies View Related

Combobox Displayed Search

Jun 11, 2009

I like the way the names auto fill. Though would ilke to have it so say when roc is entered it shows as a dropdown so you can see that there are more than one ROCHESTER.

Like a search i guess, That would be valid to the first to letters. so if you had name donald, david & daffy when D is entered it showes all three then when DA is showes only David & daffy.

The project that i will 1st use this on is a contact address ph book.

View 14 Replies View Related

Run Macro While Userform Displayed

Jul 7, 2009

I am just wondering if there is a way to allow a macro to continue running in the background once a userform has been displayed? I have disapled the 'close' button and the last action of the macro is to re-enable it.

View 10 Replies View Related

Sum Cell A1 And Result Will Be Displayed In B1?

Jan 3, 2012

I want to sum cell A1 and result will be displayed in B1

AB1ValueResult29292.50*10+8952*15+8550*10

View 9 Replies View Related

Range To Be Displayed In Email Sent

Feb 20, 2012

Using the below code, I want data in range with format (for ex: J2:K7) to be displayed in the email sent in the place of Email_Body1 place.

Private Sub CommandButton1_Click()
Range("H2").Value = Date
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String
Dim i As Long

[Code]....

View 9 Replies View Related

Referencing Displayed Instead Of Numeric Value

May 29, 2012

I'm creating a customized, interactive calendar with several drop-down menus included for each day of the week. I want to have a self-adjusting section at the bottom of each month that shows which values have not been selected in the drop-down menus on any day.

I have a column next to the source list for the drop-down menus with =IF(ISNA(MATCH formulas to show if a value has been selected anywhere on the calendar. If no one has selected a certain value from any of the drop-down menus then "XXXXX" shows up in the row next to its source (one "X" for each column, Mon-Fri). If a value has been selected, then it says "FALSE".

I now want to have a section that lists only the values that have not been used, with "XXXXX" next to the source data. However, any formula I use is not able to differentiate between "FALSE" and "XXXXX" because they are both "0" values in a numerical sense. Can I create a formula that references the displayed value, rather than its true numberic value? What formula would be best to use to list only data not being selected from the drop-down menus? Is there a simpler way of displaying this information than what I'm trying to do?

View 9 Replies View Related

Rasterize Displayed Amount

Nov 21, 2007

I have a column with dates in the following format: dd/mm/yyyy h:mm
but im doing an XML export and excel only exports in text format. I would like to somehow resterize or only use the displayed amount instead of the text which is exported (ie- 37805.1640277778)

View 9 Replies View Related

Keep Userform Displayed But Use Other Cells

Jul 17, 2008

I have a macro that once clicked shows my userform. Is it possible to have this userform displayed whilst also allowing the user to click into other cells on the worksheet?

View 9 Replies View Related

Sum The Cells Displayed Value Not The Actual Value

Jul 10, 2006

On my worksheet, cell A1 and A2 both contain the value:
0.1655555

I've changed the number formatting to three decimal places so it appears on the sheet as 0.166.

In cell A3 I have a forumla =A1+A2 which currently comes back with the value 0.331 (0.1655555+0.1655555=0.331).

Instead of adding together 0.1655555 I want it to add the displayed value of 0.166 so it should then come back with the value 0.332.

View 3 Replies View Related

Macro Of Only 1 Module Is Displayed

May 29, 2007

While working within the same worksheet, why not put all the routines in one Module instead of using different modules?

BTW, what made me raise the above question is that I have 2 modules in my test worksheet. But when I go to Tools | Macro | Macros > This Workbook, it does NOT show the sub-routines of the first module

View 5 Replies View Related

Row Of Data Displayed For Dropdown Selection?

Jul 27, 2014

I have created several records, each record is in it's own row. I'd like for the row to be displayed if the name of that row is selected in a drop down. How can this be accomplished? I've attached the spreadsheet to show my current progress. When a player is selected in a drop down, I would like for that players record to be displayed below the drop down.

UFC.xlsx

View 4 Replies View Related

Subtotal Amount Displayed In Each Row Of Group

Dec 16, 2013

I need to display a subtotal amount in a field named Invoice Total in my spreadsheet. I created a subtotal, which is the amount that I need on each line in the invoice total column. Here is an example:

Invoice Number, Invoice Total, Line_Item_Total
99223, 0, 8.77
99223, 0, 8.77
99223, 0, 17.55
99223, 0, 8.77
subtotal: 43.86
99527, 0, 8.77
subtotal: 8.77

View 4 Replies View Related

Element Name Displayed Based On Abbreviation

Apr 20, 2014

I have the need to create a workbook that will return values based on the entering of an abbreviation. I will use the chemical elements as an example, I wish to have a column formatted so that if 'Au' is entered the cell populates with 'Gold' or if 'Pb' is entered then the cell is populated with 'Lead'

So in my case I have all the abbreviations in one column and all the full descriptions in another (approx 500 different codes/description)

View 3 Replies View Related

Cell Linking Displayed In Text

Sep 21, 2009

I am trying to have a cell in a diffrent work sheet display information from another sheet.

I entered ='Weapons Table'!A48 and it is displaying in text not what is actually in that cell.
on the cell above it I have entered ='Weapons Table'!A47 and it displays fine.

I've tried typing = then just clicking the cell, and it is still just displaying it as text. I've spent 3 days on this sheet and have only 1 error, that is driving me crazy.

View 11 Replies View Related

How To Have Default Value Displayed In Excel List

Apr 2, 2013

I created a drop down list in three cells for date, month and year using data validation.

But I need these cells to display by default DD,MM,YYYY.

what's the way to do it.

View 1 Replies View Related

Identifying The Highest Value Displayed From A Cell

Jul 15, 2008

I want to be able to record the highest value displayed in a cell (b2) in another cell (c2). Cell b2 is a cell that has continuously updating numbers from a live feed, which are updated approximately every second.

--I'm not sure if I need to have a log created based on the changing numbers. If so, I wouldn't anticipate the length of time to determine the highest value would be more than 7 hours.

--Is there a function/filter that would be able to report the highest number recorded?

View 10 Replies View Related

Suppress Msg Box, Stop The Message Being Displayed

Jul 10, 2009

I have a program with a line that deletes a worksheet like so: Sheets(Output).Delete. Where "Output" is the name of the sheet (a string). This works fine, but everytime this line is excecuted excel pops up a message box "Data may exist is the sheet(s) selected for deletion. To permanently delete the data, press Delete." and I have to click delete. Is there anyway to stop this message being displayed?

View 2 Replies View Related

Dates As String Displayed As US And UK Date

Aug 5, 2009

I am currently having a very painful problem with dates.

Currently I assign a value which happens to be a date to a string.

View 11 Replies View Related

Excel Change Sign Of Displayed # To - #

Jul 6, 2006

I have an expense sheet calculated, need to show the sum of the "+" number in
a cell that does a subtraction of 2 numbers .. income and subsidized ... as a
- that it negitave amount in red if it is less than a specific numbe. ie 2500

View 16 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved