Average With < Continued

[hamricka]

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

-----CTRL SHIFT ENTER

THis forumla works for averaging without adding numbers that are < bu
how would I get this formula to work and put a 0 in the average space i
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 th
results listed.


Example:

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

Average:
MAX
MI

 

[NBVC]

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

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[NBVC]

It looks like you asked this question already here:

http://tinyurl.com/kjdzqj

is that so?

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

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


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

confirmed with CS

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[hamricka]

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

 

[NBVC]

You answered the average question on the August 15 th. but now I nee
to take that answer a step further and have the formula for average sho
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

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com