# Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

## Multiple formulas

Posted by RANJO on October 23, 2001 10:34 AM
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.

## Re: Multiple formulas

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

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

Hope this helps you out.

BarrieBarrie Davidson

## Oops, make that.

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

## Re: Multiple formulas

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?

## Oops, make that

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

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

## Re: Multiple formulas

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.

## Re: Multiple formulas

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

## Re: Multiple formulas

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.

## Re: Multiple formulas

Posted by Mark W. on October 23, 2001 11:32 AM
=AVERAGE(K:K)

## Still not working

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

## Re: Still not working

Posted by Aladin Akyurek on October 23, 2001 12:06 PM
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.

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

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

## Re: Still not working

Posted by rj on October 23, 2001 12:21 PM
Thanks A Million, I believe it worked. The average formula did not because it kept giving me zero.

## Gee...

Posted by Mark W. on October 23, 2001 12:28 PM
...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 ==========

## Re: Gee...

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

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

## Re: Gee...

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

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

## I'm with Mark...

Posted by Barrie Davidson on October 23, 2001 12:46 PM
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

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

Posted by Mark W. on October 23, 2001 12:49 PM
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)?

## Re: I'm with Mark...

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

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,

## Re: I'm with Mark...

Posted by Barrie Davidson on October 23, 2001 12:57 PM
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

This archive is from the original message board at www.MrExcel.com.