MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Multiple formulas


Posted by RANJO on October 23, 2001 10:34 AM

Can someone please help me with this problem?
I need to get the average for a column that already has a formula of ex: IF(I280, I280-G280,"") My other problem is that some cells are empty or have text which gives me a #value error. I am trying to get the days lapsed between events and the the average of the total days. I have wrecked my brain on this one, I give up.

Posted by Barrie Davidson on October 23, 2001 10:40 AM

If I understand your problem, try changing your formula to:

=IF(I280,=IF(ISNUMBER(I280-G280),I280-G280,""),"")

Hope this helps you out.

BarrieBarrie Davidson

Posted by Barrie Davidson on October 23, 2001 10:40 AM

Oops, make that.

Posted by Mark W. on October 23, 2001 10:41 AM

The AVERAGE() worksheet function ignores text,
logical values, and empty cells. What seems to
be the problem?

Posted by Barrie Davidson on October 23, 2001 10:41 AM

Oops, make that


=IF(I280,IF(ISNUMBER(I280-G280),I280-G280,""),"")
Barrie Davidson

Posted by RANJO on October 23, 2001 11:25 AM

That work hopefully this will help explain it more. Here are what my columns look like:
D E F G
DT Sent DT Rec'd Days(Snt/Rc'd) StatProc
7/2/01 7/10/01 8 7/13/01
H I J
DAYS(Rec'd+Stat) Trv Proc DaysStat/Trv)
3 7/16/01 3
K
DAYS TTL
6

OK, so in columns F,H & J, the formula is similar to IF(I3, I3-G3,"") In column K it is IF(I6,I6-E6,"") This is done for 300 or so facilities, so in the end I need an average for column K. As far as getting the "value error", I have column C labeled on online and if a facility submits online I enter the date in column c and the rest of the columns remain empty but I would like to enter "0" so that it can also be averaged in Column K.


Posted by rj on October 23, 2001 11:27 AM

Posted by rj on October 23, 2001 11:29 AM

Sorry I forgot to say I just want the average for Column K which contains the formula =IF(I6,I6-E6,"")in each cell.

Posted by Mark W. on October 23, 2001 11:32 AM

=AVERAGE(K:K)

Posted by rj on October 23, 2001 11:46 AM

Still not working


Posted by Aladin Akyurek on October 23, 2001 12:06 PM

Re: Still not working

Try the following array formula

=SUM(IF(ISNUMBER(K1:K10),(K1:K10)))/SUM((ISNUMBER(K1:K10)+0))

You need to hit CONTROL+SHIFT+ENTER to enter this formula, not just ENTER.

Adjust the range K1:K10 to your situation.

Mark is right about AVERAGE. The above is just for testing, so please report back what you get.

Aladin

========== : That didn't work hopefully this will help explain it more. Here are what my columns look like:


Posted by rj on October 23, 2001 12:21 PM

Re: Still not working

Thanks A Million, I believe it worked. The average formula did not because it kept giving me zero.


Posted by Mark W. on October 23, 2001 12:28 PM

Gee...

...awfully convoluted. What was the problem with
using =AVERAGE(K1:K10)? Try the following array formula =SUM(IF(ISNUMBER(K1:K10),(K1:K10)))/SUM((ISNUMBER(K1:K10)+0)) You need to hit CONTROL+SHIFT+ENTER to enter this formula, not just ENTER. Adjust the range K1:K10 to your situation. Mark is right about AVERAGE. The above is just for testing, so please report back what you get. Aladin ==========


Posted by Aladin Akyurek on October 23, 2001 12:38 PM

Re: Gee...

Of course, it is. Now your turn. RJ says this works, AVERAGE doesn't, anycase returning 0 when it shouldn't. Can you extract a diagnosis for I exhausted my Latin (a Dutch expression, in case it's unclear)?


>What was the problem with


Posted by Aladin Akyurek on October 23, 2001 12:38 PM

Re: Gee...

Of course, it is. Now your turn. RJ says this works, AVERAGE doesn't, anycase returning 0 when it shouldn't. Can you extract a diagnosis for I exhausted my Latin (a Dutch expression, in case it's unclear)?


>What was the problem with


Posted by Barrie Davidson on October 23, 2001 12:46 PM

I'm with Mark...

RJ, can you send me a copy of your spreadsheet so I can better understand why Aladin's solution works and Mark's doesn't (I can't let a mystery hang there unsolved)?

Barrie

Posted by Mark W. on October 23, 2001 12:49 PM

I never definitively saw that RJ tried =AVERAGE(K1:K10 (nt)

Of course, it is. Now your turn. RJ says this works, AVERAGE doesn't, anycase returning 0 when it shouldn't. Can you extract a diagnosis for I exhausted my Latin (a Dutch expression, in case it's unclear)?


Posted by Aladin Akyurek on October 23, 2001 12:52 PM

Re: I'm with Mark...

Barrie

I don't understand your subject line... I wasn't and am not against using AVERAGE. I suggested the array formula for diagnostic purposes. Like you I'd like to take a look at RJ's workbook.

Regards,

Aladin

Posted by Barrie Davidson on October 23, 2001 12:57 PM

Re: I'm with Mark...

I don't understand your subject line... I wasn't and am not against using AVERAGE. I suggested the array formula for diagnostic purposes. Like you I'd like to take a look at RJ's workbook. Regards, Aladin

Subject line - I just meant that I share Mark's confusion. I'd love to see that spreadsheet because I hate mysteries!!

Sincerely,
Barrie