Back to Dates in Excel archive index

Back to archive home

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.

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.

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

The AVERAGE() worksheet function ignores text,

logical values, and empty cells. What seems to

be the problem?

logical values, and empty cells. What seems to

be the problem?

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

Barrie Davidson

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

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.

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

=AVERAGE(K:K)

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:

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

...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 ==========

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 ==========

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

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

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

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)?

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

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.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.