MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Please Help Me!!!


Posted by Nikki on July 19, 2000 7:24 PM

HELP!!!!

I am in deep. My boss thinks I know Excel!!

Is there any way to paste a formula in row D that will return the average of a list of data that is in column A. The trick is that there is a long list of data in A and I need the formula to return the average of the data between the numbers entered into cells b1 & c1, etc. I figured out I need the range between the data but I can't make it work. Can anyone help me??? Please!! Thank You so much. Nikki ;)


Posted by Ada on July 20, 0100 12:37 AM

Nikki
Check whether this is what you need :-

=STDEV(IF((A1:A10000>B1)*(A1:A10000<C1),F1:F10000))

Ada

Posted by Ada on July 20, 0100 12:38 AM

Didn't post properly again.
Have a look at the Comments box.
Ada

Posted by Ada on July 19, 0100 7:46 PM


Nikki
Put the following array formula (Ctrl+Shift+Enter) in cell D1.
Please advise if this is not what you're looking for.

=AVERAGE(IF((A1:A10000>B1)*(A1:A10000<C1),A1:A10000))

Ada

Posted by Ada on July 19, 0100 7:49 PM

For some reason it didn't post properly. Have a look at the Comments box where it is shown properly
Ada

Posted by Nikki on July 19, 0100 10:41 PM

Thanks Ada!!

It took me a while to figure it out but it seems to work perfectly. You really saved me this time. I guess I better learn about these array formulas. If you happen to know off hand, is there also a formula method to have the same array specified by the numbers in column A also perform a function on the same row numbers but using the data in another column? Say the array that gets averaged in column A also has some matching data in column F and I would like the formula in column G to give the STDEV of the numbers in column F but still using the array that you designed to also specify the range for column F...(if you can follow this). I think it might be the offset function but no luck so far. Regardless, I really appriciate the help with the average function. Thanks a bunch. Nikki