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
