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 have a scenario where I calculate the size of a piece of process equipment, and then select the next highest size from a list of standard equipment that we supply.
I've managed to write an ugly brute-force solution to a relatively straightforward previous situation, but I'm reluctant to redo the entire exercise when I'm sure there's a more straightforward way of doing this.
An example of the problem:
We sell equipment in sizes 1, 3, 8, 12 and 25. I calculate that the piece of equipment theoretically needs to be size 19.6. I want a formula that will automatically generate the answer to this as 25. Similarly, if my formula shows a theoretical size of 12.01, I want the formula to state the actual size as 25.
My previous method of solving this was a series of nested ifs, searching for the rounded value of my formula output, then that value +1, then +2 upto a maximum of +5. This wasn't a pretty sight, but it worked for that situation.
Are there any other ways of solving this that are more elegant than mine?
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.
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.
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.
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)
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?
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.
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?
I created a formula to stop me from having to do an If statement in the case of an error in one of the variables...I'm pretty sure it work fine in all cases I used yesterday, but today when I use the formula to calculate eCatch (e8+e9,e9) the formula result is #name, even when then e8 and e9 are numbers...the vba is embedded in a module in the excel sheet used, so I'm not sure why its coming up with the error.
When this formula executes I have a value, but if some condition in the formula not true I don't want any value. I want the value in A1 to remain the same.
For example: =IF(C1>5;M1;A1)
If I write like this I have cycling. It is not good. I want just to return the value that already was in A1. I change the =CFORMULA with =IF just to explain the problem.
I have a set of data being output by a reporting application as XLS data. For each day of business the report is run, I have a set of 100 rows of data. In each of the these rows of data there is a date field and a locationID field.
What I need to do is add a calculated value on each row that specifies the day of the week (MON, TUES, WED, Etc.) and which occurrence of that days of the week it is for the data set. The enumeration of which occurrence of the day of the week (ie. MON-1, MON-1, MON-3) is determined not by how many dates fall on Monday for the data set, but how many Mondays fall between the earliest and latest dates for that location in the data set.
Example: The data set contains sales data for location XX from 4/6/09 (A Monday) to 4/22/09 (A Wednesday).
The data set contains sales data for location YY from 1/18/10 (A Monday) to 2/9/10 (A Tuesday).
Example: Location XX's data's earliest date is 4/6/09, it's latest date is 4/22/09 From 4/6/09 to 4/22/09 there occurs: 3 Mondays 3 Tuesdays 3 Wednesday 2 Thursdays 2 Fridays 2 Saturdays 2 Sundays
Location YY's data's earliest date is 1/18/10 it's latest date is 2/9/10 From 1/18/10 to 2/9/10 there occurs: 4 Mondays 4 Tuesdays 3 Wednesday 3 Thursdays 3 Fridays 3 Saturdays 3 Sundays
For all data for location XX, I need to set the new calculated columns to: Where Date = 4/6/09 & Location = XX, DOW# column = MON-1 Where Date = 4/13/09 & Location = XX, DOW# column = MON-2 Where Date = 4/20/09 & Location = XX, DOW# column = MON-3 Repeat for all dates between min-max date for that location
For all data for location YY, I need to set the new calculated columns to: Where Date = 1/18/10 & Location = XX, DOW# column = MON-1 Where Date = 1/21/10 & Location = XX, DOW# column = THU-1 Where Date = 1/25/10 & Location = XX, DOW# column = MON-2 Where Date = 1/24/10 & Location = XX, DOW# column = THU-2 Where Date = 2/1/10 & Location = XX, DOW# column = MON-3 Where Date = 2/4/10 & Location = XX, DOW# column = THU-3 Where Date = 2/8/10 & Location = XX, DOW# column = MON-3
Repeat for all dates between min-max date for that location.
A typical data run will have data for 4 or more locations spanning 30-60 days, so figuring out a formula that I can drop onto a column of the exported data so i can run pivot table analysis would be great.
I have entered a date like May 21 in a cell. I want to enter a formula that will take that date and automatically add 7 days to it (after I create and run a macro of course). I just have no idea what formula to use to continually change the date from the 21st to the 28th to June 4th etc. each time I run the macro on a copied excel worksheet.
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
The test takers are shown three pictures. Under each picture is a number. So for example, let's say there is a plane, a car and a train presented for question #1. Underneath those pictures are the numbers 1, 2, and 3 respectively. Those numbers represent a tendency to a certain occupational field. After 55 questions, we can make a pretty good recommendation of where we think they should look for a job (without going into the specifics of the test).
There are 11 occupational fields. In excel, I make those fields A through K. In field L1 through L55 I put in their answers.
Let's say that in all 55 questions, for example, answering 1, 3, and 5 lead to vocational choice K, I want it to tally it up for me automatically. I don't want to have to count those by hand.
I just want to put in the patients answers, and have excel do the work. I tried using the "countif" function, but it doesn't look like you can add multiple conditions.
I need something that will be like this: add 1 each time (as in a tally) to the cell I choose, for the answers that I specify. So, ultimately, it will be tallying up the answers based on which categories that I correspond them to into the respective cells.
So to be redundant... Let's say that answering 3, 7, and 9 to questions 1, 2, and 3 pertain to cell B. After I put those in, I want cell B to have the number 3 in it.
I'm working on creating a custom formula that loops through each row of data and performs a range of calculations when a common number appears in a field (payroll number).
The formula is being applied to 2 sets of data, both are similar however 1 has a few more fields.
When I use the custom formula in the 2nd dataset I keep getting a Circular Reference.
I have attached a stripped down version of the whole spreadsheet, which just relates to the part causing the problems.
On the sheet 'Teaching (Yr1)', the calculation works fine. On the sheet 'Support (Yr1)', the calculation doesn't and Excel prompts with a Circular Reference.
I've tried using the Circular Reference toolbar to trace the predecessors, however it doesn't highlight what they are. All the inputs are blue, and none of them perform any calculations on the cell/column with the formula in it (unless I'm being exceedingly blind!)
Excel gives the following message ...
Originally Posted by Excel
Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you.
I've spent many hours (probably 10+!) debugging the formula trying to ascertain why it's happening and I'm stumped! I've run through all the named ranges, I've changed the formula and still no success.
What I have noticed though, that for some reason the same formula is 'called' multiple times, in fact 3 to be precise.
When I enable Tools -> Options -> Calculation -> Iteration, the formula calculates, however whenever the spreadsheet does an full calculation, it reverts back to #VALUE!
I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)
The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.
For example, I want I37 on most sheets to have the same formula. So I have the following:
I am creating a custom autonumber in excel. The autonumber will be based on the value of another cell's value. So for eg, in cell A1 will look at B1, if B1 has the number 1, A1 will take B1 and add the autonumber to it, eg a, b, c.
If within, b5 the number is 2. So if the number in the corresponding row in b changes, the autonumber must restart.
I've decided to go with a-z for the autonumber as i realized for .1, .2, .3 is limited to 9 values in the list bec. 1.10 may be read as 1.1
I've tried a few options such as creating a named range, however the problem is getting the autonumber to restart.
Below is what i want to achieve via excel formula:
How do I set up a custom validation formula to prevent duplicate enties?
For example I I've already enter the song name 19 and Paul Hardcastle (BandName), how do I set it up where I can not enter that combination again on a row?
I was wanting to use a custom CountIF function but I could not get it to work.
A coworker is setting up an MS Project...project, and adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I've written the formula in excel, but I've never even touched Project until this week. Apparently it doesn't take formulas quite the same way as Excel does. I tried using the ''Switch" function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we're dealing with. Would there be a way to do this using VBA? (I know next to nothing here as well.)
if-and-or statement if possible. The formula would go in cell A10.
If D5="x" and D4=7, answer would be "H8", if D4 is blank, answer would be "x". If D5=2 or 3, and D4=7, answer would be "H8", if D4 is blank, answer would be "v8". If D5=1 or blank and D4=7 or blank, answer would be " ".
I am a sales rep and am designing a spread sheet to help me track my commissions, on occasion I need to split the commission with another rep I have the spreadsheet set up as follows:
J1 Dollar amount - the total amount of the sale. K1 fee% - my fee is negotiable so this number will vary by client this field is formatted as a precentage. L1 total commission - takes J1*K1 to give me a $ amount M1 split y/n - here is my question, i would like a formula that will divide L1 in half if I type a Y or Yes in M1 I'm sure this can be done but I dont know how, also would the formula go in L1 or M1 if I want the results to be displayed in L1?
I'm entering a simple IF statement in excel that asks if one cell is bigger then another. However one of the cells I'm referencing is also a formula, so excel is not reading the value in that cell. How can I get around this problem? Is there anything that will enable me to compare just the value of that cell? I have attatched the file and the cell in question is F21.
I want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
