# How to Use CHOOSE to Sum or Average Cell Ranges and Replace Nested IF Statements in Excel 2007 and Excel 2010

## Introduction

Hi and welcome to my latest hub on Excel. In today’s hub, I will be looking at the **CHOOSE** function.

In its simplest form, the **CHOOSE** function allows you to select a value from a list. So, you could, for example, have a list of seven items (such as the Seven Dwarfs) and ask the function to return the second item in the list.

**CHOOSE** can be used in conjunction with other functions such as **SUM** or **AVERAGE**, so I could **SUM** cells between a starting cell and another cell chosen by **CHOOSE** from a range.

At a more advanced level, it can be used to replace nested** IF **statements and can be configured to choose a value based on the results of a formula or the results of a **Control** such as a **Combo Box**.

We will work through an example of each scenario to illustrate the power of this useful and versatile function. The figure below shows how **CHOOSE** can enable you to create much simpler and easier formulas.

## Using the CHOOSE function in a formula to select an item from a list in Excel 2007 and Excel 2010

In our first example, we will use a simple formula to use the **CHOOSE** function to fetch an item from a list.

- First of all, I enter my data into the cells B30 to B36.
- I then enter the following formula into C30

=CHOOSE(2,B30,B31,B32,B33,B34,B35,B36)

The formula is made up of two parts:

- Which item in the list we are selecting (in this case 2)
- The cells that contain the list of values (for this example B30,B31,B32,B33,B34,B35,B36)

**Note:** *You can have up to 254 cells containing values making up your list in your formula should you wish to.*

In this instance, the formula returns the contents of the cell B31 as the answer which is Sleepy.

**Note:** *If I change the contents of the B31, Excel will automatically change the result of our formula in C30.*

## Using CHOOSE with SUM in a formula to sum a range of cells using Excel 2007 or Excel 2010

In our next example, we will use **CHOOSE** and **SUM** together to add up a range of cells.

For our example, we have a range of numbers in the cells B38 to B44. We use the following formula to add up a subset of the range.

=SUM(CHOOSE(2,B38:B40,B40:B42,B42:B44))

To understand the formula better, let’s break it down into its individual parts:

- First, as above, CHOOSE selects the 2
^{nd}item from our list which is in this case B40:B42 - Excel then uses SUM to add up the cells B40:B42

The answer is returned, 180

**Note: ***The item that we are looking for does not have to be a number. It can be a reference to a cell that contains a number, or a formula that generates a number.*

To illustrate this, we can use the cell reference A38 as what we are looking for and enter 1 into cell A38. This allows you to easily change what we are asking **CHOOSE** to fetch for us without making any changes to the formula itself.

The formula becomes:

=SUM(CHOOSE(A38,B38:B40,B40:B42,B42:B44))

**Note:** *If I enter a number into A38 that is outside the range of the CHOOSE function in our formula (so in this example 4 as there is no fourth item to choose from), Excel will display a #VALUE error rather than an answer.*

## Using the CHOOSE function to replace nested IF statements and using CHOOSE with Combo Boxes in Excel 2007 and Excel 2010

Now that we have shown how to use **CHOOSE** to select an item from a list and also how to use it in conjunction with other functions such as **SUM**, we will illustrate how to use it to replace nested** IF** statements and also how to use it with Combo boxes.

For this example, I will use the panel I made for the hub I wrote on how to create a Mortgage calculator. It used a range of functions including **PMT, PPMT, IPMT** and **ABS** as well as also using **Combo boxes** to allow users of my spreadsheet to select their interest rate and mortgage length from a drop down list and have their repayments automatically calculated. That hub can be found here:

To learn more about **Combo boxes** and how you can build and use them in your spreadsheets when you want to control a user’s input and allow them to select from a drop down list, I have a hub that goes into Combo boxes in much more detail which can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Combo-boxes-in-Excel-2007

Now onto the example (the below figure will help to illustrate all the component parts):

- We are interested in this case in the
**Interest Rate**that the user selects from the**Combo box**in cell**F18**. - This
**Combo box**allows the user to select from a list (the Combo Box uses the data contained in cells**P2 to P16**to populate the drop down) - When a user selects an
**Interest Rate**, Excel puts the result into cell**Q2**(the Combo box is linked to cell Q2) - You can see from the above figure that Excel enters
**9**into cell**Q2**when the user selects 4 from the Combo Box (as 4 is the ninth item on the list (P2 to P16))

OK, now where we need **CHOOSE** to help us is to convert this result in Q2 into something that we can use in our formulas which create the actual calculator. Originally, I used nested **IF** functions to create the following formula to convert the result of the Combo Box into an Interest Rate percentage in R2:

=IF(Q2=1,"2.25",IF(Q2=2,"2.50",IF(Q2=3,"2.75",IF(Q2=4,"3.00",IF(Q2=5,"3.25",IF(Q2=6,"3.50",IF(Q2=7,"3.69",IF(Q2=8,"3.75",IF(Q2=9,"4.00",IF(Q2=10,"4.25",IF(Q2=11,"4.50",IF(Q2=12,"4.75",IF(Q2=13,"5.00",IF(Q2=14,"5.25",IF(Q2=13,"5.50"))))))))))))))%)

What the formula does is:

- IF Q2 equals 1, then display 2.25% in R2,
- IF Q2 equals 2 then display 2.50% and so on.

To learn more about how to construct nested **IF** functions, I have a hub that investigates the **IF** function which can be found here:

We can use **CHOOSE** to create a much simpler and tidier formula in this case.

As we did above, we create a **CHOOSE** formula with two basic elements:

- Which item from the list we want Excel to choose and
- The list we want Excel to choose from

So for this formula, we want Excel to select an item from the list based on the contents of Q2 which is linked to our Combo Box in cell F18

The list is the same as listed in the Combo Box and is stored in cells P2 to P16

The formula is:

=CHOOSE(Q2,2.25%,2.5%,2.75%,3%,3.25%,3.5%,3.68%,3.75%,4%,4.25%,4.5%,4.75%,5%,5.25%,5.5%)

This is much easier to read and understand (and also to type) than the original formula which was:

=IF(Q2=1,"2.25",IF(Q2=2,"2.50",IF(Q2=3,"2.75",IF(Q2=4,"3.00",IF(Q2=5,"3.25",IF(Q2=6,"3.50",IF(Q2=7,"3.69",IF(Q2=8,"3.75",IF(Q2=9,"4.00",IF(Q2=10,"4.25",IF(Q2=11,"4.50",IF(Q2=12,"4.75",IF(Q2=13,"5.00",IF(Q2=14,"5.25",IF(Q2=13,"5.50"))))))))))))))%)

**Note:** *Simpler formulas that achieve the same goal are always preferable as they are easier to troubleshoot if they go wrong and also easier to understand, explain and replicate.*

## Conclusion

The **CHOOSE** function at its most simple, allows you to ask Excel to choose a specific item from a list (in my example, we chose the second dwarf from the list of the seven dwarfs). In addition, you can combine it with other functions such as **SUM **and **AVERAGE** so that Excel can sum cells from a range, for example, based on which cells you told it to add up.

Finally, we looked at more advanced uses for **CHOOSE**, specifically replacing nested **IF** statements and working with the results from **Combo Boxes**. This allowed us to create a shorter, more easily understood formula rather than a very long and complicated one. This helps reduce the possibility for errors and also makes your formulas easier to understand, replicate and explain.

As with all functions in Excel, the **CHOOSE** function has a very wide range of possible uses which is limited only by what you want it to do for you and which functions you decide to use it with. I hope that this hub has been interesting and informative and you now feel comfortable using **CHOOSE** in formulas to assist you. Many thanks for reading, please feel free to leave any comments you may have below.