Excel Average Help

[michaela18]

I want to average a range

a2:AC2

Some cells have text, blanks, and dates. I want to only average the cells with numbers in them > 0. When I do this it will also average the date values also. How can i exclude this?

 

[SkipVought]

Hi,

Some cells have text, blanks, and dates

Exactly then, what are you averaging! This kind of a range is a MESS!

Please explain your data. and exacly why you have this awful mixture of data types.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

I totally agree with Skip that this is a sloppy mess that should be fixed.

That having been said, try this:
[tab][COLOR=blue white]= SUMIF(A2:AC2, ">0") / COUNTIF(A2:AC2, ">0")[/color]



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mintjulep]

That will still include the dates - assuming (and that's a big assumption) that the dates are proper Excel dates.

 

[SkipVought]

John, you're CERTAINLY NOT 'Excel Average Help!'

Indeed, you are way above the average!

However, your solution does NOT disregard DATES.

Still believe that there must be a better way to structure your sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

OK,

I added a ROW 3 with this function...
[tt]
A3: =CELL("format",A2)
[/tt]
then the formula entered as an ARRAY FORMULA (shirt+ctr+enter)...
[tt]
= SUMPRODUCT(IF(ISNUMBER(A2:AC2),A2:AC2,0)*(LEFT((A3:AC3),1)<>"D"))/SUMPRODUCT((IF(ISNUMBER(A2:AC2),A2:AC2,0)>0)*(LEFT((A3:AC3),1)<>"D"))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Awww, shucks.

*kicks dirt*

I completely missed the word "dates" in the OP. That really screws things up.

Back to Skip's question: "Please explain your data. and exactly why you have this awful mixture of data types."

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Although, did you notice, that since your threw down the gauntlet, I took a stab at the mess.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]