Free Microsoft Excel 2013 Quick Reference

Decimal places in formulas Results

I have a formula in a cell that, when calculated, returns a number with 2 decimal places. I want it to round down to the integer and so i have not used the formatting where I can choose to have no decimal places incase it rounds it up.
How do I insert the ROUNDDOWN function.
e.g. my formula is
=E10/((E8-G9)/2)

this may give me a 2 decimal place answer. I only want a whole number, rounded down.
I have put in the ROUNDDOWN function using the insert function buttons but it adds the rounded down figure to the original result, thus doubling the answer. I don't want this, I only want the rounded down figure to appear. Can it be done in one cell?

Hello: I am not a VBA programmer but I have gathered enough info to accomplish most of what I set out to, Much thanks to this forum. I am still having an issue with one item. When I try to copy a value from one worksheet to another it is rounding off the number to 2 decimal places rather than 4. eg shows up on sheet 3 as $13.4100 rather than $13.4135.
Both cells are formatted to display currency to 4 decimal places.
The value being copied from sheet one is calculated using a formula
=IF(F31>0,M31/L31,IF(AND(G31>0,E31>0),K30,IF(AND(G31>0,E31=0),M31/L31,"")))

and the code used to transfer the info is:
Private Sub CommandButton2_Click() 'Tax Form Button - captures all sales for year

Sheet3.Activate

ActiveSheet.Unprotect
   

Dim lastrow3 As Long
lastrow3 = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row

If Cells(4, "A") = "" Then
Else
Sheets("Sheet3").Range(Cells(4, "A"), Cells(lastrow3, "H")).Select
Selection = ""

Sheets("Sheet3").Range(Cells(lastrow3, "B"), Cells(lastrow3, "H")).Select
    
     With Selection.Borders(xlEdgeTop)
        .LineStyle = none
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = none
    End With
End If



Sheet1.Activate
Dim i As Integer, rng As Range
Dim lastrow As Long, nextrow As Long
Dim Title As String
Title = ("MY WESTJET SHARES - TAX REPORT FOR " & TextBox1.Text)
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
nextrow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row + 1

Set rng = Sheets("Sheet1").Range("T4:T" & lastrow)

    For i = 4 To lastrow
    
            If Cells(i, "T").Value = TextBox1.Text And Cells(i, "C").Value = "Sell" Then
            
            'Range(Cells(i, "A"), Cells(i, "L")).Copy
Destination:=Sheets("Sheet3").Cells(nextrow, "A")
            
            Sheets("Sheet3").Cells(nextrow, "A").Value = Range(Cells(i, "A"), Cells(i,
"A")).Value 'Date
            Sheets("Sheet3").Cells(nextrow, "B").Value = Range(Cells(i, "E"), Cells(i,
"E")).Value 'Share Price
            Sheets("Sheet3").Cells(nextrow, "C").Value = Range(Cells(i, "G"), Cells(i,
"G")).Value '#Shares Sold
            Sheets("Sheet3").Cells(nextrow, "D").Value = Range(Cells(i, "I"), Cells(i,
"I")).Value 'Price Sold For
            Sheets("Sheet3").Cells(nextrow, "E").Value = Range(Cells(i, "K"), Cells(i,
"K")).Value 'ACB/Share
            Sheets("Sheet3").Cells(nextrow, "G").Value = Range(Cells(i, "J"), Cells(i,
"J")).Value 'Capital Gain/Loss
            Sheets("Sheet3").Cells(nextrow, "H").Value = Range(Cells(i, "D"), Cells(i,
"D")).Value 'Sales Fee
            Sheets("Sheet3").Cells(nextrow, "I").Value = Range(Cells(i, "P"), Cells(i,
"P")).Value 'Date 1st
            Sheets("Sheet3").Cells(nextrow, "J").Value = Range(Cells(i, "Q"), Cells(i,
"Q")).Value 'Date last
            
            'Sheets("Sheet3").Cells(nextrow, "I").Value = Range(Cells(i, "I"), Cells(i,
"L")).Value
            'Sheets("Sheet3").Cells(nextrow, "J").Value = Range(Cells(i, "J"), Cells(i,
"L")).Value
            'Sheets("Sheet3").Cells(nextrow, "K").Value = Range(Cells(i, "K"), Cells(i,
"L")).Value
            
            'Sheets("Sheet3").Cells(nextrow, "A").Resize(1, 12).Value = Range(Cells(i, "A"),
Cells(i, "L")).Value
            
           
            
            nextrow = nextrow + 1
        End If
    Next i

Sheet3.Activate


    
    Dim EndRow As Long
    EndRow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row
    Cells(EndRow + 1, 1).Value = "Totals"
    
    
    Dim r As Long

r = Cells(Rows.Count, "B").End(xlUp).Row

Range("F2").Copy
Range("F2").AutoFill Destination:=Range("F2", ("F4:F" & EndRow))


Cells(3, "F").Value = "Adj. Cost Base"
    
    
    Cells(EndRow + 1, 3).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 4).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 6).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 7).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 8).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(1, 5).Value = Title
    
    'Range("A" & Rows.Count).End(xlUp).Select
    Sheets("Sheet3").Range(Cells(nextrow, "B"), Cells(nextrow, "H")).Select
    
     With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = xlAutomatic
        .Weight = xlThick
    End With

Range("A" & Rows.Count).End(xlUp).Select 'Selects the last cell in column A (used to deselect previous
selection)
'Unload Me

   
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

REPORTS.CommandButton3.Visible = True

End Sub
Sheets("Sheet3").Cells(nextrow, "E").Value = Range(Cells(i, "K"), Cells(i, "K")).Value 'ACB/Share This is the line giving me issues.

On sheet 1 the value is displayed to 4 decimal points but on sheet 3 there are 4 decimal places but the last two are both zero's
Any Ideas on why this is happening

Hi. I have looked everywhere and cannot find anyone asking this question. I made a table where just about every number in there is a formula result. ei: column a lists one value, column b multiplies that value by a percent, column c adds those values, and column d subtracts a static value from column c.

This goes on for several hundred rows, so I want a vlookup to find me the row number where column d approaches 0. I've tried everything, but I can't figure it out. No matter what I do it produces N/A. Even If I tell vlookup to search for the number displayed to the second decimal place, it won't find it. And no, I'm not telling it to look for an exact match either. I put in 1,2,10,100, or 1000 as the last variable in the formula, and still nothing.

Any thoughts?

Hi Guys, I wanted to know how I could calculate how many weeks have elapsed between two dates.

I have a start date and end date in Date format and then I use the formula NETWORKDAYS(Start Date,End Date)/5), formatted to 2 decimal places this tells how many weeks have elapsed between the two dates.

Now I wanted to do the reverse, So basically I would have a Start Date, then the number of weeks and from this calculate the End Date using working days only.

Is this possible?

Thanks in advance for any help given.

Is there a formula that counts all the numbers in between 1 and 2, up to two decimal places, but only shows the two numbers - 1 and 2 in the cell??

I need to create a vlookup formula that looks at the increase in price which decreases demand, example id price increase between 5% and 10% demand decreases by 8%

Hi
This is a bit of a stange one. I have some formula in a spreadsheet and the results are currently showing with 2 decimal places e.g. 0.75, 1.25, 5.60. However I would like this to display without the decimal places e.g. 75, 125, 560 without the need to include *100 in the formula. Unfortunately it is not a percentage so I cannot use that option as it then displays with the percentage symbol.
Anyone any ideas?

Cell A1 contains a formula that returns a number...(ex. 211.56777777)

Cell B1 contains the following formula:
=CONCATENATE("Your payment is $",A1,"and is due in 15 days.")

As the formula stands now the reslult is:
Your Payment is $211.56777777 and is due in 15 days.

I need it to say:
Your Payment is $211.56 and is due in 15 days.

What do I need to add to the formula so that it removes all characters two places after the "."

The reason that you need to ROUND for this operation (as suggested by
Aladin) is that computers do not have infinite precision, and some
fractions cannot be exactly represented without it.

For example, consider a hypothetical computer that carries only 4
significant figures. Then 4/3 would evaluate to 1.333 and 1/3 would
evaluate to 0.3333. Because of finite precision, 4/3-1 would then
evaluate to 0.333 which is not exactly the same as what 1/3 evaluates to.

1/24 = 1/8*1/3, so you should expect finite precision effects. The same
thing can happen where you might not expect it since computers do math
in binary rather than decimal, so numbers involving 1/5 (and hence 1/10)
will be non-terminating binary fractions (that must be approximated)
even though they are terminating decimal fractions.

If you want to learn more about this, there are several Knowledge Base
articles on the subject

http://support.microsoft.com/kb/48606
Comparison of Values Does Not Return Correct Result

http://support.microsoft.com/kb/42980
(Complete) Tutorial to Understand IEEE Floating-Point Errors

http://support.microsoft.com/kb/165373
OFF97: Rounding Errors in Visual Basic For Applications

http://support.microsoft.com/kb/69333
How To Work Around Floating-Point Accuracy/Comparison Problems

http://support.microsoft.com/kb/214118
How to correct rounding errors in floating-point arithmetic

http://support.microsoft.com/kb/78113
Floating-point arithmetic may give inaccurate results in Excel

Jerry

Biff wrote:

> Hi Folks!
>
> Here's another rounding issue I just discovered:
>
> A2 = 9:00 PM (not calculated, manually entered)
> B2 = 10:00 PM (not calculated, manually entered)
> C2 formula: =B2-A2+(B2<A2) Formatted as h:mm returns as expected 1:00
> D2 formula: =IF(C2<1/24,1/24-C2,0)
>
> Without preformatting cell D2 it defaults to GENERAL and unexpectedly
> returns 3.46945E-17 which is the value_if_true argument of the IF function.
>
> When formatted as h:mm it returns 0:00.
>
> If cell D2 is formatted as h:mm and is tested to be = 0 FAILURE!
>
> Here's how it breaks down:
>
> =IF(0.0416666666666666<0.0416666666666667,1/24-C2,0)
>
> =(B2-A2+(B2<A2))*24 returns 1 as expected. However, if formatted as number
> to 15 decimal places returns 0.999999999999999. So, if that formula was
> tested to return = 1 it would also fail.
>
> But........ change:
>
> A2 = 11:00 AM
> B2 = 12:00 PM
>
> (not drag filled either!) D2 returns 0 as expected and when tested for = 0
> passes.
>
> Hmmmm!
>
> Just be aware!
>
> Biff
>
>
>
>
>

As Ron pointed out, there is no sigfig format, but if you will be
handling a lot of numbers, you may prefer a formula to round to
specified number of figures

http://groups.google.com/group/micro...244c8f41e91025

to the Round function that rounds to a specified number of decimal places.

Jerry

Daniel Bonallack wrote:

> I had a search as I was sure that this would have been answered, but didn't
> really find what I needed.
>
> How can I display 3,456 as 3,460 (so to 3 significant digits) without
> changing the value of the cell (as would happen with the round function).
>
> Thanks in advance.
>
> Daniel

First off, thanks in advance for any light you might shed on this for me.

Is it possible to have text justified, based on conditional formatting?

I'm dealing with a large set of 6 digit numbers (XXXXXX). Some of them have subsets, represented by 2 decimal places, so they would be XXXXXX.XX. I want all of the subset numbers to be right justified so that it will emphasise that they are part of a subset; is this possible?

Also, is there a way to custom format these numbers so they will either appear as XXXXXX or XXXXXX.XX; trying to do this in the custom format method will at best yield results of:

123456. or 000101. (dots at end of number)

or

123456.78 000101.10

I would want the 123456. and the 000101. to appear without a decimal point if it doesn't have a subset belonging to it.

I figure it would be in conditional formatting, something like this:

A formula in conditional formatting such as:

=roundup(a2,0)-rounddown(a2,0)=1

this will yield a 1 value for any number I might be working with that would contain any fraction type number.

What I would like to know is what, if any exists, (VBA) code I can supplement this to make it a right justify, and also format the cell from custom number format XXXXXX to XXXXXX.??

Hello:

If you have a number in the cell such as "100" or "25000" or "49" and you
want to place a decimal point two places from the right so that the numbers
read as "1.00" or "250.00" or ".49" respectively, then what Excel formula
would you use?

I tried =LEFT(P2)&"."&RIGHT(P2,2) but the problem with that is that it made
"25000" into "2.00". That's not what I wanted. I wanted "250.00".

Thanks!

childofthe1980s

I have a report that lists individuals, dates, quantities, dollar amounts and a description. There are multiple entries for each individual and both the amounts and descriptions are also repetative so vlookup is a no go. What I'm trying to do is isolate the entries for each individual on their own sheet. What I have currently works but is tedious. On receipt of the report, I insert rows at the start of each new person so that they are assigned to a specific location. On the individual's sheet, I have a formula that duplicates the data from the assigned rows. In addition, the report flucuates in length and number of entries on a weekly basis.

I considered the IF function but again, I run into location problems as the report is a approximately 500 rows which means an IF formula to examine every entry and the person starting at row 451, will have the data that far down the on their sheet and I would have to delete the other 450 rows which is even more tedious that what I'm doing now.

Any ideas?

One other unrelated issue with this is with vlookup (as usual). I have a formula that divides amount by quantity. The result of the division is compared to a rate chart and returns a second value. This works fine for the most part but occasionally, I get an #N/A return and I don't know why. Example, $45.54 / 99 = 0.46 with no additional decimal places. The vlookp should return 0.38 when it finds 0.46 but gives me an #N/A instead. Oddly, this does not happen consistently as sometimes it works. Using this example, if I change the $45.54 to $45.55, I then get the correct answer of 0.38 which has me scratching my head.

Thanks in advance.

Hello Again,

Same spreadsheet as yesterday's question. I'm converting metric to British values and have no problem with fixing the decimals when displaying the number. However, I'm wanting to CONCATENATE the unit of measure to the conversion and end up with 12 places beyond the decimal.
Entered formula is:
=CONCATENATE(CONVERT(T5,"cm","in")," in")
Results in 159.055118110236 in

But = CONVERT(T5,"cm","in")
Results in 159.1 which is what I want.

Any ideas?

I am making a spreadsheet and would like the "placement" tab to look at the "years in position" column and go to another tab and find the corresponding data then put that information into the cell that the formula is in. I have concatenated and the value carries out to about 6 decimal and I cant change the formating. I would like it to display as a currency with 2 decimal places.

If you look at the placement tab cell H7 you can see what I am trying to do.

If you change the value in d7 to a value that is on tab 06-09 step plan (b9-b23) then the value is put in other wise noting is returned.

Please let me know if this is the best way to do this, and if not how should I go about it. Otherwise any formatting tips would be appreciated.

Thanks

Hi

Can some of you try this simple sum to see if you get the same problem as I do.

65,223,514.63
61,419,687.83
3,803,826.80

Now in a seperate cell using the simple formula (= A1-A2-A3) subtract the 2 smaller amounts from the larger one.

You will now need to format the sum cell to "number" and increas the number of decimal places places to see the figure 0.000000004656612873077390000000

Now if anyone can explain to me why Excel does this I would be most grateful. Incidently if I increase all 4 cells to display the maximum number of decimal places none of the figures involved go beyond 2 decimal places (except the sum one of course which continues to "add" in its own)

Thanks

Dan

I can't quite figure out why this is occuring.

I have a simple formula that is behaving oddly.

the formula executes simple division and then adds a label.

As follows,
Problem is, even though I have the cell format set to "number" with "2" decimal places I still get 16
decimal places!

You can see the result in the attached file.

Funny thing is, when i remove the label the resultant reverts back to the proper format!

???

Anybody know of a way to add the label without blowing the format?

How do I enter times in excel without the program converting the time to a date? This is so incredibly frustrating I almost punched my monitor.

I am trying to enter race car lap times. When I enter mm:ss.000, it wont display correctly. I have tried all of the number and time formats that are offered in the format cells/number tab.

I can get it to display my time as I want, but it won't give me three decimal places. Then, if I look up in the formula bar, there is a clock time format in there. In otherwords, I enter "00:48.324" (mm:ss.000), and in the formula bar it shows me 12:00:48 AM. So when I go to edit the .324 part, I cannot. Its not even there. If I just convert to show tyhe date serial number, it makes it a bit tough to edit times.

Can anyone tell me what setting or format I need to enter or change? I would almost be happier to display it as text just to escape the automatic conversion to date serial.

UHG.

Thanks

Speedracer

Hi

I'm sorry if this is really simple but I can't see how to do this.

All columns referred to are monetary values.

I have a column (Column Z) which is the total of 3 other columns (T, U and W) so the formula in Z is =T1+U1+W1.

Columns T, U and W are also formulated in there own way (e.g. result of a lookup table or a simple calculation)

Column Z produces a result and is then formatted to 2 decimal places.

However, when I total Column Z the result is always 1 or 2 pence out because it is actually calculating the results of the formulas and not the result of the displayed amount.

So the question is - how can I get the sum total of Column Z to calculate the displayed amount and not the amount as calculated by the formula?

Hmm - does that make sense?

Cheers anyway

Matt

Alright, I am making a decimal/binary/hex/octal convertor.
I have cell E3 set as a drop down box where you can pick from Decimal, Hexadecimal, Binary, and Octal. This is the type of number you will enter.
And then I have Cell E4 where you enter your own number.

Then I am going to have places for the results. I am trying to use LOOKUP to see what E3 is and then convert whatever the number in E4 is to the other types. So here is the code I am using to convert it to octal:

=LOOKUP(E3, {"Decimal","=DEC2OCT(E4)";"Hexadecimal","=HEX2OCT(E4)";"Binary","=BIN2OCT(E4)";"Octal","E4"})

The problem is that I can only get it to show =DEC2OCT(E4),E4,etc not the actual answer to it or the cell....is there anyway I can get it to show the answer?

Guys, I am brand new here and with a mere average knowledge of excel and I originally had a huge formula with multiple nested statements, but because Excel cannot nest more than 7, I needed to come up with a workaround. The formula below is ideally what I required:


Now I was able to get some fantastic help on this to simplify to the following formula which is in fact working perfectly:

The problem is that I am having difficulty understanding in layman's terms what the formula actually means and although its
working perfectly, I am not confident with it and I really need to know the workings behind it. Unfortunately the person who
assisted me with this so far is not available at the moment, so I'm throwing it out to you guys to see if anyone can shed any
light on it. I would be so so grateful if someone could guide me on it. 

My interpretation of it so far:

Vlookup the value in the table, when choosing column to bring back do not look past column 10, do not look before column 2, instead look for column 2 + rounded up figure (0 decimal places) for the max. of ..... here's where I hit a brick wall. And I can't seem to figure out the -23 and the /12 parts.

Also I'm wondering if I could use the Roundup formula as opposed to the Ceiling one?

Big thanks in advance for any assistance or feedback you can give.


No luck finding an answer? You could always try Google.