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

## Formula Friday – Sum Formula Not The Best Solution? Try Subtotal!

Today's Formula Friday will be looking at the Sum Function vs the Subtotal Function. If you want to download the Formuala Friday examples you can so so here Even though SUM is probably one of the most straightforward and most used formula in Excel, it sometimes does not serve so well in certain situations. Ever tried to use SUM in these scenarios? Try to calculate Sum Total when you have hidden or filtered rows Try to calculate Grand Total when you have Sub Totals in your data … [Read more...]

- « Previous Page
- 1
- 2
- 3
- 4
- 5
- …
- 12
- Next Page »