How to develop an advanced function or formula in MS-Excel

A website visitor asked this question:

I need to develop a formula for the following: Develop a column of 10 consecutive numbers between 10k and 30k. These numbers have to be whole, divisible by 3 but without the digit 7.

We solved the problem in the following manner:

First we generate the 10 numbers using the randbetween() function

The numbers can now be copied, their ‘values’ pasted to another location and they cal also be sorted from smallest to largest to give you the 10 consecutive numbers. This part is easy and is not discussed in the training video

Next we find out whether the generated numbers contain 7 by using a modified version of the find() function so that it displays ‘false’ and ‘true’ as result which can be used in the final formula

Finding whether the generated numbers are divisible by 3 is done using the mod() function.

In the end we use a ‘IF’ function combined with ‘AND’ because we need to make sure that the number that is displayed is divisible by 3 and does not contain the digit 7.

Watch the training video below to learn how to develop an advanced formula or function of the type described above in Microsoft Excel: