Working out figures no matter what

[Cmorgan]

Last problem and then I can enjoy my weekend and close this spreadsheet

I'm using:
=AVERAGE('INPUT SHEET'!S:S)

But I need to alter it so that it will work out the average score of this column no matter what (if there are missing figures or additional rows are added).

I've tried playing around with =IF(ISERROR, but I'm not really sure what to do.

Any help would be greatly appreciated.

Thanks

[teylyn]

The formula you posted will average all values in that column. I don't understand what the problem is. If additional rows are added, they will be included in the calculation, since the formula refers to the whole column.

If you want to exclude cells with a particular value, e.g. zero, you could use AverageIf, like

=AVERAGEIF('INPUT SHEET'!S:S,">0")

[Cmorgan]

I think the problem is because some of the cells have no value, as it gives an error message of:

Value not available error

I want it to work out the value of every cell in that column that has a figure in, whatever the figure is. (Some of the lower cells have formulas in to work out the value added if new students are added - which I think might be causing the problem)

Cheers

[teylyn]

Can you post a small data sample in a spreadsheet?

[Cmorgan]

Thanks for your help on this, I have attached the spreadsheet.

I seem to have everything working except W2 on the Table Sheet. In the cell I am trying to work out the average value for column S on the input sheet.

(When I have finished the spreadsheet, the input sheet will be blank from columns A to O, teachers will copy and paste 'their' data into these columns and then the spread sheet will work out all the data on the Table sheet. The number of students that teachers copy and paste into the columns A to O will vary, which has made things difficult.)

I greatly appreciate your help.

Thanks

[Roger Govier]

Hi

You have lots of errors on your sheet because you are not using any error trapping.

Your immediate problem is caused because of the problem with the formula in cell Q2 of sheet Input sheet.

Change it to
=IF(N2="",0,VLOOKUP(N2,FORUMLAS!$O$1:$P$9,2))

and in R2
=IF(O2="",0,VLOOKUP(O2,FORUMLAS!$O$1:$P$9,2))

and copied down.

Then in W2 of Table
=AVERAGEIF('INPUT SHEET'!S:S,">0")
returns a result of 2.7

In the attached workbook, I have also inserted error trapping to get ris of your DIV0/# errors

[teylyn]

Hello,

there are several issues with the spreadsheet.

Columns Q and R produce #N/A errors when no match is found. You can avoid these errors by wrapping the Vlookups in these columns into an IfError function, for example in Q2

=IFERROR(VLOOKUP(N2,FORUMLAS!$O$1:$P$9,2),"")

and in R2

=IFERROR(VLOOKUP(O2,FORUMLAS!$O$1:$P$9,2),"")

Copy down.

Now there are no errors in columns Q and R and no #N/A errors will impact the formulas that refer to these columns.

Another issue is the formula in column S. It is in row 2

=SUM(Q2-R2)

I'm not quite sure this formula is doing what you want it to do.

Do you want to Sum columns Q and R in that row?
Or do you want to subtract column R from column Q in that row?

Whenever I see a normal calculation like A-B wrapped in a Sum() function, as in Sum(A-B) the hairs of my neck rise.

Some people will wrap any calculation into a Sum() function, thinking that it is required for Excel to do the calculation. It's NOT.

This is a classic case where it is absolutely UNCLEAR if the function delivers the expected result.

Do you want to SUM the cells of Q and R? If so, use =Sum(Q2:R2)

Do you want to SUBTRACT R from Q? If so, just use =Q2-R2

But using =SUM(Q2-R2) as in your file is very confusing. I don't know whether or not you know what you're doing or whether you just use a funny "I've always done it that way" of entering a formula, always putting a Sum() around it.

The difference becomes apparent thus: If you apply the formulas I suggested above for columns Q and R, you will find that column S now delivers another error in row 21 and down, i.e. #VALUE. That error means that a formula attempts a calculation with invalid input. The formula calculates

=SUM(Q21-R21)

Both Q21 and R21 are "" -- blank values, after applying the IFERROR function described above.

Now I'm wondering: Do you actually mean to sum Q21 and R21? Because

=SUM(Q21:R21)

would NOT give an error.

On the other hand, if you REALLY want to calculate Q21-R21, then you should do just that, without the SUM() around it.

=Q21-R21

Then, of course, the - operator does not tolerate text, so in order for this formula to work (or for =SUM(Q21-R21), for that matter), you need to make sure that the elements referenced in the formula are really numbers.

Sum can tolerate text in a range. But using operators like + or - will not work with text. So, you need to change your formula in columns Q and R to

=IFERROR(VLOOKUP(N2,FORUMLAS!$O$1:$P$9,2),0) -- column Q
=IFERROR(VLOOKUP(O2,FORUMLAS!$O$1:$P$9,2),0) -- column R

Then the formula in column S (=SUM(Q2-R2)) will not throw an error.

I urge you to revise the formula in that column, though. If you want to use a Sum() to sum data, then use the proper syntax. If you want to use an operator with several cell inputs, then don't wrap it in a Sum(). Don't use Sum() unless you really want to sum something. Sum(A1+B1) and A1+B1 and Sum(A1:B1) may look the same, but can have different results, depending on the values in A1 and B1. Unless you understand these difference, please just use Sum() with a range, i.e. Sum(A1:B1) and use the operator, i.e. =A1-B1 if you don't want to sum.

Sorry, this has gotten quite lengthy and evangelical, but if you learn how to use formulas properly and not wrap everything in a Sum(), your life will be a lot easier.

[Cmorgan]

Thanks for taking the time.

On the point about =SUM(Q2-R2), this is because I am 'self taught' (and i use that phrase very loosely) and it is a mistake I have just picked up. Thanks for making me aware of this, I've changed the columns as suggested, and will use the standard formula =Q2-R2 from now on, it's just easier as well.

I made the changes to Iferror function for both columns and that has worked with the errors going away lower down. Unfortunately it has not sorted the problem on the TABLE SHEET for W2 where I am trying to use the formula =AVERAGE('INPUT SHEET'!S:S) - so this cell work will out the average for whatever figures are in column S on that sheet. Any ideas please?

I really do appreciate all your help on this subject.

[teylyn]

I was probably too wordy for you to read all of my post.

Towards the end I suggest to change the formula from

=IFERROR(VLOOKUP(N2,FORUMLAS!$O$1:$P$9,2),"")

to

=IFERROR(VLOOKUP(N2,FORUMLAS!$O$1:$P$9,2),0)

Combined with the suggestion from my first response to this question, i.e. to use

=AVERAGEIF('INPUT SHEET'!S:S,">0")

... or now that I know the context, I'd adjust that to

=AVERAGEIF('INPUT SHEET'!S:S,"<>0")

then the formula in W2 will not throw an error.

See attached.

cheers,

[Cmorgan]

Thanks for this, it works a treat. I appreciate all your help.