Solved: Excel sum and average functions

Discussion in 'Business Applications' started by caraewilton, Feb 27, 2008.

Not open for further replies.

Joined:
Nov 7, 2007
Messages:
1,352
Hi guys

I am trying to set up a markbook type thing using excel. File attached.
There are 10 sets of mark. Each set is made up of a academic mark (%), which will be typed in by the teacher. The second column is a symbol which will add or subtract bonus marks. A=25
B=15
C=7.5
D=0
E=-15

The teacher will just type in the symbol.

The third column is a hidden column. This column uses an if function which gives the correct bonus mark as described above.

The fourth column must add the marks from the first column and the bonus marks in the third column.

At the end, there must be an average column, which must keep a running average of the marks.

My problem is this, if I use the sum function it returns a 0 value, if no values are entered. This then messes up the average value at the end. How can I enter the sum value so that if no figures are entered the total cell remains blank so that the correct average value is reflected.

Many thanks
Cara

File size:
2.8 KB
Views:
18
2. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Is this what you want?

Attached Files:

• MARK BOOK v2.zip
File size:
6.8 KB
Views:
28

Joined:
Mar 8, 2005
Messages:
19,679
I think it should be more like this (only three values entered though).

Won't you need a Lookup for the values in the Hidden Columns?

File size:
3.1 KB
Views:
23
4. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Well, now, my UK friend, in your formula
=IF(E5="A",25,IF(E5="B",15,IF(E5="C",7.5,IF(E5="D",0,IF(E5="E",-10,"")))))
E should be equal to -15, not -10 (see post 1) - you don't grade as hard as Cara, obviously!
Otherwise we get the same thing, though.
Oh, and neither of us changed the spelling of "Totoal" - is that maybe some odd SA spelling? LOL

Joined:
Mar 8, 2005
Messages:
19,679
Sorry, I don't think that your average is quite right.
Also that formula is not mine

6. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Uh, duh, quite right on both counts (shamefaced) I didn't even look at my average - "Sorry, I don't think that your average is quite right." indeed!!! Boy, are you being nice today!
=(G5+K5+O5+S5+W5+AA5+AE5+AI5+AM5+AQ5)/10
would work better for the average....
As for the formula, well, you know, I guess Cara is just a typical woman - can't make up her mind. (I said that to annoy you Cara, you do know that, don't you?)

Joined:
Mar 8, 2005
Messages:
19,679
The trouble is dividing by 10 only works for 10 values and cara specifically wanted to allow for Blanks.
I like you Vlookup though, which is what I was suggesting cara needed.

8. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Oh, I hadn't read her post well enough - how about a column using ISBLANK to list the data that is blank as True or False, then using a COUNTIF in a manner that you caount the False responses and divide by that?
For instance, with data in column A, I have a this in D
=ISBLANK(A1)
and this at the bottom of D
=COUNTIF(D117,"False")

Joined:
Mar 8, 2005
Messages:
19,679
I think that the formula that I used in the Totals Columns Takes care of it, just add your Vlookup to the hidden columns and it should work OK.

Joined:
Nov 7, 2007
Messages:
1,352
Thanks guys.
As for the -10, -15 thing, the powers that be are still deciding soooo, hopefully, they will eventually decide and let me know
I thought of using a lookup table rather than the IF formula. The up side to that would be that should the powers that be change their mind regarding the grading, it will be much easier to adjust... I think.
Anyway thanks a million.

11. Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Set your SUM formulas to return a numeric, if zero, have it return the NA() function. Then, on your AVERAGE formula, use an array-based formula. Something like this ...

Code:
`=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))`
This assumes your values are in A1:A5 (no, I have not looked at your file) and can be numerics or the NA() function, or even alpha characters, but it will only pick up valid excel-based numbers. You see where I'm going though (I hope)..

HTH

Edit: The array-based formula is confirmed with Ctrl + Shift + Enter, btw.

Joined:
Nov 7, 2007
Messages:
1,352
Hi, just another question, obp, in your formula you use <>
What exactly does this mean? Does it just tell excel to look for values for greater than or less than 0 only...

13. Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
<> means "not equal to", whereas = means "equal to".

Joined:
Nov 7, 2007
Messages:
1,352
Thanks firefytr. I originally tried something like that but could not get it to work properly because the figures I want to average are not in consecutive cells, ie A1, B1,C1 etc but rather A1, E1, G1.
Personally I think my problem lies in trying to work with arrays I can't even get them to work on paper, as my understanding is messed up. I even have to get another teacher to teach that section of the syllabus

Joined:
Mar 8, 2005
Messages:
19,679
And it is checking that there is something in the cell, and if it is nothing then it puts nothing in the total.
It seemed to work.
Unfortunately Excel doesn't have the same "Is Null" and "Null" functions that Access has, but then I would use Access anyway because it suits inputting things like "Pupils" and "Subjects" etc.

As Seen On