Combine Two Formulas

**NBVC** · 02-20-2008, 11:56 AM

...a little confusing....

but if I take you literally...then I come up with this:

=IF(COUNTIF(B3:B14,">0"),IF(ISNA(B17),AVERAGE(B3:B14),NA()),0)

is this what you need?

**SOMLSS1** · 02-20-2008, 08:42 PM

Having the same problem with that formula as I was mine.

It gives me N/A preceded with a # sign (not sure why), but is still will not calculate the AVERAGE when all the values are not available.

Here is the file so you can see what I have so far.

Thank you again for the help!

**JRock** · 02-20-2008, 09:05 PM

So I didn't know if you only wanted the average of the months with values or the months with the zero months included. So if you want an average of just the months available, try.

=IF(COUNTIF(B3:B14,">0")=12,"N/A",ROUNDUP(SUM(B3:B14)/COUNTIF(B3:B14,">0"),0))

If you want the monthly average including the 0 months try:

=IF(COUNTIF(B3:B14,">0")=12,"N/A",ROUNDUP(AVERAGE(B3:B14),0))

**SOMLSS1** · 02-20-2008, 09:44 PM

Originally Posted by JRock

If you want the monthly average including the 0 months try:

=IF(COUNTIF(B3:B14,">0")=12,"N/A",ROUNDUP(AVERAGE(B3:B14),0))

Exactly what I needed. THANK YOU!

**SOMLSS1** · 02-21-2008, 07:05 AM

..........

**NBVC** · 02-21-2008, 09:26 AM

Originally Posted by SOMLSS1

Having the same problem with that formula as I was mine.

It gives me N/A preceded with a # sign (not sure why), but is still will not calculate the AVERAGE when all the values are not available.

Here is the file so you can see what I have so far.

Thank you again for the help!

Try also:

=IF(COUNT(B3:B14)=12,NA(),ROUNDUP(AVERAGE(B3:B14),0))

**SOMLSS1** · 02-21-2008, 01:30 PM

I can't believe I'm still stuck on this!

I have modified the formula to:

=IF(COUNTIF(B3:B14,""),"N/A",ROUNDUP(AVERAGE(B3:B14),0))

Which does what I need it to with N/A but will not AVERAGE(B3:B14) unless all 12 month values are there. I need it to calculate if 1-11 are there but not the 12th value.

**NBVC** · 02-21-2008, 01:45 PM

How is this different than my last suggested formula?

**SOMLSS1** · 02-21-2008, 02:08 PM

Instead of the cell reading #DIV/0! when there are no values set for "Usage" it reads N/A (not available), like I need it to.

But your formula calculate the AVERAGE like it's supposed as mine wont.

**NBVC** · 02-21-2008, 02:29 PM

Originally Posted by SOMLSS1

Instead of the cell reading #DIV/0! when there are no values set for "Usage" it reads N/A (not available), like I need it to.

But your formula calculate the AVERAGE like it's supposed as mine wont.

So do we have a solution or not?

I'm not following.