Free Microsoft Excel 2013 Quick Reference

Rounding numbers and subsequent use

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


I am generating charts from PivotTable reports. The tables take thier data
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

I am trying to use numbers rounded to 1 decimal place in formulas with a constant. The formated cell rounds to 1 decimal place but the cell with the formula uses the entire number in the formated cell

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?

I have a database with over 100 products listed across the first row.
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

Problem:

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)

Hi

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:
	
x = 113 
x / 10 = 11.3 
x = 12 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Could anyone help with this?

TIA

I have a work sheet that has a calcualtion where the result is displayed as a whole number with 1 decimal place. (4.25 becomes 4.3)

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?

I need to round numbers to the nearest ZERO or FIVE. Both Positive and
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.

The text in my cell is 25m3/hour and I want to use 25 in my formula, how do
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

I need to define cusips in excel in an if/nested if statement

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 !!

Can anybody help me to set up Excel so that it uses rounded numbers in
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

The text in my cell is 25m3/hour and I want to use 25 in my formula, how do
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

Simple question (i hope)

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

Just thought I would pass on this tip. After using Excel for over a decade, I've never found a way to round numbers visually, without using formulas or using vba to reformat all of the data and maintaining the precision of the number (perhaps I'm just slow).

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

I saw a post that asked this question and the guy who answered made him look
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.

Hello:
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.

Is it possible to round numbers to the nearest 5 without using a formula or VBA?

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

Using Excel 2003.

So I created a simple math equation and I need it to round up to a whole number. I was able to get it to round up from .5 and beyond in the number settings, but would like it to round up for anything above a whole number (like 5.1 goes to 6). is there an easy way to do this?

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!!!

Hello. I am new to this group and will try to explain two problems. Hope
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.

I need to define cusips in excel in an if/nested if statement

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 !!

Hi All
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 a massive spreadsheet to manupulate. Long story short ...
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?

I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.

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


Hi all, im new to these forums.

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.

BACKGROUND & CONTEXT

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???