Average Formula but does not include blank fields

[Travksallen2007]

I need a formula that shows the average of c6+d6+F6+g6+i6+j6+l6+m6+o6+p6+r6+s6+u6+v6 BUT if the field is blank it does not include in the calculation in y6

[Glenn Kennedy]

?? Blanks are ignored.

=AVERAGE(C6:D6,F6:G6,I6:J6,L6:M6,O6:P6,R6:S6,U6:V6)

[Travksallen2007]

Yes, for example: O6:V6 has no data so that formula calculates that as a 0 but its not 0 as the data has just not occurred yet - Daily pull of info

[Glenn Kennedy]

I don't understand. In the file I uploaded, the blank values were not counted in calculating the average. The average of 1, 2 and a pile of blanks is 1.5

SHOW me in a sample file what is going wrong.

[Travksallen2007]

ok i figured out what i was explaining wrong. I have three data points divided by day. I need the formula to add the three data points together but should only count that as ONE days worth of data. Right now its trying to divide by 21 instead of 7. i think i got the attachment to work

[Glenn Kennedy]

Please remove the password protection and repost. Where is the formula to be found?

[Travksallen2007]

Z6:Z21 is where the formula should be found. Sorry about the PW - thought i took that off

[Glenn Kennedy]

Your sheet does not tally with your explanation. The formula is averaging REGULAR hours. If that's what you want... and you want to ignore blanks and zeros (your explanation is FAR from clear!!!), you need:

=AVERAGEIFS(D6:X6,$D$5:$X$5,"Regular",D6:X6,"<>0")

copied down.

[Travksallen2007]

Ill try and explain a different way -

FRIDAY
Regular MANAGER SHIFT SMART
25 25 25

I need these three numbers added together and count as one days worth of data = 75 for Friday.

SATURDAY
REGULAR MANAGER SHIFT SMART
25 25 25
=75

SUNDAY
REGULAR MANAGER SHIFT SMART
NO DATA NO DATA NO DATA


Average should be (75+75)/2=75. Its currently doing 75/6 as there are 6 data points.

(D6:F6)+(G6:I6) ect. all the way through V6:X6 added together divided by 7 maximum and less if there is no data in the cells.

I do have it listed currently as just regular time as I could not figure out how to get this formula to work.

[MrShorty]

I don't see that scenario shown anywhere in your sample sheet. (I've added it in row 21).

If this were me, I think I would add a column to each day, and have that sum the other three values for that day. =IF(SUM(D6:F6)=0,"",SUM(D6:F6)) in a newly inserted G6. Repeat for the other 7 days of the week. Then, this average formula would be =AVERAGE(G6, K6, O6,...) [see column AI].

Would you be allowed to insert that helper column into each days block of cells, or would such a thing be forbidden?