I’ve always thought this kind of funny: even people who are “bad at math” tend to have a good handle on order of operations. You know: do the exponents first, then multiplication and division, and then addition and subtraction.
It turns out that Excel violates that standard in one way. Like all things with computers though, our lack of clarity about what we’re doing is part of the problem: GIGO.
So, consider the following:
You and I look at this and say: “three minus five equals minus two”. That’s correct, but it misses an important subtlety: the two occurrences of minus in that phrase are homophones — they sound the same but have different meanings!
A better way to state that equation would be “three subtract five equals negative two”. That’s correct because subtraction requires you to know both numbers, while negation only requires you to use one.
So, what does Excel do? In order of operations, most people lump negation together with subtraction. Excel does not: it does negation before even exponents.
This can very easily make a difference in your Excel calculations. Consider this
If you evaluated this by hand, you’d do the exponent before the minus, like so:
-3^2 = -(3^2) = –9
But, not Excel. Excel says that it makes a difference whether that minus is used for subtraction or negation: subtraction would go after exponentiation, but negation would go before exponentiation. So, Excel does it this way:
-3^2 = (-3)^2 = 9
Wow. There’s got to be a world of hurt in the business world from that one.
Do you ever need to sort text without changing the original range? You know: to have an original unsorted range and a sorted one that works in parallel?
This is a variation on the method using =index(), =match(), and =small() that can be used for numbers found in the Chapter 4 of Powell and Baker’s text. The thing is, those are functions that operate on numbers, not text.*
The trick is to use =countif(). This works with numbers or text, and can allow you to count the number of entries in a range that fall alphabetically before the cell that you’re in.
So if you have a range of unsorted text in A1.A5, you can create a list of numbers giving their rank order in B1.B5 by entering this formula in B1 and copying it down the list:
The first argument is telling =countif() to work on that fixed range of text you want to sort.
The second argument uses the ampersand as a text concatenator to combine less than or equal to with the contents of the first cell in your range.
Your result will look something like this:
Now you can use those ranks to put your sorted text anywhere you like. The site I linked above advocates using =vlookup(), but I use =offset() too.
* I never thought that section was very useful. Then I talked to MBA students who have jobs where they have ranges they need to sort in spreadsheets they’re not supposed to goof up (thanks MU!). Now that they’ve opened up my mind a bit, I have found a few cases where I’ve been sorting and desorting that could be improved by creating a parallel sorted range.
See how the choice of “Jams,Preserves” at the top right got the two rows corresponding to that choice to highlight!
These directions from Susan Harkins at TechRepublic are a bit involved, but not hard. First, from your list of non-unique entries, extract a list of unique ones. Second, turn that into a drop-down menu. Third, set up conditional validation for a broader range based on that menu choice.
With the kickstand out, Type Cover keyboard deployed, and Word fired up, the Surface is an absolute joy to use. It's a truly, almost unfathomably good computing experience. I instantly went from "meh, what's the point of this thing?" to "OMG - this is the best device ever!"
That’s interesting … there’s a lot of people who’s biggest problem with tablets is the difficulty of getting their new ideas into it … since everyone agrees that the iPad is a great way to get older ideas out.
With the development of internet technology, work at home jobs are increasing in the market. Also setting up small business online with ones own bank savings can provide excellent work at home opportunities. Apart from savings, banks offer0 credit card to cater to short term finance needs. Partial tax payments like tax credits are also available to promote online businesses. Market now offers several alternatives to traditional credit card debt which are helpful to work at home businesses.