Array Formulas in Excel
Hello all. So sorry I haven't been in touch over the past week. I've been extremely busy planning for the wedding and taking care of other details. Anyway, I wanted to pass along some great information (at least, great to me): I've been included in an article on accounting blogs! You might find it useful as many other accounting blogs are on there such as tax, management accounting, etc.
Anyway, on to today's blog. A few weeks back, a friend of mine had asked about using the SUMPRODUCT function, and I recommended that he use array formulas. In the comments to that blog, I briefly discussed array formulas and I'd like to expand on their usage today. Essentially, you will be interested in using array formulas (CSE's for "Ctrl-Shift-Enter" formulas) when you need to perform extremely complicated formulas on numerous cells/rows and then have a summary function to tie it all together. For example, this blog discussed the SUMIF and COUNTIF functions. So you know how to use them, but what if you wanted an "AVERAGEIF"? No such function exists in Excel, but CSE's will help us remedy this. The best way I can describe starting CSE's is by the summary function you want to utilize. In our example, we want to average values if a certain criteria is met. So our formula begins:
=AVERAGE(
The next step is to define the criteria. Let's assume we're interested, in averaging sales by state. Now realize that this is a somewhat contrived example. The easiest way to average across states is to do "=SUMIF(State_Col, "PA", Sales_Col)/COUNTIF(State_Col, "PA")". That is, sum the sales by state and then divide by the number of entries identified by COUNTIF. But, it will help us get started. So, to limit on state, we need an "IF" statement to limit our records. So, we simply add the if:
=AVERAGE(IF(
Now it gets a little complicated. Essentially, the next part of the formula will define whether or not the number should be included in the CSE. We want to examine each cell in the range of state names. Lets assume that the states are in cells A1:A10 and the corresponding sales figures are in cells B1:B10. The "IF" part of the formula wants to know what to examine, so we enter the state cells:
=AVERAGE(IF(A1:A10
Since we want to see if it's equal to a particular value (Pennsylvania, for example), we just enter that:
=AVERAGE(IF(A1:10="PA",
The comma is added so that we know what we want to include as the value. We want the sales figures, so we'll add:
=AVERAGE(IF(A1:A10="PA", B1:B10))
It's that simple so far. The last step (after you add the braces as shown above) is to hit "Ctrl-Shift-Enter" to complete the formula. If you don't do the last step, the formula will not work properly. But this will average sales for Pennsylvania as we wished. You may be wondering, what if I wanted to sum New York or Pennsylvania? To do that, you have to modify the "IF" statement. You'll have to add:
=AVERAGE(IF((A1:A10="PA")+(A1:A10="NY"),B1:B10))
You'll notice that I added another set of parentheticals and a "+" (plus) sign. This says "If the state is PA OR if the state is NY, show the sales figure". You'll have to consult the help files to find out why we use a plus sign, but know that it works. If you wanted PA sales over $500, you'd have to use a "*" (multiplication) sign as follows:
=AVERAGE(IF((A1:A10="PA")*(B1:B10>500),B1:B10))
We limited based on the state as well as the size of the sale. You can get as complicated as you like, stringing results together like this. Remember, though, that you ALWAYS have to finish your formulas by hitting "Ctrl-Shift-Enter".
For a further discussion of array formulas, check out this article that will give you some more insight. Thanks for reading!
Anyway, on to today's blog. A few weeks back, a friend of mine had asked about using the SUMPRODUCT function, and I recommended that he use array formulas. In the comments to that blog, I briefly discussed array formulas and I'd like to expand on their usage today. Essentially, you will be interested in using array formulas (CSE's for "Ctrl-Shift-Enter" formulas) when you need to perform extremely complicated formulas on numerous cells/rows and then have a summary function to tie it all together. For example, this blog discussed the SUMIF and COUNTIF functions. So you know how to use them, but what if you wanted an "AVERAGEIF"? No such function exists in Excel, but CSE's will help us remedy this. The best way I can describe starting CSE's is by the summary function you want to utilize. In our example, we want to average values if a certain criteria is met. So our formula begins:
=AVERAGE(
The next step is to define the criteria. Let's assume we're interested, in averaging sales by state. Now realize that this is a somewhat contrived example. The easiest way to average across states is to do "=SUMIF(State_Col, "PA", Sales_Col)/COUNTIF(State_Col, "PA")". That is, sum the sales by state and then divide by the number of entries identified by COUNTIF. But, it will help us get started. So, to limit on state, we need an "IF" statement to limit our records. So, we simply add the if:
=AVERAGE(IF(
Now it gets a little complicated. Essentially, the next part of the formula will define whether or not the number should be included in the CSE. We want to examine each cell in the range of state names. Lets assume that the states are in cells A1:A10 and the corresponding sales figures are in cells B1:B10. The "IF" part of the formula wants to know what to examine, so we enter the state cells:
=AVERAGE(IF(A1:A10
Since we want to see if it's equal to a particular value (Pennsylvania, for example), we just enter that:
=AVERAGE(IF(A1:10="PA",
The comma is added so that we know what we want to include as the value. We want the sales figures, so we'll add:
=AVERAGE(IF(A1:A10="PA", B1:B10))
It's that simple so far. The last step (after you add the braces as shown above) is to hit "Ctrl-Shift-Enter" to complete the formula. If you don't do the last step, the formula will not work properly. But this will average sales for Pennsylvania as we wished. You may be wondering, what if I wanted to sum New York or Pennsylvania? To do that, you have to modify the "IF" statement. You'll have to add:
=AVERAGE(IF((A1:A10="PA")+(A1:A10="NY"),B1:B10))
You'll notice that I added another set of parentheticals and a "+" (plus) sign. This says "If the state is PA OR if the state is NY, show the sales figure". You'll have to consult the help files to find out why we use a plus sign, but know that it works. If you wanted PA sales over $500, you'd have to use a "*" (multiplication) sign as follows:
=AVERAGE(IF((A1:A10="PA")*(B1:B10>500),B1:B10))
We limited based on the state as well as the size of the sale. You can get as complicated as you like, stringing results together like this. Remember, though, that you ALWAYS have to finish your formulas by hitting "Ctrl-Shift-Enter".
For a further discussion of array formulas, check out this article that will give you some more insight. Thanks for reading!
posted by ktr at
1:11 PM
2 Comments:
Great blog! In the past i found that if you want to save some money at tax time, you can get a good deduction by donating a car to a charity or non profit organization. If this intrests you, here is a good directory of car donation info that i found rather usefull, it really comes in handy to people trying to get refunds or donate to charity for tax purposes (which is a great benefit):
By
Anonymous, at 3:59 PM
Great blog! In the past i found that if you want to save some money at tax time, you can get a good deduction by donating a car to a charity or non profit organization. If this intrests you, here is a good directory of car donation info that i found rather usefull, it really comes in handy to people trying to get refunds or donate to charity for tax purposes (which is a great benefit): http://www.usa-car-donation.com
By
Anonymous, at 3:59 PM
Post a Comment
<< Home