Excel tips and tutorials
With the Excel CONVERT function, you can change quantities from one measurement type to another. For example, a hot day in Celsius looks even worse in Fahrenheit! Get my updated workbook for the CONVERT function, with drop down lists for category, units and prefixes.
Continue reading “Excel CONVERT Function With Drop Down Lists”
If a cell contains a full address, what formulas would you use to show the street address, city, state and zip code in separate cells? That was the challenge that I gave to my weekly newsletter readers. A few rows of the sample data are show below, and you can download the “Split Address with Excel Formulas” sample file, to see all the data.
If there’s just one price per product in an Excel lookup table, you could use the INDEX and MATCH functions to to get that price. But what if the price changes occasionally, and your pricing list has multiple dates and prices for each product? How can you do a product price lookup based on invoice date and product name?
It’s not usually as obvious as the error in the screen shot below, but have you ever seen Excel adding things up incorrectly? No wonder some people take out a calculator, to check the totals! Your trusty old calculator wouldn’t tell you that 2+2=5.
I recently learned about The 100-Day Project (#The100DayProject), in which you work on making something every day, for 100 days. Would Excel formulas (#100DaysOfXL) be considered an artistic project? Maybe not, but I had fun working on this SUMIFS formula with empty criteria cells!
With the Excel VLOOKUP function, you can pull data from a specific column in a lookup table. For example, if the lookup table has Product ID, Product Name and Price, you’d use column 3 to get the price. To make your formulas more flexible, and to prevent problems, you can combine VLOOKUP and MATCH.
Continue reading “Combine VLOOKUP and MATCH for Flexible Formula”
Last week, in my Contextures Newsletter, I posted an Excel Name Fix challenge. There was a short list of names, which needed to be fixed. Then we needed to count how many were fixed. Take the challenge yourself – download the workbook to see how you’d solve it.
There is a Microsoft Excel RANK function, so you can calculate where each number stands in a list of numbers. There isn’t a RANKIF function though, if you need to rank based on criteria. Someone asked me for help with ranking daily sales, so I used COUNTIFS to make an Excel Rank If formula workaround.
Recently, someone asked me for help with VLOOKUP problem numbers:
The VLOOKUP formula looks okay, so why doesn’t it work?
To keep track of what I’m eating, I use the LoseIt food tracker app on my iPhone and iPad. It’s easy to use, and creates nice summaries for each day and week. To see how things are going, I’d like to analyze that food tracker data in Excel, instead of being limited to the preset options in LoseIt.
