Is there a was to have Excel use the rounded number in its next calculation. E.g., if cell C1 =SUM(A1/B1) and were to be $3.12345, Excel will display $3.12. If the next operation were to be =Sum(C1*0.5), can one have excel use the $3.12 as the value for C1 rather than the $3.12345?

Thanks

Thanks

- Need to force PT to use formated number
- Rounding numbers and formulas
- Find Column Number and then use Index Function
- Converting a Date into a String, Indicating the Quarter Number and Year
- Extract number and round up
- Using displayed rounded numbers in subsequent calcualtions
- How to round negative and positive numbers
- Extract number and use in formula from text & numbers in cell
- Cusip (number and text) in same cell- How do use in excel?
- ROUNDED NUMBERS IN FORMULA'S
- Extract number and use in formula from text & numbers in cell
- Rounding numbers to the nearest thousand
- Displaying a number as rounded while maintaining precision
- Rounded numbers do not add properly.
- Rounding to higher and lower of a set multiple
- How to round numbers to the nearest 5 without using VBA or formula?
- Rounding Numbers and setting a minimum value
- Increase value of number in formula/using named cells. And use ofIF sentence
- Cusip (number and text) in same cell- How do use in excel?
- Using text box input to lookup number and replace based on user input into new column
- Rounding numbers and =if statement
- Identify Row Number based on Value in a cell and use that Row number in a Macro
- Rounding Numbers for Calcs
- Rounding Numbers a specific way (Behind the Scenes Too)

from a field that contains a =median() formula. The formula often gives

deicmal results (3.5 for example) and I use cell formating to round the

number to the nearest whole.

Whoever, the PivotTable refuses to use the rounded number and instead uses

the decimal result from the formula. I need the PivotTable to use the rounded

number and not the decimal number. Has any one had any experience with this?

Thanks,

David Russell

Software Support Analyst

School District 54

Ex. 42.7 is the cell formatted to round to 1 place

42.664237 is the actual number

The constant equation is ((A1-38.3)/38.3)+1 . This cell is formatted to round to 4 places

Answer 1 is :1.1149

Answer 2 is :1.1139

Is there a way to just use the rounded number?

Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project.

For Example

A B C D E etc.

Products --> X Y Z AA

Proj 1 2 3

Proj 2 1 4 5

Proj 3 2 4

etc.

I want to be able to create a report for any given product.

The report could look like,

Product Z

Proj 1 3

Proj 3 2

So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A.

Thanks in advance for any help.

I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.

modytrane

Converting the dates listed in column A into strings indicating the relevant quarter number and year, using the following format:

"Quarter X, YYYY".

Solution:

Use the MONTH, INT, and YEAR functions as shown in the following formula:

="Quarter " &INT((MONTH(A2)+2)/3)&", "&YEAR(A2)

I have the following text

113 results for "Hotel" And would like to extract the number from this text. The number could be anything from 1 to 9999. Once I have extracted this number to a variable I would like to divide by 10 and then round up to the nearest whole number.

For eg using the text above:

VB:Could anyone help with this?x = 113 x / 10 = 11.3 x = 12If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

TIA

I want to perform a calculation that multiplioes the result (4.3). However, Excel seems to use the actual value before rounding. I want the result of 4.3*5 to be 21.5, not 21.3.

How do I get the formula to recognize the result (4.3) instead of the actual number (4.25) in susequent calculations?

Negative. I am using the formula "=ROUND(Q89,1-LEN(INT(Q89))". it does it

mostly fine with postive number except 25 gets rouned to 30.. any way of

keeping 25 at 25.. and also negative numbers don't work. the cell is blank.

not even an error.

I use functions to do this? It is not always 25 though, it could be a one,

two or three digit number. and there could be more than one digit mixed in

with the text that I don't want to include

Example

Cell A1=005482J86

Cell A2=0162483G9

How do i use and if/or logic statement to make a true statement equal "Muni

bond" (cell b1) or "Corp Bond"(cell b2)

I cannot get excel to recognize any logic statement since a cusip has

numbers and text!!

Please help!

Thanks in advance !!

formulas?

I have got the cell formatted to 'show' the number rounded to two decimal

places but the complete root number (shown in the formula bar), is used!

Eg. 1.5642 (1.56 shown) x 4 = 6.2568 (6.25 shown)

I want to multiply 1.56 x 4 = 6.24, without going into each individual cell

and removing the unrequired numbers.

I hope that somebody can help me - even if it is to tell me to stop trying

because it's maybe not possible.

I thank anybody in advance for any constructive response.

bing1080

I use functions to do this? It is not always 25 though, it could be a one,

two or three digit number. and there could be more than one digit mixed in

with the text that I don't want to include

Is there a simple way i can round numbers to the nearest thousand by using

the format cells command.

For example

i would like to display 1,234.56 as 12

I don't want to be diving by 1000 and rounding to zero decimal places

because i still want the whole number displayed in the formula bar when

selecting it.

i use excel 2003

thanx

Mark

Althought a little clunky, this seems to work well if you want to round a large number of cells to the same number of digits while maintaining the underlying precision of the numbers. It requires using a custom number format and using the comma character, which when used correctly divides by 1000.

For example, let's say you want a bunch of numbers in the millions to appear rounded to the nearest thousand. Format all of the underlying cells with the following custom numberformat #,###,",000" exactly as is, including the quotes.

The #,### by itself simply instructs Excel to us commas to seperate each set of three digits. The next "," divides the number by one thousand and the last ",000" adds ,000 as a text string on the end.

If you wanted to the nearest million with a leading dollar sign, simply use $#,###,,",000,000" which includes 2 commas to divide by a million and then adds 6 zeros as a text string.

And if you don't use commas, you could round 12691 to 13000 by using 0,"000" as the customer number format.

I couldn't find this tip anywhere on the net so I thought I would add it here. I hope it helps! I know I will be using it quite a bit...

Nathan

stupid but I agree with the man.

I use Excel to do accounting work. What happens is the numbers add as the

original number and not the rounded number. That can lead to some serious

miscalculations.

Is there a formula that can be used when multiplying or dividing that will

round the number to the nearest hundredth and have the column properly add

the rounded numbers? The way it is right now, it adds the whole number not

the rounded.

I think the IRS might have something to say if the pennies start adding up

to $100s of dollars in missed tax monies.

How do I round a number to the next higher value of a set multiple and the previous lower value of that same set multiple? For example, I have a number 42 and I want to use a multiple of 5. So I want Excel somehow to give me 45 (higher rounding of the multiple) in one cell, and 40 (lower rounding of the multiple) on another cell. Similarly, with say 28 and multiple of 5, I want Excel to give me 30 on one cell and 25 on another cell. And so on.

The reason is I want to then take the higher and lower rounded numbers and lookup corresponding numbers from a chart and do a linear interpolation, which is easy to do if I have the higher and the lower rounding. The chart has values listed on multiples of 5, meaning it lists values for 5, 10, 15, 20 and so on and has corresponding values on the next column. But I need to figure out the corresponding values to numbers in between like 37, 46 etc.

The MROUND function gives only the "nearest" multiple, and does not seem applicable to find both the higher value and the lower value.

If you need any further clarification, please let me know.

Any help will be greatly appreciated.

Thank you.

I was certain there was an option to do this in the format Cells dialog box, but I cannot locate it.

Using Excel 2003.

Here is my current formula:

=IF(K3="SUP",((((C3*0.75)-70000))/10000)+10,IF(K3="WM",(((C3*0.75)-30000)/10000)+6))

I would also like to know if I could set a minimum value for the outcome of the formula.

For example: If the value of the equation was only 7 could a set a min value at 14?

Thanks for all the help!!!

you understand my poor english. My questions are probably easy for most

of you, so I hope someone will help :-)

Problem one.

I am making a spreadsheet where I have to use names not cell numbers

(can not use B1, C3 and so on). I shall use the f(X)=ax3 + bx2 + cx + d

formula (the number 3 and 2 are exponents -if that is what is it called).

I am going to write a start value (lets name the cell Start), a step

value (Step) and a,b,c and d. The last four cells are named a, b, c_ and

d. All this is quite all right but i am going to calculate the formula

in 30 steps where the step value changes the value of the Start value

with the value i wrote (ex if Step is 0,2 the Start value increases with

0,2 each step down the colum - from lets say -4 to pluss 1,8). I have

the start value in a column on the left of the column with the

calculation. Okey so if I could use the cell numbers it would be easy.

But as it is now I have not found a solution. If I could start by using

start+step*0 in the formula and then autoincrease the one number with

1 when copying the formula down the colum i would be saved. But I can

not find a way to do this. Can anyone help ( or suggest another

solution) I would be really pleased.

Problem 2.

I am going to write a program to calculate tax groups. I must use the IF

sentence and the whole calculation is going to be made in the one

formula. The problem is as follows. If the input is, lets say, up to

20000 there is no extra tax (answer is 0), if you earn more than 20000

and up to 30000 there should be calculated 13,5% tax on the value

between 20000 and 30000. If you earn More than 30000 you must pay 19,8%

tax of the sum above 30000 and of course 13,5% of the 10000 between 2000

and 30000. I have the formulas (I think) Taxes=(income-20000)*13,5%

(between 20000 and 30000) and Taxes=10000*13,5% + (income-30000)*19,5%

(above 30000) but I am uncertain on how to use the IF sentence to solve

it all in one fomula (or one cell). Can anyone help.

Example

Cell A1=005482J86

Cell A2=0162483G9

How do i use and if/or logic statement to make a true statement equal "Muni

bond" (cell b1) or "Corp Bond"(cell b2)

I cannot get excel to recognize any logic statement since a cusip has

numbers and text!!

Please help!

Thanks in advance !!

Getting into this VBA but am stuck using msg box to create a new column based on user input

I have a table with column D & E containing variable data, these will be numeric values,

I am trying to create a msg box that will ask" Do you need to change any VLAN numbers" if the answer is yes, it will ask for the number then look in column D and where it finds that number it will put the new user input number into a new column at the end of the table in the same row. If the user input is no then it will juts put the existing numbers in column D into the last column with a header of NEW VLAN and then loop to a new msg box this time asking "Do you want to change any Voice VLAN numbers" and repeat the above against column E putting the new info into the last column in the same row number with the column header changed to New Voice VLAN.

The last thing that would be useful is if the changes could have their cells coloured red.

Thanks for your excellent help in advanced

I have 3 numbers that I have rounded and if they do not equal a certain number, i need 1 added .... the tricky part (for me) is that I need it added in a cell that is already rounded.

attached is an example, but here is the short version:

Number1 Number2 Number3 Total

5.2 6.4 9.4 21.0

5.0 6.0 9.0

nmb 1 rounded nmb 2 rounded nmb 3 rounded

If these 3 numbers do not add up to the total then I need 1 added to one of the numbers. How can I incorporate that into my =round formula?

Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN

Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE?

---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)

I have attached a scaled down version of the Workbook.

Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)

Sub ProposedEmployee02() ' ' Proposed02 Macro ' Macro recorded 1/27/2009 ' Highlights lit blue Range(COL B: COL F) and ' For Range(cells for each workday in each workweek): Highlight cells lt yellow and unlocks for employee input ' Sheets("PROPOSED SCHEDULE").Select Range("B16:F16").Select With Selection.Interior .ColorIndex = 35 .Pattern = xlSolid End With Range("G16:M16").Select With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With Selection.Locked = False Selection.FormulaHidden = False Range("O16:U16").Select With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With Selection.Locked = False Selection.FormulaHidden = False End Sub

Im wondering how to round a number to an integer for calculations.

I have one cell which is using vlookup to find a percentage and multiplying by another value. If i set the cell type to number with no decimal places it displayes the correctly rounded number.

Problem is that, for example the actual value is 0.867, it displays 1, which i want it to but if i try to do a mathmatical calculation on this it uses the 0.867. e.g. 10*<TheCell> = Â£8.67 when i want Â£10

How would I do this?

Thanks much.

I am creating electronic timesheets. When one works overtime, one receives 1.5x the normal rate for that time. This "time-and-a-half" of overtime is not actually paid out as overtime until one has "banked" 40.00 hours of Compensatory Time.

Assume one already has 36.00 Hours of Comp Time banked. One then works 4 hours of overtime. At 1.5x the rate, one will be over the 40.00 hours of allowed Compensatory Time. The following formula resolves this:

=IF(H38+I34<40, I34, IF(H38+I34=40, (40-H38)/1.5, IF(H38+I34>40, (40-H38)/1.5)))

The above formula splits the 4 hours into 2.67 Hours of Comp Time and 1.33 Hours of Overtime that gets paid out. However, 2.67 x 1.5 = 4.005 + 36.00 Hours of Banked Comp Time = 40.005 Hours. One cannot have more than 40.00 Hours, so our auditor requires that the 2.666666 of comp time be rounded down to 2.66 and the 1.33 hours of paid out OT be rounded up to 1.34.

Excel does not technically round numbers. While it visually rounds the number in the cell, the math behind it actually uses the 2.66666666666666666666666 repeating forever in its math.

ACTUAL QUESTION

Is there a way to tell Excel to actually round 2.666666666666 in the one cell down to 2.66, and 1.3333333333333 in another cell up to 1.34???