Been Tying to get column E to show amount on all days but Sunday and blank cells. Would like for it show 0 I did get it to show false on blank cells, haven't figured out how to get it to show zero or leave blank. Also file upload keeps failing this is the current formula =IF(J3<>"",$E$34/COUNT($I$3:$I$33))
column J is the date, I is the day of the week and E34 is a total.
file upload
Formula for E3 =IF(J3<>"",$E$34/COUNTIF($I$3:$I$34,1))
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Try this in E3:
=IF(OR(J3="",WEEKDAY(A3,2)=7),"",$E$34/COUNT($I$3:$I$33))
then copy down.
Hope this helps.
Pete
That does solve the 0 on Sundays but it does still counts Sunday as a day in the count line. Its still counting as 28 days when there are really only 24 workdays with out Sundays. And to learn from this formula
the WEEKDAY(A3,2)=7) part is that basically saying that the weekday starts on Monday then =7 is telling the end of the week?
The basic Average if column I > 1 (not Sunday) should be:
=$E$34/COUNTIF($I$3:$I$33,">1")
If column A was Sunday or blank then hide:
=IFERROR(IF(WEEKDAY(A3)=1,"",$E$34/COUNTIF($I$3:$I$33,">1")),"")
Quang PT
Perhaps this in E3:E33.
Formula:
Please Login or Register to view this content.
Dave
Thank you everyone for your answers. These have all answered my questions and letting me know I really Should take another excel class its been to many years.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks