Average With < Continued
 

=SUMPRODUCT(--ISNUMBER(H21:H51)),H21:H51)/COUNTA(H21:H51)

-----CTRL SHIFT ENTER

THis forumla works for averaging without adding numbers that are < but
how would I get this formula to work and put a 0 in the average space if
all the numbers are <. THe max and min must show the number with the <.
The MIN can not show 0 for the lowest number, it has to be one of the
results listed.


Example:

Sunday <2
Monday
Tuesday <2
Wednesday
Thurdaday <2
Friday <2
Saturday <

Average:
MAX
MIN


--
hamricka
------------------------------------------------------------------------
hamricka's Profile: http://www.thecodecage.com/forumz/member.php?userid=676
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127104

Average With < Continued
 

With those examples... what would be the answers for Max, Min, Average?


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127104

Average With < Continued
 

It looks like you asked this question already he

http://tinyurl.com/kjdzqj

is that so?

If yes, you should provide links so that people helping you aren't
overstepping each other and wasting time...

Based on you last comment to Domenic's solution... is this what you
want?


="<"&MAX(IF(H21:H51<"",SUBSTITUTE(H21:H51,"<","") +0))

confirmed with CSE


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127104

Average With < Continued
 

You answered the average question on the August 15 th. but now I need
to take that answer a step further and have the formula for average show
0 if all the numeric values are <.

NBVC;459310 Wrote:
It looks like you asked this question already he

http://tinyurl.com/kjdzqj

is that so?

If yes, you should provide links so that people helping you aren't
overstepping each other and wasting time...

Based on you last comment to Domenic's solution... is this what you
want?


="<"&MAX(IF(H21:H51<"",SUBSTITUTE(H21:H51,"<","") +0))

confirmed with CSE


--
hamricka
------------------------------------------------------------------------
hamricka's Profile: http://www.thecodecage.com/forumz/member.php?userid=676
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127104

Average With < Continued
 

hamricka;459369 Wrote:
You answered the average question on the August 15 th. but now I need
to take that answer a step further and have the formula for average show
0 if all the numeric values are <.

As far as I can see, the formula for the Average that I gave you:

=SUMPRODUCT(--(ISNUMBER(A1:A5)),A1:A5)/COUNTA(A1:A5)

does return 0 if all values have a < in front.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127104