2

In another question I posed recently I was shown how to use the Excel Match function to query my data in order to produce a summary type table:

Creating a formula that queries data in Column B based on a selection from Column A in Microsoft Excel

I was very helpfully provided with this formula which works really well:

=IF(ISERROR(MATCH($A$1:$A$2&"Not Fed", Animals!A$2:A$7&Animals!B$2:B$7, 0)), "All Animals Fed", "All Animals Not Fed")

I am now looking to see if there is a way to extend this formula to Match on a column containing more than one type of value (potentially up to 3 or 4 different values).

For example:

enter image description here

The output I would want to see based on the above is

enter image description here

I tried something like:

=IF(ISERROR(MATCH($A$1:$A$2&(OR("Fed","Partly Fed","Somewhat Fed")) Animals!A$2:A$7&Animals!B$2:B$7, 0)), "All Animals Fed", "All Animals Not Fed")

... but this didn't work and logically probably isn't even the right route based on how I want this to behave! Essentially as long as the range B1:B7 for the given AnimalType contains nothing but the allowed values (in whatever quantity / mix) I want the function to return true.

Improve this question
3
  • What is the expected behavior if an animal has both fed and not fed values?
    – ssssteffff
    Commented Apr 25, 2014 at 6:59
  • If the range is not made up exclusively of values that constitute true (i.e. Partly Fed, Somewhat Fed, Fed), then the formula should return false ("All Animals Not Fed"). So if Dog was, Fed, Fed, Not Fed, "All Animals Not Fed" should be returned. Hope this makes sense.
    – cw84
    Commented Apr 25, 2014 at 10:24
  • When you type these formulas, what behavior do you get? I think the first one still works, what result do you get?
    – ssssteffff
    Commented Apr 25, 2014 at 16:07

2 Answers 2

Reset to default
2

What you're trying to do is a cross tabulation (aka crosstab). The tool for doing that in Excel is called a PivotTable.

Unfortunately, pivot tables aren't compatible with qualitative data, so to get this to work, you're going to need to add a column, which represents your fed/not-fed data as a numeric value.

For instance, if you use =IF(B2="Not Fed",1,0) you will get a column in which if any animal is "Not Fed", you will get a one. (Note, you haven't specified what you want to happen if the Fed? field is empty for a row, or has other surprizing value. You haven't said what you want to happen in the case that "birds" -> {"not fed", "partially fed", "fed"}. What precise IF statement you use depends on your answers to these questions.)

Then you select the whole range with all three columns (or put the new column between these two, and select just the animals and your calculated column, and select "PivotTable report" from the Data menu.

The wizard will step you through a series of dialogs. On the first one, your data is in "Microsoft Excel list or database". On the second, if you selected your data before you started the wizard, it should default to your data range correctly.

On the third dialog, don't hit Finish immediately. You have a bunch of options you need to set. First, on that dialog, you will be given a choice of where to put the new pivot table. I recommend putting your pivot table on a new sheet. Next, click "Layout...". Drag the "Animals" box into the left column ("Row"). Drag the title to your new column into the "Data" area in the middle; when you do that, it will show up as "Sum of nameoffield". Double click it, change the name to something like "Are any animals unfed?" and change the summary type to "Max", and click "Okay", then click "Okay" again to finish with the Layout dialog. You're still on step 3 of the wizard dialog. Click "Options...". Unclick "Grand Total for Columns" and make sure "Grand Total for Rows" is on. Turn on "For Error Values Show" (very important for this) and set it to 0, and hit "Okay". Then, finally, you can hit "Finish" the wizard.

You should wind up with a new sheet that says:

Are any animals unfed?  
Animals Total
Cat     0
Dog     1

Now, you can enter into the column right after that, =IF(B3=1, "Some Animals Not Fed", "All Animals Fed") and so on down the column, and if any one animal in a type is "Not Fed", it will read "Some Animals Not Fed", otherwise "All Animals Fed"). You can now hide the B column, and see only:

Are any animals unfed?  
Animals 
Cat     All Animals Fed
Dog     Some Animals Not Fed
Improve this answer
1
  • Thanks ever so much for your answer - a very informative lesson in Pivot Tables. I had long been aware of these but considered them more advanced, whereas they are actually quite simple. I have now applied your teachings to my real-life scenario at work (tracking the completion status of development work) and it's working perfectly.
    – cw84
    Commented Apr 28, 2014 at 16:31
0

I would create a Pivot Table (Insert ribbon) from the range. I would drag the AnimalType field to the Row Labels zone, and the Fed? Field to the Column Labels zone. I would drag the AnimalType field to the Values zone.

This will give you a count of Animals by type and Fed status, with row and column totals. These can answer all the questions you are trying to answer, without coding any formulas, in a structure that can easily be extended to include other columns and values.

Pivot Table example

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .