You created a cool array formula and then merged it across several cells. Later, you realize that you need to modify this array formula. When you try to press Control Shift Enter you get this horrible error:(more…)
Despite how amazing Excel is there are times when you’ll find yourself shaking with fear! For example, have you ever seen anything as scary as this =ISERROR(FIND(_xlfn.CONCAT($A2:$E2),_xlfn.CONCAT($I$2:$M$6))) What is xlfn ???(more…)
Although the VLOOKUP versus INDEX/MATCH debate gets most of the attention there are MANY more topics to debate in Excel. When calculating student grades what method is best: NESTED IF or LOOKUP/BINNING TABLE?(more…)
In Excel we can bin numbers. We can also bin letters. Rare but possible. However, be careful when binning a mix of letters and symbols! What could go wrong? A student who earns a B- could end up with an A- grade!(more…)