LinkBack URL
About LinkBacks
Try also:Originally Posted by SOMLSS1
=IF(COUNT(B3:B14)=12,NA(),ROUNDUP(AVERAGE(B3:B14),0))
Try also:
=IF(COUNT(B3:B14)=12,NA(),ROUNDUP(AVERAGE(B3:B14),0))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I can't believe I'm still stuck on this!
I have modified the formula to:
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.=IF(COUNTIF(B3:B14,""),"N/A",ROUNDUP(AVERAGE(B3:B14),0))
How is this different than my last suggested formula?
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.
I am sorry for being confusing. I'm just trying to get this to come out looking good and functional.
I have your formula:
And mine:=IF(COUNT(B3:B14)=12,NA(),ROUNDUP(AVERAGE(B3:B14),0))
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).=IF(COUNTIF(B3:B14,""),"N/A",ROUNDUP(AVERAGE(B3:B14),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!
Try then perhaps:
=IF(OR(COUNT(B3:B14)=12,COUNTBLANK(B3:B14)=12),NA(),ROUNDUP(AVERAGE(B3:B14),0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks