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

## Formula Friday – Randomly generate words in Excel with INDEX and RANDBETWEEN.

Happy Formula Friday. If you want to download the example Formula Friday workbook then you can do so here. I like generating random data a lot of the time to test out work sheet solutions. I have been asked by one of my subscribers to show how to generate some random names from a list of names that are contained in a data set or table. We can do this with ease in Excel, by using both the INDEX and RANDBETWEEN functions together. What are we waiting for???. Here is a same list of … [Read more...]

- « Previous Page
- 1
- 2
- 3
- 4
- …
- 32
- Next Page »