Combine Two Formulas

Working on the same simple chart NBVC helped me on, but this is for AVERAGE instead of SUM and with an added variable.

I've come up with this, which works:

Please Login or Register  to view this content.

Then needed to add; IF(B17,"N/A"). I tried using AND getting an error of too many arguments?

This also works to give me N/A but not the the AVERAGE I need if B17 is not N/A:

Please Login or Register  to view this content.

Thanks for your help!

Not quite sure that's working out. I have never used that, so it may be me.

B17 says N/A when it can't get a total from the all 12 months from above.
If B17 says N/A I need B16 (cell to hold this formula) to show the AVERAGE of the 12 months. If B17 has a total, then B16 needs to show N/A.

I also still need COUNTIF in there too, I need a 0 showing with no data for the above months. (COUNTIF(B3:B14,">0")

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!

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))

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!

..........

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

I have modified the formula to:

Please Login or Register  to view this content.

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.

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.

I am sorry for being confusing. I'm just trying to get this to come out looking good and functional.

I have your formula:

Please Login or Register  to view this content.

And mine:

Please Login or Register  to view this content.

Both work different. Yours works properly to calculate the AVERAGE, but when there is no values in the "Usage" column the cell displays #DIV/0! (I looked into this and it's because Excel is reading that it is dividing my a denomenator of 0).

Mine does what I would like it to when there is no values in the "Usage" column by displaying N/A, but will not calculate the AVERAGE(B3:B14).

Also remember that when all 12 month's values are known, I need this cell to show N/A again.

Thanks again!

That did it. I adjusted it a tiny bit to get N/A to look like my other formulas.

Please Login or Register  to view this content.

One more part to this, and I'm done.

I have got:

Please Login or Register  to view this content.

&

Please Login or Register  to view this content.

and need to combine those. I have tried several times and each time the combonation will only calculate the first part or give me #VALUE!

This formula is going into B19:

I need to take B16/30 if that cell has a value, if not then I need B17/30.

If there errors in the above cells (same as we worked out before, if there are no values from the months above the cell shows "N/A") I also need B19 to show "N/A"

Attached is the new updated workbook if it helps?

That did the trick. I was trying to fix the error with ISERROR, I guess that's what was throwing me off.

THANK YOU!