0

I am making a spreadsheet to score recipe ingredients, averaging scores for quality of the ingredient, packaging, and accuracy of serving size, and then using that data to create an average for the whole recipe.

The scores are filled in through data validation. 1=Bad, 2=Fair, 3=Good. The AVERAGE formula works fine when all of the fields are input through the dropdowns, but if any of the rows are left with all blank values I get a DIV/0 error in the column I am attempting to get average in (E8:E19 ) as well as the average for the entire recipe (B20). I tried to do AVERAGEIF, but that didn't work and I am stumped. Every recipe has a different number of ingredients, so it is important that this function works whether I have one ingredient or 20.

I tried to upload a screenshot but the site won't let me.

3
  • Then post the image on some imagesharing site, and add a link to it. It would also be helpful if you showed us e.g. what you had tried to do with AVERAGEIF
    – eirikdaude
    Apr 23, 2015 at 22:08
  • Average normally does ignore blank cells. It must have something to do with filling in through data valuation.
    – user438322
    Apr 23, 2015 at 22:10
  • AVERGAE() will ignore some blanks, but if there are only blanks, what would you like the formula to return ?? Apr 24, 2015 at 0:14

2 Answers 2

0

Have you tried using the error-catch function in Excel? As you have said, if you take the average of only blank cells, Excel will provide a result of Div/0.

Let's say there are 3 people (A,B,C) reviewing a recipe for Guacamole. Let's say the csv looks something like this:

Ingredient,Score A, Score B, Score C

Avocado,1,2,3

Cilanto,,,

Lime,1,2,3

Tomato,1,2,3

When you calculate the average for each ingredient, use a formula like this: =IF(ISERROR(AVERAGE('ingredient score range')=TRUE),"",AVERAGE('ingredient score range')

All this formula is doing is saying if the average of the ingredient scores is an error, leave it blank. Otherwise, calculate the average.

Then you should be able to average all of the scores of your ingredients without a problem. You also have the option of scoring an ingredient as 0 rather than blank. This would of course change the math in the average for the recipe.

0

IFERROR is a very convenient function;

  • IFERROR(calculated_value, default_value)

    is short for

  • IF(ISERROR(calculated_value), default_value, calculated_value)

i.e., it substitutes a default value (typically a non-error value) for an error value.  So you probably want something like

IFERROR(AVERAGE(E8:E19), "")

replacing the "" with whatever you want to get when E8:E19 is all non-numeric.

You must log in to answer this question.

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