Need help with conditional formula

[Eviloliv3]

I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.

The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.

im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...

this is the formula I have now, obviously it doesnt work

=IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))

Little help please!

[mdbct]

Since you are dividing the sum based on the number of days * hours per day (7.5 in your example), you can change your formula to this:

Please Login or Register  to view this content.

[Eviloliv3]

Since you are dividing the sum based on the number of days * hours per day (7.5 in your example), you can change your formula to this:

Please Login or Register  to view this content.

That code still doesnt work if nothing is entered in.

it still divides by 7 days.

If possible, i would like it to not count a value if nothing is entered. it doesnt seem to be doing that now

[deadlyduck]

Would this do it:

Please Login or Register  to view this content.

[Eviloliv3]

Would this do it:

Please Login or Register  to view this content.

that didnt work either

[Eviloliv3]

Im uploading a test excel sheet that has the setup like i have it.

If all days have a 5, no matter 1,2,3,4,5,6, or 7 days worked, it should still show 0.67 in the weekly total spot

[Eviloliv3]

i fixed it!!!

it was counting the linked boxes. i updated it to d9:d15 and it works now!!!

[mdbct]

The formula in I2 would be:

Please Login or Register  to view this content.