What is one tool an analyst needs to have to make his or her life easier? the excel array. it is truly a lifesaver! if you want to work with data in excel, you have to learn to use it.
it’s very difficult to describe an array. imagine this…you have 38 cells in Column A and 38 cells in Column B. You want to divide all of the column A by Column B and sum them. You can do this two ways
1. Divide A/B in Column C…add at bottom
2. enter the formula =sum(A1:A38/B1:b38) and hit Ctrl+Shift+Enter to exit out of the cell
Of course, 1 may be easier in this case, especially if you don’t use the mouse. However, this is a simple example of an array
Imagine now that you have descriptor values in column A, column B, and column C, units in Column D and dollars in Column E. You want the sum of units for a unique combination of descriptors. There are 44,000 rows, and 10,000 unique combination of descriptors. by using an array that looks like this:
=sum(if((column A=Descriptor1)*(column B=Descriptor2)*(column c=descriptor3),column4,0)) close with ctrl+shift+enter
you get your answer. what it’s doing is going through each value in column A and comparing it to what you’re looking for, same with columns B and C, then adds the matches in column 4. Very sophisticated!
if you’re an analyst, learn to use arrays. they’ll make you look like a genius to your bosses when you can crunch data very quickly
