|
|
One of the best features of Excel is that it's quick and easy to sort columns of data. You can even sort data in an Excel row, left to right, by changing one of the sort options. Sort Multiple Rows of Data With a Formula In a comment on the Sort a Row in Excel 2010 blog post, Debbie asked about sorting 2000 rows, left to right. She didn't say they were lottery numbers, but her example, shown below, sure looks like that to me. One way to sort the rows is to use a formula, in columns to the right. In the screen shot below, cells H1:M1 are selected, and this SMALL formula is entered: =SMALL(A1:F1,{1,2,3,4,5,6}) Then, to array-enter the formula, press Ctrl+Shift+Enter Then, copy the formula down to the last row of numbers, to see all the rows in ascending
Continue reading Sort Lottery Number Rows in Excel
There are Excel formulas and User Defined Functions (UDF) that can change numbers into words. Those are handy if you're typing a number into a workbook, and want the written amount to be shown, as it might appear in a cheque. Have you ever tried to do the opposite – change words into numbers? It's a tricky process, and Excel MVP, Jerry Latham, has created an Excel UDF – WordsToDigits – to help you out. Words to Numbers With the WordsToDigits UDF, you can create a worksheet formula to translate words into their numeric value. For example: Five hundred fifty-two becomes 552 One dollar and 27 cents becomes 1.27 In the first example, the words are in cell B2, and the formula in cell C2 is: =WordsToDigits(B2) You can format the formula results in any number format you want, such as general numbers, numbers with thousands
Continue reading Words to Numbers in Excel
Mock me if you will, but I use Excel to plan the timing for our holiday dinners. Monday was our Canadian Thanksgiving, so it was the perfect occasion to dig the planner out again. You know that it's a delicate juggling act, trying to get everything cooked and on the table at the same time. Then, halfway through dinner you realize that the dinner rolls are still in the oven. Oops! To prevent the senseless loss of dinner rolls, and help things go smoothly, I use my Excel Holiday Dinner Planner. Yes, it takes a few minutes to set up, by entering all the dinner items, and the preparation steps, but it's time well invested! If you're like me, and don't vary the holiday menu too much, you can reuse the worksheet, for every holiday. Calculate the Start Time Once the sheet is set up, you simply select the
Continue reading Plan Your Holiday Dinner in Excel
My son is in an Air Traffic Control course, and there's lots of information to memorize. Directions have to be given in a very specific sequence, or the pilots don't respond. Apparently, you can't say, "Hey dude, just put it down anywhere." No, you have to address the aircraft correctly, and specify an apron and refer to a valid destination. Or something like that! Create Practice Scenarios Anyway, to help himself memorize all this information, my son asked if Excel could create random scenarios, with an aircraft, apron and destination. Well, of course Excel can do that! Even if you're not preparing to direct giant airplanes in and out of the sky, you might find some uses for this workbook. For example, you could change the options to Protein, Side Dish 1, and Side Dish 2. Then, select tonight's dinner items with a single click. Hmmm...What other types
Continue reading Create Random Scenarios in Excel
Easter has passed for this year, but it's never too early to figure out when Easter will occur next year. Then, you can book your vacation for that date, and be out of town when the family shows up for Easter dinner! Last week, Excel MVP Jerry Latham showed us how to calculate distance in Excel, based on starting and ending points. Today, Jerry explains how you can calculate Easter dates in Excel, using a worksheet formula or Excel User Defined Function (UDF). Easter Formulas First, Jerry gives a few examples of Excel worksheet formulas for Easter dates, where the year is entered in cell A1. This formula, by Norbert Hetterich, works if the regional settings have a day/month/year date format. =FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34 And here's another Easter formula, by Thomas Jansen: =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 However, the worksheet formulas can't be used reliably to determine dates of Easter after 2203, and the
Continue reading Excel Easter Date Calculations
How do you calculate distance? In the small town where I grew up, distance was measured in blocks or travel time. For example, my school was about 5 blocks away (much further in the winter!) and my grandparents lived 5 minutes from our house – or 6 minutes during rush hour. Sometimes you need more precise measurements, and Excel MVP, Jerry Latham, has an Excel user defined function that will help you. It's designed to calculate accurate measurements, based on the longitude and latitude of your start and end points. Why a user defined function? Unfortunately, an Excel worksheet formula isn't accurate enough, if you need precise distances. Jerry used to work in air traffic control, and he explains the problem with "almost" accurate worksheet formulas: Typically they are short by some number of meters, typically about 20 to 30 feet per statute mile, and after flying just
Continue reading Calculate Distance in Excel
Last year, I showed that you could combine text in Excel by using the ampersand (&) operator, instead of the CONCATENATE function. That makes it much easier for those of us who are lazy, or can't remember how to spell concatenate. There was a combine text video too, and I've it added to the bottom of this post, so you can see it again. It's more interesting than most of the new programs that are starting on television this season! Formula is Showing After watching that video on YouTube, someone asked why his worksheet was showing those combine text formulas, instead of the results. There are two likely causes for this, described below. And the good news is that it's easy to fix both! Show Formulas Excel has a feature that lets you switch between seeing the formulas, and formula results, by using the keyboard shortcut: Ctrl +'
Continue reading Excel Formulas Show in Cell
Recently, I saw this tweet, from someone stuck on an Excel problem:
An Excel problem I have never solved – easily copying a formula down tens of thousands of rows where the row next to it may be blank.
Yes, it's easy to copy a formula down a column, if the adjacent column is filled. Follow these steps to copy the formula down to the first blank cell in the adjacent column.
Select the cell with the formula Point to the fill handle, at the bottom right of the selected cell When the pointer changes to a black plus sigh, double-click the fill handle
Fill Down with Empty Adjacent Column
However, if the adjacent column is empty, this fill handle trick doesn't work. How can you quickly enter formulas in a column, if you're setting up a workbook, with lots of empty cells?
Here's how I do it –
Continue reading Quickly Copy Excel Formula Down
You spent hours creating an impressive table of loan payment calculations. Different loan amounts are across the top of the table, and a variety of terms and interest rates are at the left side. At a glance, you can see the monthly payment for any combination of variables. Sweet!
Then, your boss breaks your magical spell of awesomeness, by asking you to include the total payments for each combination. Sure, you could copy that sheet, and tweak the formulas, or add more columns, but then the workbook is
double the size, and twice the maintenance. Use a CheckBox
Thanks to Dave Peterson, there's a new tutorial and sample file on the Contextures website – Excel Formula CheckBox. Instead of duplicating your work, and creating multiple sheets, you can solve the problem with a simple checkbox.
A checkbox at the top of the worksheet is linked to cell C1. If
Continue reading Change Excel Formula Results With CheckBox
Can you afford that new car? Or maybe you loaned money to one of your kids, and you want to calculate a repayment schedule. (Oh yes, they will stick to the plan, without fail.)
To help you figure out the payment amounts, here is a nifty Excel loan payment calculator. (The kids will think you're cool when you say "nifty".)
It uses the PMT function to calculate the payment amount, and you can enter the variables:
Loan Amount Payment Frequency Term (Years) Annual Interest Rate How the Loan Payment Calculator Works
Of course, if you are the Bank of Dad, you might offer a lower interest rate, so you can adjust any, or all, or the green cells.
Type in any cell, except Payment Frequency, where you can select from a drop down list of options.
The Lists sheet has a lookup table of frequencies and number
Continue reading Excel Loan Payment Calculator
|
|
Recent Comments