Thank you so much, you saved my life - this is for work. There was text in
the columns in particular the word "blank" the worksheet is a pivot table and
sometimes when I refresh the data the pivot table will insert the word blank.
I deselected blank from those columns and it worked. As you can probably
already tell I am self taught and a beginner in Excel.

To answer your question about why are you using =0 in your original
formula, can there be negative numbers? I'm not sure as this came from my
boss, we were having trouble getting the average formula to work in the first
place because the columns had empty cells so she found this formula somewhere
and had me enter it.

Now that we excluded the PTA survey's from that cell she wants only the PTA
survey average in the cell next to it . What would that formula look like
please?

Again many thanks for your help!
Rose

"Peo Sjoblom" wrote:

The reason you get a value error is that you have text in U11:V1143, you
should always avoid that as much as possible. Btw, why are you using =0 in
your original formula, can there be negative numbers?


--


Regards,


Peo Sjoblom




"Rose" wrote in message
...
Thanks so much for your quick reply. I copied and pasted to my worksheet
but
got the dreaded #VALUE!

Any suggestions, I really appreciate your help.

Many thanks,
Rose

"Max" wrote:

Try:
=SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA")*(U1 1:U1143=0)*(V11:V1143=0)*U11:V1143)/MAX(1,SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA" )*(U11:U1143=0)*(V11:V1143=0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik
---
"Rose" wrote:
Help Please! I need to learn how to get the average of two columns
excluding
certain types which are in a third column. Column U is Survey Score
column V
is Additional Survey Score but I need to exclude the type of "PTA"
survey
scores which are in column B.

The current formula we use just to get the average is of the two
columns is:
=SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0"))

What do I need to add to the formula to exclude the "PTA" scores, the
score
types are in column B. Or do I need to use a totally different formula
to
achieve this?

Best Regards,
Rose