I have a workbook where on the tab "Data"

tab "Data" Column A, Cells A2:A2500 Gives a Year Value (for example, 2007,
2008)

tab "Data" Column D, Cells D2:D2500 Gives a specific Date (for example Feb
1, 2007, Feb 18, 2007)

tab "Data" Column C, Cells C2:C2500 Subtracts the Value of the date from the
current line from the date of the line previous (=D4-D3) and returns a number
value.

tab "Data" Column G, Cells G2:G2500 gives a the department of each record.
(For example "Admin".


What I am trying to do is in cell C20 of the "Frequencies" tab, I would like
to go back and find the largest number in "Data" worksheet, cells C2:C2500,
where the year is 2007, and returns the highest value in that range.

Could anyone please give me a hand with this?

Thank you,
Gina

Try this array* formula:

=MAX(IF(Data!A2:A2500=2007,Data!C2:C2500))

*An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter. If you do this correctly then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - do not type these yourself. If you need to amend/edit the
formula then use CSE again.

Hope this helps.

Pete

On Jul 31, 7:31*pm, Gina wrote:
I have a workbook where on the tab "Data"

tab "Data" Column A, Cells A2:A2500 Gives a Year Value (for example, 2007,
2008)

tab "Data" Column D, Cells D2:D2500 Gives a specific Date (for example Feb
1, 2007, Feb 18, 2007)

tab "Data" Column C, Cells C2:C2500 Subtracts the Value of the date from the
current line from the date of the line previous (=D4-D3) and returns a number
value. *

tab "Data" Column G, Cells G2:G2500 gives a the department of each record.. *
(For example "Admin".

What I am trying to do is in cell C20 of the "Frequencies" tab, I would like
to go back and find the largest number in *"Data" worksheet, cells C2:C2500,
where the year is 2007, and returns the highest value in that range. *

Could anyone please give me a hand with this?

Thank you,
Gina

Modifying a formula by yshridhar on another post.

C20: =LARGE(IF((Data!A2:A2500=2007),Data!C2:C2500),1)

this is an array formula, and therefore instead of pressing enter, you must
press and hold CTRL+Shift+enter when entering the formula.

--
John C


"Gina" wrote:

I have a workbook where on the tab "Data"

tab "Data" Column A, Cells A2:A2500 Gives a Year Value (for example, 2007,
2008)

tab "Data" Column D, Cells D2:D2500 Gives a specific Date (for example Feb
1, 2007, Feb 18, 2007)

tab "Data" Column C, Cells C2:C2500 Subtracts the Value of the date from the
current line from the date of the line previous (=D4-D3) and returns a number
value.

tab "Data" Column G, Cells G2:G2500 gives a the department of each record.
(For example "Admin".


What I am trying to do is in cell C20 of the "Frequencies" tab, I would like
to go back and find the largest number in "Data" worksheet, cells C2:C2500,
where the year is 2007, and returns the highest value in that range.

Could anyone please give me a hand with this?

Thank you,
Gina

these worked great. i didn't understand what you meant originally,
ctrl/shift/enter, but I figured it out and it is exactly what I needed.
Thank you so much.
Gina

"Pete_UK" wrote:

Try this array* formula:

=MAX(IF(Data!A2:A2500=2007,Data!C2:C2500))

*An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter. If you do this correctly then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - do not type these yourself. If you need to amend/edit the
formula then use CSE again.

Hope this helps.

Pete

On Jul 31, 7:31 pm, Gina wrote:
I have a workbook where on the tab "Data"

tab "Data" Column A, Cells A2:A2500 Gives a Year Value (for example, 2007,
2008)

tab "Data" Column D, Cells D2:D2500 Gives a specific Date (for example Feb
1, 2007, Feb 18, 2007)

tab "Data" Column C, Cells C2:C2500 Subtracts the Value of the date from the
current line from the date of the line previous (=D4-D3) and returns a number
value.

tab "Data" Column G, Cells G2:G2500 gives a the department of each record..
(For example "Admin".

What I am trying to do is in cell C20 of the "Frequencies" tab, I would like
to go back and find the largest number in "Data" worksheet, cells C2:C2500,
where the year is 2007, and returns the highest value in that range.

Could anyone please give me a hand with this?

Thank you,
Gina

This worked great! Thank you again.

If I wanted to add a further filter to show only when the department was
"Admin", which is Data!G2:G2500=Admin, would I just add it to the formula
like:

=MAX(IF(Data!A2:A2500=2007,Data!C2:C2500,Data!G2:G 2500=Admin))

?

Thanks!

"Pete_UK" wrote:

Try this array* formula:

=MAX(IF(Data!A2:A2500=2007,Data!C2:C2500))

*An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter. If you do this correctly then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - do not type these yourself. If you need to amend/edit the
formula then use CSE again.

Hope this helps.

Pete

On Jul 31, 7:31 pm, Gina wrote:
I have a workbook where on the tab "Data"

tab "Data" Column A, Cells A2:A2500 Gives a Year Value (for example, 2007,
2008)

tab "Data" Column D, Cells D2:D2500 Gives a specific Date (for example Feb
1, 2007, Feb 18, 2007)

tab "Data" Column C, Cells C2:C2500 Subtracts the Value of the date from the
current line from the date of the line previous (=D4-D3) and returns a number
value.

tab "Data" Column G, Cells G2:G2500 gives a the department of each record..
(For example "Admin".

What I am trying to do is in cell C20 of the "Frequencies" tab, I would like
to go back and find the largest number in "Data" worksheet, cells C2:C2500,
where the year is 2007, and returns the highest value in that range.

Could anyone please give me a hand with this?

Thank you,
Gina

You're welcome, Gina - thanks for feeding back.

Pete

On Jul 31, 10:18*pm, Gina wrote:
these worked great. *i didn't understand what you meant originally,
ctrl/shift/enter, but I figured it out and it is exactly what I needed.
Thank you so much.
Gina

No, you would need to do it like this:

=MAX(IF((Data!A2:A2500=2007)*(Data!G2:G2500="Admin "),Data!C2:C2500))

Again, commit with CSE.

Hope this helps.

Pete

On Jul 31, 10:21*pm, Gina wrote:
This worked great! *Thank you again. *

If I wanted to add a further filter to show only when the department was
"Admin", which is Data!G2:G2500=Admin, would I just add it to the formula
like:

=MAX(IF(Data!A2:A2500=2007,Data!C2:C2500,Data!G2:G 2500=Admin))

?

Thanks!



"Pete_UK" wrote:
Try this array* formula:

=MAX(IF(Data!A2:A2500=2007,Data!C2:C2500))

*An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter. If you do this correctly then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - do not type these yourself. If you need to amend/edit the
formula then use CSE again.

Hope this helps.

Pete

On Jul 31, 7:31 pm, Gina wrote:
I have a workbook where on the tab "Data"

tab "Data" Column A, Cells A2:A2500 Gives a Year Value (for example, 2007,
2008)

tab "Data" Column D, Cells D2:D2500 Gives a specific Date (for example Feb
1, 2007, Feb 18, 2007)

tab "Data" Column C, Cells C2:C2500 Subtracts the Value of the date from the
current line from the date of the line previous (=D4-D3) and returns a number
value. *

tab "Data" Column G, Cells G2:G2500 gives a the department of each record.. *
(For example "Admin".

What I am trying to do is in cell C20 of the "Frequencies" tab, I would like
to go back and find the largest number in *"Data" worksheet, cells C2:C2500,
where the year is 2007, and returns the highest value in that range. *

Could anyone please give me a hand with this?

Thank you,
Gina- Hide quoted text -

- Show quoted text -