# Rounding Conversion Related Formula Query

Nov 29, 2013

I would like to round the weight as per the matrix below. I have tried to put formulae in Column B but it is not yielding the desired result. Formulae in Column C which can convert the data to the desired result?

Sheet1

*
A
B
C

1
Weight
Result With My Formula
Desired Result

2
0.56
1
0.5

[Code] .......

Cell
Formula

B2
=CEILING(A2,0.5)

[Code] .......

## Abnormal Decimal Conversion: Auto-import Statistics From The Web Via Web Query

May 9, 2009

I am auto-importing statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x-1/3 (one-third) and x.2 represents x-2/3 (two-thirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...

## VBA To Read Text Formula And Related Constants And Insert As Formula Into Cell

Jul 30, 2014

I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.

Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example

Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc

I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g

In cell B10: =a+b*A10+c*A10^2

I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.

## If Formula - Look At All Eight Cells Related To These Courses?

Jun 19, 2014

got a formula that checked C4, then D4 for dates (either in or our of date) and returns a count of 0 or 1, with some cells having tet in them also.

The formula I am currently using is below

=IF( OR(F4 ="enrolled",F4 = "O/S"),0, IF(AND(G4="",TODAY()-F4>730),0,IF(AND(TODAY()-F4>730,TODAY()<G4),0,1)) )

I have a range of four courses (all child protection) that should be attended, some are eleanring, some local authority courses and staff should attend at least one but up to all four of them. Although they all fall under the question are they compliant or not (i.e.0 or 1)i do not want to count them all seperately. Is there any way I can look at all eight cells related to these courses and assess the latest date to assess if they are complinat (in date) or non compliant (out of date)?

I have attached the spreadsheet which I hope will clarify things

=IF(AND(E4="",TODAY()-D4>730),0,IF(AND(TODAY()-D4>730,TODAY()<E4),0,1))

## Correct Formula Related To Dates?

Aug 5, 2014

correct formula related to dates;

Assume cell A1 contains a date in DD-MMM-YYYY format. I would like to have a formula that looks in A1 and compares to the current date (TODAYS Date) and if the value in A1 is todays date or greater return "YES" and if not return "No".

## Array Formula To Find The First Related Value

Jun 29, 2009

(Thread: Max array with multiple columns) I got the following array formula:

{=MAX((A45:A47=A28)*(B45:D47))}

Here, where the value in A45:A47 is equal to A28, the maximum value is pulled from the same row, within cells B45:D47.

e.g.

A28 = Apples

A45 = Pears
A46 = Apples
A47 = Bananas

B46 = 10
C46 = 20
D46 = 23

The formula returns 23.

This is great. However, in order to control for a potential situation that will arise, I need to adjust this to find the first matching value within the data set. I do not know what the MAX should change to in order to find the first matching value.

Using the above example, if the data was now:

A45 = Apples
A46 = Pears
A47 = Apples

B46 = 10
C46 = 20
D46 = 23

How do you get the formula to return 10?

The position of "Apples" will change, hence the need for an array formula.

## Formula For Largest Percentage Related To Reference Number

May 15, 2014

I have data one sheet which is

Date Ref No Type %complete
Monday 1 Erect 75
Monday 2 Erect 50
Tuesday 1 Erect 95
Wednesday 1 Erect 100

On the next tab I am consolidating all the reference numbers and want to capture the latest % complete

I have been trying =Max(if ref no=1 and type = Erect, % complete),1

But when I try it for reference number 2, it just says 100% aswell, when that should be 50%.

## Time Conversion Formula

Mar 12, 2009

I have 4 columns:

A B C D
0:00 1:17 56.15 0.06

Column A is in hours:minutes:seconds.milliseconds
Column B is in hours:minutes:seconds.milliseconds
Column C is in seconds.milliseconds
Column D is in hours:minutes:seconds.milliseconds

I would like to represent all these columsn in this format:
hours:minutes:seconds.milliseconds

Is there a simple formula I can use? I was looking at the CONVERT function, but it doesn't appear to work for milliseconds.

## Formula To Fetch Data Related To Matched Value In Separate Workbooks

Dec 20, 2013

So the problem is that I have two workbooks: one has a set of identifying values which are a subset of one of the sets of values in the second and I need to match them up. In addition I need to take the values two columns to the right of the matched values in the second workbook and put them in the first.

Here is what my formula looks like now.

=IF(A5=VLOOKUP(A5,'[WBOOK2]Sheet1'!\$A:\$A,1,TRUE),INDEX('[WBOOK2]Sheet1'!,MATCH(A5,'[WBOOK2]Sheet1'!\$A:\$A,0),3),"NF")

Whenever I try to run it an error message comes up and highlights the match function name.

## Formula For Months To Years Conversion

Aug 14, 2009

Here is my formula:

=E3*(1+E9/365)^(365*E5)

Cell E5 contains a place for you to put in the number of years you want

I want to modify this formula so that it calculates months instead of years, but still be based of a 365 day calendar year.

## Hex To ASCII Conversion Formula In Excel

Apr 17, 2002

I know you can create a DEC2HEX formula. I wanted to convert Hex to Ascii.

When I use HEX2DEC, it puts the ASCII number instead of the actual character.

For instance, if I put the HEX number 4A in Cell A1, I want Cell A2 to display a capital J instead of the number 74 which is J in ASCII.

## Simplifying Military Time Conversion Formula

Jun 17, 2014

I have written a formula to convert military time to 12-hour format.

## Date Conversion Formula For Dates Formatted With Period

Aug 19, 2012

How to create the formula for dates formatted with periods.

Dates are as follows:

01.07.12 - should return as 1-Jul
01.08.12 - should return as 1-Aug
02.08.12 - should return as 2-Aug

## Formula Conversion To A Less Processor Hungry Alternative Such As DCOUNTA

Jan 26, 2007

An OzGrid member kindly provided this formula from another post I made here:
http://www.ozgrid.com/forum/showthre...837#post326837

= SUMPRODUCT((--(WEEKDAY(Database!\$E\$7:\$E\$3973,2)=(ROW()-3))*1)*(Database!\$C\$7:\$C\$3973= 'Pattern Analysis'!\$A\$4)*(Database!\$F\$7:\$F\$3973='Pattern Analysis'!C\$3))

My problem is that the =SUMPRODUCT is slowing my sheet down considerably and I was wondering if any of you formula whizzes can convert it to a less processor hungry alternative such as DCOUNTA (if that's possible) or suggest a better method.

## How To Remove Rounding Formula

Jan 16, 2013

Is there a quick way to remove the rounding formula from my spreadsheet of over 1000 cells with the formula? I would literally have to go to each cell and delete it since the formulas are not consistent in each cell so I can't just copy and paste the new formula from on cell without the rounding funcntion.

Is there a quick way to remove it?

## Rounding With Formatting - Not Formula

Mar 8, 2007

I am trying to figure out if there is a way to round numbers to the nearest thousand without using a formula. I really want to be able to round just as you can set the number of decimal places. I need to be able to do calculations with the actual numbers, but I want to be able to look at rounded numbers.

## Custom Rounding And IF Statement In One Formula

Aug 3, 2013

Round to Nearest Half Based On Defined Range of Decimal thread.

So here is what happens in the first half of the equation....I round numbers to the nearest half based on a specific range of decimals in the original number. For example if the number in A1 is:

28.0 to 28.399 then rounds DOWN to 28
28.4 to 28.799 then rounds to 28.5
28.8 to 28.99 then rounds UP to 29

For this I am using shg's equation:

[Code] ........

This is all I needed for most of my numbers.....but one part goes one step further.....

NOW THE SECOND HALF......Once the first half of the equation rounds the number above, I need the second half of the equation to then do the following. If the resulting number from the first part of equation above is:

1 through 3 = its own size (1 is 1, 1.5 is 1.5, 2 is 2, 2.5 is 2.5, 3 is 3)
3.5 to 7 = 3
7.5 and above = 4
Resulting number from last part of the formula multiply by 2

So this will end up being only 1, 1.5, 2, 2.5, 3, or 4 multipled by 2.

I am pretty sure this second part would be an IF statement (or maybe there is a more efficient way), but I am still learning the ins and outs of that....but I don't know how to use the first formula and add the needed second part into all one formula.

## 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)

## Rounding Decimals Values By Amending Formula

Dec 11, 2012

I am using the following formula to convert m (months) into y/m (years/months)

=IFERROR(QUOTIENT(AQ8,12) & "/" & MOD(AQ8, 12),"")

where in AQ8 I have m (months)

how do I ensure that the formula does not display any decimal points after the m and instead rounds to the nearest month

ie. 100 to show as 8/4 (not as 8/3.5)

note that I have an additional formula working which gives me the 100 (months) in AQ8

IFERROR(AVERAGEIFS('Raw Scores'!AP\$2:AP\$65,'Raw Scores'!D\$2:D\$65,"M"),"")

(so I am guessing that this is rounding it first to the value I see in my cell but it may infact be using 99.84 months for further calcs)

I have tried formatting my cells but this does not work

## Time Clock Six Minute Rounding Formula

Apr 27, 2007

I am trying to figure out the written formula for needing this answer:

For a time clock with 6 minute rounding.

If I type in 8:20 as the arrival time, I need it to round to 8:18, so that the final time will be in tenths.

Here is the rounding:

Actual Rounded Minutes in Time Clock
:57, :58, :59, :00, :01, :02
:03, :04, :05, :06, :07, :08
:09, :10, :11, :12, :13, :14
:15, :16, :17, :18, :19, :20
:21, :22, :23, :24, :25, :26
:27, :28, :29, :30, :31, :32
:33, :34, :35, :36, :37, :38
:39, :40, :41, :42, :43, :44
:45, :46, :47, :48, :49, :50
:51, :52, :53, :54, :55, :56

Anything in the same line will round to the bolded time.

Example: I arrive to work at 8:09 and go to lunch at 11:59, it will round to 8:12 and 12:00, making my time worked 3:48 or 3.80. Then I get back from lunch at 12:59 and leave at 17:14, which will round to 13:00 and 17:12, making my time worked 4:12 or 4.2 for the 2nd 1/2 of the day and 8:00 or 8.0 hours for the day.

Time In----------Time Out----------Total Time----------Decimal Time----------Total Hours
...8:12.................12:00..................3:48......................3.80
..13:00................17:12..................4:12......................4.20..........................8.00

The problem is I want to write the actual time and not have to round everything myself and yet I need the decimal time to match what my time clock actually calculates from.

My current formula for the decimal shown above as 3.80 is this: =(HOUR(F7)*60+MINUTE(F7))/60
My current formula for the total time for the day (regular hours) is this: =IF((((E7-D7)+(I7-H7))*24)>8,8,((E7-D7)+(I7-H7))*24)
My current formula for overtime is this: =IF(((E7-D7)+(I7-H7))*24>8,((E7-D7)+(I7-H7))*24-8,0)

This is not a time clock, it is my personal copy of what my time for the week was/is, so that I can compare it to the actual time card that I have to sign off on.

## Rounding Number In Formula To Remove Decimal

Sep 19, 2006

My formula is not rounding properly. Cells I4, J4 and K4 all filter from the results of cell I3 divided by 3. (e.g. 10/3 = 3.333) I was able to remove the decimals in cells I1:K3, but the between formulas (I4:K4) keeps adding the decimal back, therefore this results in errors to my chart.

## Formula With Text Function Rounding Up Result

Dec 8, 2007

I have the following formula: =TEXT(( SUM(D98,D117,D136))/12,"0")&"' - "&MOD((SUM(D98,D117,D136)),12)&""""

This formula adds the inches in D98, D117 & D136 and divides it by 12 to get the footage of the 3 cells. The problem occurs when you reach the half foot mark...for instance say D98 was 2-0 (24 inches), D117 was 3-0(36 inches) and D136 was 2-6(30 inches) added up this comes out to 7-6(90 inches)...the formula then rounds the footage up to 8 feet...this can understandably be frustrating when I'm trying to get an exact measurement...is there a way to force the formula not to round up?

## Rounding Formula :: Round To The Nearest Integer (whole Number)

Feb 3, 2007

I would like to round this number (3.5) to become 4, but by using this formula =CEILING(A1,0.5) the result will be 3.5

i need a formula that turns 3.5 to 4

when i use this formula =ROUND(A2,0.5), 3.5 becomes 4, but 3.2 becomes 3 not 3.5

i.e. a formula turns 3.2 to 3.5 and 3.5 to 4

## How To Make A Custom Conversion Program / Formula Between Custom Data

Feb 15, 2014

I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)

What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)

My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.

## Formula For MAX Value And Lookup Query?

May 8, 2012

I have been struggling with what seemed at first like a very simple formula. I have the sample data as shown below and I am trying to work out a formula that provides the latest value in sheet 2 from sheet 1 column AF based on a number of conditions.

If Sheet2 A1:A500 = Sheet1 F1:F500
And Sheet1 G1:G500 = max & Sheet1 AF1:AF500 doesn't = blank (i.e. last value in column AF)
Then return adjacent value from Sheet1 AF1:AF500

Sheet 1
F-----G-----AF
127--1-----15.6
127--2------3.1
128--1------2.0
128--2-----
129--1------2.5
129--2------1.6
129--3------2.7
130--1------3.3
130--2------3.1
130--3------1.6
130--4------5.4
130--5------
130--6------

Sheet 2
A--------B (desired results)
127-----3.1
128-----2.0
129-----2.7
130-----5.4

## [SORT OF ]VLOOKUP Formula Query

Aug 31, 2009

I have a problem with the VLOOKUP function and i can't work out what is going on. I've attatched file. On the members sheet in cell F9, the result should equal 13.2, but the formula throws out 13.0. The final VLOOKUP seems to be the problem. The lookup value is F8(which is 13.4), the table array is named 'calc'(on the tables sheet), and column index number works out to be 3. The figure in column 3 is 13.2, yet it throws out 13.0!

## Altering Formula To Read An <= Query

Sep 4, 2007

In cell AD17, I have a formula that returns the number of days or draws(Skips), since the number in cell AD2 has been drawn. The following cells AD18.......... returns the next skip and continues untill all the hits( AD3), are accounted for. My question, Can the formulas be altered to read a "

## If AND Formula Using Hours And Minutes - Query

Jan 23, 2009

I am trying to write a formula that will take the value of cell J9 which is in hours and minutes format (hh:mm) and return a value in £.
ie. if J9 5hours and 12hours £15.00

I used this
=IF((J9)5,(J9)24,(J9)>24),15,))) returns #Value!
It won't work as it won't accept the hours and minutes format in J9. If I maually enter a value in J9 like 7.00 it returns the correct £12.00

## Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

## Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.

My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12

Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False