BluBBen
July 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?
Sickone
July 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.
Sickone
July 13 2009 4:25 AM EDT
Um, I mean it works on logical expressions only if the results are numbers, not text containing numbers :)
AdminShade
July 13 2009 5:23 AM EDT
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.
Easier fix. ;)
Take the "" out of the IF function. That sets the 20, 15, 10 and 5's to Text instead of numbers.
AdminShade
July 13 2009 8:32 AM EDT
That is exactly what he does not want to happen.
The "" makes the cell turn blank instead of having a 0 in it.
=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. ;)
There's no IF clause asking for a blank cell Shade. ;)
(I <3 Excel. Tend to do a lot of my work in it. Not really on topic, just wanted to 'big it up'! :P )
QBsutekh137
July 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*
BluBBen
July 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 =)
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.
BluBBen
July 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>