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

Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010



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 whetther 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 digi 7.

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




Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010