BluBBenJuly 13 2009 12:52 AM EDT

Ok, so I have some problem with excel and I want to know why it does what it does to me.

Ok, so heres the thing.

I have 10 boxes with this IF:

=IF(C2="MVG", "20", IF(C2="VG", "15", IF(C2="G", "10", "5")))
(and then with C3, C4, C5 ect down to this: )
=IF(C11="MVG", "20", IF(C11="VG", "15", IF(C11="G", "10", "5")))

So then I want to get the average of these 10 boxes.

So I did like this :
=(D2+D3+D4+D5+D6+D7+D8+D9+D10+D11)/10
That worked as it was supposed to but I'm just doing this to get a little better at Excel (I'm terrible as it is now) so I tried to make that same thing with the =AVERAGE funktion instead, like this:
=AVERAGE(D2:D11)
But now it gave me the output "#DIV/0!"

Seems to me like those two ways is pretty much the same. =S
Anyone know why the second way doesn't work?

SickoneJuly 13 2009 4:23 AM EDT

Because the "AVERAGE" function only works on numbers, not on logical expressions, even if the result of the cell is a text containing a number.

If you would add inside the E2:E11 cells something like E2=D2*1, E3=D#*1 and so on (and format cells as numbers if not already formated like that).
You COULD make an E12=AVERAGE(E2:E11) afterwards.

Or, you could change your D cells to say
=IF(C2="MVG", 20, IF(C2="VG", 15, IF(C2="G", 10, 5)))
that way you would actually get numbers in your D cells, not text containing numbers.

SickoneJuly 13 2009 4:25 AM EDT

Um, I mean it works on logical expressions only if the results are numbers, not text containing numbers :)

if your average function does not work, check if those cells are all in number or general format indeed.

Alternative: send me a copy through mail and I'll look at it later today.

AdminQBGentlemanLoser[{END}]July 13 2009 8:22 AM EDT

Easier fix. ;)

Take the "" out of the IF function. That sets the 20, 15, 10 and 5's to Text instead of numbers.

That is exactly what he does not want to happen.

The "" makes the cell turn blank instead of having a 0 in it.

AdminQBGentlemanLoser[{END}]July 13 2009 8:34 AM EDT

=IF(C2="MVG",20, IF(C2="VG",15, IF(C2="G",10,5))) (and then with C3, C4, C5 ect down to this: ) =IF(C11="MVG",20, IF(C11="VG",15, IF(C11="G",10,5)))

Should work perfectly. ;)

AdminQBGentlemanLoser[{END}]July 13 2009 8:36 AM EDT

(I <3 Excel. Tend to do a lot of my work in it. Not really on topic, just wanted to 'big it up'! :P )

QBsutekh137July 13 2009 9:13 AM EDT

Oh no, Excel users! The database programmer's most notorious predator (or is that prey?)

Use a real database! *smile*

BluBBenJuly 13 2009 10:04 AM EDT

Yes, it worked perfectly after taking out the "" around the numbers.
Thanks guys! I'll post here if I run into any more problem =)

Admindudemus[jabberwocky]July 13 2009 10:05 AM EDT

if you haven't changed cell formatting, you can easily determine if excel is reading cell contents as text or numbers by looking at their justification. left justified for text and right justified for numbers.

BluBBenJuly 13 2009 10:18 AM EDT

Not if you have the text centered, like stupid BluBB had >.<
This thread is closed to new posts. However, you are welcome to reference it from a new thread; link this with the html <a href="/bboard/q-and-a-fetch-msg.tcl?msg_id=002pEk">Question about Excel</a>