Hello Excellers, time for some more formula fun, or some fast formula writing fun. Let's look at using one of Excel's features - Auto Correct and put it to another use to speed up Formula Writing. With the Auto Correct feature in Excel, we can specify our own Auto Corrects, essentially we can use shortcuts of text to get Excel to automaticially fill in the beginning of the formula we are wanting to type. For example, let's say we want to type VL and get Excel to auto correct us and type … [Read more...]

## Formula Friday – Calculating The Percent Of Year Completed And Remaining Using The YEARFRAC Function.

Hello Excellers, time for another Formula Friday. Today let's look at calculating the percentage of the year that is completed and that is remaining really easily using the YEARFRAC function in Excel. This figure can be used as a standalone number or calculation for the audience of your report, or used as part of another calculation in your report. Syntax of YEARFRAC YEARFRAC(start_date, end_date, [basis]) Where Start_date this is required. A start date. End_date this is … [Read more...]

## Formula Friday – Why is my formula showing in a cell and not the result??

You may have already come across this problem, and if you have not, you probably will at some point when using Excel. The mysterious case of your formula is showing in the cell not the result of the formula looking like this. Whilst it is really frustrating there are a number of reasons this can happen, and usually I just work my my way through them to resolve the problem. 1. You have show Formulas selected. You have have in error or accidentally hit CTRL+’ (the back quote symbol), … [Read more...]

## Formula Friday – How To Use RANK In Excel But Ignore Zeros

Happy Friday Excellers. Today I want to share with you how to RANK a data set, but ignore zero values. I use RANK a LOT, and sometimes you just do not want to include zero values in your results. If you want to download the Formula Friday examples you can so here- Ranking Ignoring Zeros We actually are going to make use of the IF function as well as RANK in this solution. Here is sample data set I want to rank. I have 10 lines of data and 2 of them are zeros which I do not … [Read more...]

## Formula Friday – Add Leading Zeros To Numbers In Excel Using The TEXT Function

Hello time for some more #FormulaFun. Today we are looking at using the TEXT function to add leading zeros to numbers. In certain situations you may need to have data displayed with leading zeros. For example -customer ID records, or in my case I use store numbers in my databases and I add leading zeros to maintain data consistency for example 00564 and 03453 as the store ID's. I know I will never use these fields in any calculations, (I am never going to add the actual value of the … [Read more...]

- 1
- 2
- 3
- …
- 17
- Next Page »