Free Microsoft Excel 2013 Quick Reference

- Solved - rounddown function
- Rounding error when copying value from one sheet to another
- Vlookup of calculated table
- Calculate weeks between two dates (working Days)
- Counting between numbers
- avoiding multiplication
- Removing digits after decimal in "text" numbers
- Reason Another rounding issue
- Signifcant digits:Ron printout
- Conditional Formatting and text Justify
- Inserting decimal two places from the right
- Isolating Data
- Fixied Decimals with CONCATENATE
- Concatenate formatting tips
- Excel adding values to a simple sum
- A formatting quandry...
- Stopwatch time frustrations
- Sum Column as Displayed not As Calculated
- Need to know how to make functions/formula work in LOOKUP...
- Workaround for nesting more than 7 statements

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?

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 SubSheets("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

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?

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.

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%

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 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 "."

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

>

>

>

>

>

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

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

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

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?

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

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

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

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

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?

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.