Date Range

[gatortech]

I have an Excel 2000 worksheet with three adjacent columns, A, B, C.

Column A contains an arbitrary number of arbitrary dates, arranged from top to bottom in chronological ascending order
Column B contains an equal quantity of arbitrary numbers, where each number is related to one of the dates in Column A
Column C contains an equal quantity of arbitrary $ amounts, where each amount is related to the same date as that in Column B

in a separate cell, I want to be able to enter any arbitrary date which can range from the earliest date in column A to any later date up to "today's" date. If a date earlier than the earliest date in column A is entered, the results returned should say "False"

I want to compare that entered date with the dates in Column A, so that all the numbers in Column B associated with dates in Column A which are equal to or earlier than the entered date will return a number equal to the average of those numbers.

And also so that all the $ amounts in Column C associated with dates in Column A which are equal to or earlier than the entered date will return a $ amount equal to the average of those $ amounts.

thanks for any help on this one

[dilipandey]

Hi gatortech,

Welcome to the forum.

It will be better for forum to understand and give a quick try if you post a sample file with example. While replying, click on "Go Advanced" and look for paper clip icon to upload the file. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[tigeravatar]

gatortech,

Welcome to the forum!
The below formulas use the following assumptions:
Your data has headers in row 1, so the actual data starts on row 2
The data range is from row 2 to row 100 (when you use the formula, adjust ranges to suit).
The entered date is in cell F2

To get the Average #'s in column B:

Please Login or Register  to view this content.

to get the Average $'s in column C:

Please Login or Register  to view this content.

[gatortech]

as per suggestion, I uploaded an example file yesterday, yet I see nothing of it here on forum.

Might well be because I have no clue about uploading files, even though the process did appear to work.

[gatortech]

In response to tiger avatar, the formulas appear to work correctly, BUT only when the "arbitrary date happens to be one of the dates in date column. If I select any other date either in between the dates in column or exceeding the latest date, I get the error #N/A.

[tigeravatar]

gatortech,

Made a slight update to the formulas: Changed $A$2:$A$100=$F$2 to $A$2:$A$100<=$F$2
Again, adjust ranges to suit

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[gatortech]

tigeravatar,

Those formulas do work as desired; however, I was wondering what could be done about the range in the formula, because if I don't change the range to agree with the actual range, I get a wrong answer. The number of DATE entries will increase over time, so I would like to be able to set the formula DATE range to something above where it currently works successfully. Basically I want to be free to change the DATE range whenever I need to, without impacting results.

thank you

gatortech

[tigeravatar]

I would recommend you use a dynamic named range. Here are some links for more information:

http://support.microsoft.com/kb/830287
http://www.contextures.com/xlnames01.html#Dynamic
http://office.microsoft.com/en-us/ex...001126115.aspx

[gatortech]

This is second time a post of mine disappeared, so I'll try again.

Tigeravatar, I appreciate all you have done, but as to your last suggestion to use a dynamic named range, I haven't a clue how I would use the information in your references to either modify or replace your formula which works fine except for the inability to have a dynamic date range.

Would you please show me how this can be done.

thank you

gatortech

[tigeravatar]

gatortech,

Attached is an example workbook based on the criteria described. It has a dynamic named range named rngDate which is defined using this formula:

Please Login or Register  to view this content.

In cell F3 (average #'s) is this formula:

Please Login or Register  to view this content.

In cell F4 (average $'s) is this formula:

Please Login or Register  to view this content.

If you add new rows of data to the end, rngDate will pick them and the formula will calculate with the new data.

[gatortech]

It tried adding dates to beginning, and that doesn't work (FALSE)

Suppose I clear out all of the data in columns A,B,C, but leave formulas intact? I guess I'm asking what can I move where, and still be functional?

[tigeravatar]

the dynamic named range is column A, from A2 to the end of populated cells in column A. as long as you don't insert a row above A2, that will stay as is. If you need to add earlier dates, I would recommend adding them at the end of column A (along with their associated data for columns B and C), and then sorting by date to get them to the top.

Really experience is the best teacher. You could delete all the data in columns A, B, and C and put in your own data and see how the named range picks up the new entries automatically.

[gatortech]

Thanks to tigeravatar for lots of patience in solving this one for me.

gatortech