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] .......
Spreadsheet Formulas
Cell
Formula
B2
=CEILING(A2,0.5)
[Code] .......
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?...
View 2 Replies View RelatedI 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.
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;
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".
(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.
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%.
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.
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.
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.
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.
I have written a formula to convert military time to 12-hour format.
View 3 Replies View RelatedHow 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
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.
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?
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.
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.
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)
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
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.
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.
View 4 Replies View RelatedI 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?
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
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.
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
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!
View 4 Replies View RelatedIn 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 "
View 9 Replies View RelatedI 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
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.
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