help with average if another cell contains date

[roussery]

I need help with a formula please:
I need to average cells if others contains date
ex: b4 , f4, g4 has date, then I need to average b5,f5,g5.
b5,f5,g5 contains percentages. if there is no date it contains 0 and I do not need to include if there is no date.
Thanks in advance!

 

[roussery]

I am sorry I am so stupid on this.
I don't know how to attach my form, it wont let me post a screenshot.
ACTUAL DATA:
y4, ac4, ag4, ak4, ao4, as4 -May or may not have a number
z4, ad4, ah4, al4, ap4, at4 - contains a percentage
I need to average the % if there is a number in the prior cell. if no number, then do not need the 0%
EXAMPLE
y4 contains 2, z4 contains 10%
ac4 contains 0, ad4 contains 0%
ag4 contains 0, ah4 contains 0%
ak4 contains 1, al4 contains 3%
ao4 contains 5, ap4 contains 16%.
I would need It to average z4,al4,ap4

 

[AliGW]

Grab yourself the ForumTools add-in (link in my signature line) and use that.

What about the cells in between? Do they contain any data or are they blank (e.g. between Z and AC)? This could all be significant - you are only giving us a fragment of the whole picture.

 

[roussery]

I am sorry so stupid. I cannot load the form or picture
ACTUAL INFO:
ag4 contains nothing, ah4 contains 0%
ak4 contains 1, al4 contains 3%
ao4 contains 5, ap4 contains 16.67%
as4 contains nothing, at4 contains 0%
I need to average al4 and ap4. but on the next row it might be I need ah4,al4,ap4

 

[AliGW]

I'm afraid having to rebuild your worksheet from these sketchy bits of information and guesswork is too time-consuming for me. How about uploading the workbook to DropBox and providing us with a link?

 

[roussery]

there is data between the cells mentioned. dates.

 

[roussery]

co. policy will not allow drop box.

 

[AliGW]

I'm sorry. I have run out of time now, but you should provide a link to a sample workbook for anyone else interested in helping, otherwise you are just going to end up going round and round in ever-decreasing circles. I hope you get it sorted.

PS Doesn't have to be DropBox - any file-sharing site will do.

 

[Joe4]

In ready over this thread, it looks like there may be a disconnect or misunderstanding.

It looks like your examples all include non-contiguous ranges, i.e. b4, f4, g4
and Ali's solution all assume contiguous ranges, i.e. =AVERAGEIF(B4:G4,<>0,B5:G5)

So it appears to me that maybe Ali's suggested formulas are included cells that you do NOT want factored in to your average.
And based on what some of these other cells contain, that may cause errors.

 

[AliGW]

Yes, I know we are dealing with non-contiguous cells, but I had thought at first that the intervening ones might be empty. However, that does not now seem to be the case, which is why I have been asking to see some sample data, which has not, as yet, been forthcoming. Unfortunately it is now heading into evening here, and the TV schedule is beckoning. I hope you get a bit further with this than I did, Joe, and apologies to the OP for not getting to the bottom of it.

 

[Joe4]

roussery,

Is there any "rhyme or reason" regarding which cells you need to look at? Or are they just random, all over the place, where you will need to reference everyone exactly in your formula?
If it is something like every 4th cell, or something like that, that may be not be necessary.

Otherwise, for this example:

ag4 contains nothing, ah4 contains 0%
ak4 contains 1, al4 contains 3%
ao4 contains 5, ap4 contains 16.67%
as4 contains nothing, at4 contains 0%
I need to average al4 and ap4. but on the next row it might be I need ah4,al4,ap4

A formula might look something like this:

=(IF(AG4,AH4,0)+IF(AK4,AL4,0)+IF(AO4,AP4)+IF(AS4,AT4,0))/(IF(AG4,1,0)+IF(AK4,1,0)+IF(AO4,1)+IF(AS4,1,0))