SUMPRODUCT with AVERAGE, is this possible?

[lrusso]

Hi

I am not really sure if this is possible, but I'm hoping someone can help or possibly give me another option. Just fyi though, I have no control over the setup of the datasheet. I am just creating a report from the data. The report pulls the data from the company-shared datasheet.

I have a datasheet where I need to subtract the number of days between two dates (start date and end date). This tells me how many days it took to complete each project. Then, I need to average the number of days with both a regular average and a median avg. I need the formula to not count zeros within the results that are being averaged.

My problem is that I'm not sure I can subtract and then average the result within the same formula? Do I need to use a SUMPRODUCT? and if so, how do I make it average the results?

Here is thought would work. However, the results don't come close to my manual test.

=AVERAGE(('Test Data'!$C$2:$C$65535-'Test Data'!$S$2:$S$65535))/COUNTIF('Test Data'!S2:S65535:'Test Data'!$C$2:$C$65535,">0")

Then I was assuming I could us MEDIAN to get the result as well. of course, thats not working either

Any thoughts? I'm definitely willing to try any suggestions

Thanks so much!

 

[barry houdini]

Is start date in column C and end date in S? Try

=AVERAGE(IF('Test Data'!$S$2:$S$65535-'Test Data'!$C$2:$C$65535>0,'Test Data'!S2:S65535-'Test Data'!$C$2:$C$65535))

confirmed with CTRL+SHIFT+ENTER

and do the same again with MEDIAN replacing average

 

[lrusso]

Yay!!!!!!!

IT WORKED!!!!!!! I'm so happy!!! Thank you, thank you thank you!!!!