A common Excel task is to categorize values into bands. You can use Frequency or some pivot table capabilities to report on that, but lets say that you want to add a categorization characteristic to individual rows but want to avoid complex IF's and array formulas. Here's how Let's say that you have in A1 an age, and you want to categorize into 0-10,10-20, above 20. This formula will do it for you. =IFERROR(CHOOSE(MATCH(TRUE,{10,20,10000}>a1,0)," 0-10","10-20"," over 20"),"-") Walkthrough
For more tips like this see Get Started Snippets. You want to learn Google Apps Script?Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'Reilly, Amazon and all good bookshops. You can also read a preview on O'Reilly If you prefer Video style learning I also have two courses available. also published by O'Reilly. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Quirky Functions >