Excel problem

vbimport

#1

First, I know this thread is in the wrong forum, but I need the solution quickly and because the LivingRoom has the most viewers I’m posting my question here. I’ve posted this question also in The Dutch Forum, but no reply at the moment.

I’m having a problem with a formula in excel.
It’s about the next formula:

=IF(F27=0;IF($F$2=0;NA());AVERAGE(B27:F27))

De formula has to look at field F27, when there’s a 0 it should look at field F2. If that’s also a 0, then it should return NA().
If F2 has a value greater than 0 it has to give the average of field B27 -> F27.
If field F27 has a value greater than 0 it has to give immediately the average of field B27 -> F27.

At the moment I get the announcement: False when there’s a 0 in field F27.

More info: field F2 ain’t 0.
Fields B27 -> F27 are also filled with numbers.

It’s probably very simple, but I just don’t see what’s wrong.

What am I doing wrong? :confused:


#2

What am I doing wrong?

you’re doin something wrong in the forumla!!! ;)…


#3

AVERAGE(B27:F27))
or
AVERAGE(B27;F27))

But i still don’t have a clue about excel :doh:


#4

Smartass. :cop:

It’s AVERAGE(B27:F27)). That’s correct.

I thought I had to extend the formula to: =IF(F27=0;IF($F$2=0;NA());AVERAGE(B27:F27);AVERAGE(B27:F27)) but I then get the message: You’ve entered too many arguments for this function. :a


#5

w00t, w00t, w00t, w00t, I’ve got it.

The formula should be: =IF(F27=0;IF($F$2=0;NA();AVERAGE(B27:F27));AVERAGE(B27:F27))

It was sooooooooooooooooooooo simple. :iagree: :bigsmile:


#6

Er, yes… bloody simple :confused:


#7

LOL…that’s wat i was thinkin too…damn namoh, bein such a smartass…don’t u juz hate that? ;)…hey…i shud be postin that in that “don’t u hate it” thread…:smiley:


#8

@theone1_ & vdk_au

It wasn’t that simple, because even the fromula above didn’t work. :stuck_out_tongue: :a

The formula that gives the correct solution is: =IF(AND(F27=0;$F$2=0);NA();AVERAGE(B27:F27)).

And yes, that is pretty simple, but I just couldn’t figure it out.

One cup of coffee and voila!!! I got it. :iagree: :clap:


#9

It does the same thing but is shortened using the AND logic test function.
You had implied the AND test in the previous formula by using a nested IF to get to the NA() function. In both nested IFs the AVERAGE function is returned if the result is false.

Good use of the AND function though! :wink:


#10

Yep, it is really simple but this morning I just couldn’t figure it out.

My boss wanted to have some graphs before 12.00 (always nice to have a boss that tells you such thing the same morning he needs it :rolleyes: ).

But after a cup of coffee, I realized what I was doing wrong.
It was so simple. :iagree:


#11

Namoh, your last two formulas are the same, only different formulation. My version uses “,” instead of “;”.

=IF(F27=0,IF($F$2=0;NA(),AVERAGE(B27:F27)),AVERAGE(B27:F27))

=IF(AND(F27=0,$F$2=0),NA(),AVERAGE(B27:F27))

Don’t know why the first didn’t work for you.


#12

I don’t know why the first formula didn’t work, but it just didn’t. :confused:


#13

Next time try this place for Excel questions.

http://support.microsoft.com/newsgroups/default.aspx

there are a lot of nice people that really can help.

It’s one of my favourite daily website visit places.