if statement

[al.rainey]

I am having trouble with a function. I am importing data from Filemaker
that
contains one column with a date for each day of the year (column B). A
second column, which may or may not contain data will be a number
value "MBW" (Column C". In column A, (hidden) I have a formula that
returns the month of the date column "month_num".

On another sheet I have a monthly table. What I want is an average of
the data in "MBW" for each month.

=IF(month_num=1,AVERAGE(MBW),"") should give me the January average for
"MBW", but instead returns the average for all of "MBW" (for the data
currently imported).

Any help appreciated...

Al

 

[Bob Phillips]

Al,

Try

=AVERAGE(IF(A1:A100=1,C1:C100))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[al.rainey]

Thanks for the reply. Works!

 

[al.rainey]

Now I am wondering how to not display the #DIV/0 message when there are
no records for a given month?

 

[Bob Phillips]

=IF(ISERROR(AVERAGE(IF(A1:A100=1,C1:C100))),"",AVERAGE(IF(A1:A100=1,C1:C100)
))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RagDyer]

BTW,

How do you rate gmail as compared to whatever your last mail provider was?

 

[Bob Phillips]

RD,

Do you want one to try yourself?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RagDyeR]

Thanks Bob, but already got an account a couple of weeks ago through my cell
phone number.
Seems its "open enrollment" through that form of registration.

Just wondered how others were evaluating the *actual* mail usage.

I'm cheating a little, and using it as a web "virtual drive".
It's great for accessing and transporting data between office and plant and
home.
And with free 2½ gig capacity, it's better and more convenient then my old
zips.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

RD,

Do you want one to try yourself?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Cheating? That's mainly what I use my (3) account for!

Bob

 

[RagDyeR]

Are you also using the gmail drive shell from viksoe, so that the gmail
drive automatically shows up in your Windows Explorer?

 

[Bob Phillips]

Yes I use that also.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RagDyeR]

Well, at this rate, it appears that Google will end up being to the Web,
what Redmond is to the PC !

They learned well from Mr. Gates ... give it away ... at the outset ... and
then you'll eventually own it all ! ! !
--


Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Yes I use that also.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[al.rainey]

Thanks for the reply Bob. I haven't had a chance to try it out until
this morning. It works fine with one exception. The average is not
accurate if I have any cells that are blank.
Col A
100

200

Using =average(a2:a4), the average is 150, of course. When I use the
array formula you suggest, it returns 100.

Can I get around this?

Thanks,

Al

 

[Bob Phillips]

=IF(ISERROR(AVERAGE(IF(A1:A100=1,C1:C100))),"",AVERAGE(IF((A1:A100=1)*(C1:C1
00<>""),C1:C100)))

still an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[al.rainey]

Thank you!

 

[Guest]

=if(iserror(c6),0,if(c6=0,0,d6/c6*e6)) will place a "0" how many cells can
excel use in th first formuls (iserror(c6) is there a way to have three cells
in first aug. does not seem to accept three. Any ideas anyone?

 

[Guest]

do you have excel book there error trapping formulas in there
=if(iserror(c6),0,if( This will put 0 in