Jump to content


Applying an advanced filter with function driven criteria

MOD filter advanced filter

  • Please log in to reply
2 replies to this topic

#1 Pandasia



  • Members
  • Pip
  • 2 posts

Posted 10 November 2016 - 09:39 PM

Hi Everyone,


I'm loving this course and really getting on well with it; however ...


In Expert Skills Session 1 Lesson 7 we learnt how to do an inventory audit on 20%


I've had a go to see if I can do this on my own - my format is right but instead of getting every 5 - 5, 10, 15, 20 etc

I get 6, 11, 16, 21, 26, 31 etc. I can't work out what I've done wrong. 



#2 Jonathan


    Forums Administrator

  • Administrators
  • 887 posts

Posted 11 November 2016 - 02:18 PM

Hi Pandasia,


If you're following the same steps as in Expert Skills Lesson 1-7, you'll be using the following formula:




This should work if you have placed ascending numbers in column A, starting in cell A7. My guess is that your numbers may start in cell A6 or A8, which would explain why you're not getting the correct results.


I hope this helps, but please feel free to reply if you're still having problems. If you'd like to attach a copy of your workbook I should be able to find the problem (instructions here).

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

#3 Pandasia



  • Members
  • Pip
  • 2 posts

Posted 12 November 2016 - 10:54 AM

Hi Jonathan,


Thank you for your reply.


I've been having fun (really) fiddling around with this formula.


I find if MOD starts in A6 - returns #VALUE! - as this is the Header row!

- so the cell isn't recognised as a Value.


.... A5 returns 7, 12, 17, 22 etc


.... A7 returns 5, 10, 15, 20 as it should


and A8 returns 4, 9,14, 19, 24


Seems I've cracked it now, thank you.


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users