hi all, please help

A B
--------- ----------
date item
--------- ----------
02/14/04 azm
07/22/05 naf
11/21/05 naf
03/26/06 naf
04/18/07 naf
04/20/07 azm
05/19/07 azm
06/08/07 ktm
08/22/07 ktm

i like to find the number of days between max and min dates for a given item
in column B

This is an array formula that must be entered using ctrl+shift+enter vs just
enter. ans=1190
=MAX(IF(B6:B14="azm",A6:A14))-MIN(IF(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
hi all, please help

A B
--------- ----------
date item
--------- ----------
02/14/04 azm
07/22/05 naf
11/21/05 naf
03/26/06 naf
04/18/07 naf
04/20/07 azm
05/19/07 azm
06/08/07 ktm
08/22/07 ktm

i like to find the number of days between max and min dates for a given
item
in column B

Assume source data within A2:B10, dates in A2:A10, items in B2:B10
Assume unique items are listed in D2 down, viz: azm, naf, etc
In E2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=MAX(IF(B$2:B$10=$D2,A$2:A$10))-MIN(IF(B$2:B$10=D2,A$2:A$10))
Format E2 as general/number. Copy down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"excelFan" wrote:
hi all, please help

A B
--------- ----------
date item
--------- ----------
02/14/04 azm
07/22/05 naf
11/21/05 naf
03/26/06 naf
04/18/07 naf
04/20/07 azm
05/19/07 azm
06/08/07 ktm
08/22/07 ktm

i like to find the number of days between max and min dates for a given item
in column B

Works fine Don,
in my case our subject (number of days) is just a part of a bigger formula,
how to overcome using an arry formula. Thanks

"Don Guillett" wrote:

This is an array formula that must be entered using ctrl+shift+enter vs just
enter. ans=1190
=MAX(IF(B6:B14="azm",A6:A14))-MIN(IF(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
hi all, please help

A B
--------- ----------
date item
--------- ----------
02/14/04 azm
07/22/05 naf
11/21/05 naf
03/26/06 naf
04/18/07 naf
04/20/07 azm
05/19/07 azm
06/08/07 ktm
08/22/07 ktm

i like to find the number of days between max and min dates for a given
item
in column B

Works fine Don,
in my case our subject (number of days) is just a part of a bigger
formula, how to overcome using an arry formula.

Give this non-array formula a try...

=SUMPRODUCT(MAX((B2:B10=C2)*(A2:A10)))+SUMPRODUCT( MAX((B2:B10=C2)*(99999-A2:A10)))-99999

where I assumed the dates started in A2, the code items in B2 and the
specified code item you wanted to calculate the date difference for in C2.

Rick

"Rick Rothstein (MVP - VB)" wrote:

Works fine Don,
in my case our subject (number of days) is just a part of a bigger
formula, how to overcome using an arry formula.

Thanks Rick,
just perfect that's does it, many thanks again

Give this non-array formula a try...

=SUMPRODUCT(MAX((B2:B10=C2)*(A2:A10)))+SUMPRODUCT( MAX((B2:B10=C2)*(99999-A2:A10)))-99999

where I assumed the dates started in A2, the code items in B2 and the
specified code item you wanted to calculate the date difference for in C2.

Rick

Try this. Kind of tricky in that the max and min are DIFFERENT.
=SUMPRODUCT(MAX((B6:B14="azm")*(A6:A14)))-SUMPRODUCT(MIN(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
Works fine Don,
in my case our subject (number of days) is just a part of a bigger
formula,
how to overcome using an arry formula. Thanks

"Don Guillett" wrote:

This is an array formula that must be entered using ctrl+shift+enter vs
just
enter. ans=1190
=MAX(IF(B6:B14="azm",A6:A14))-MIN(IF(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
hi all, please help

A B
--------- ----------
date item
--------- ----------
02/14/04 azm
07/22/05 naf
11/21/05 naf
03/26/06 naf
04/18/07 naf
04/20/07 azm
05/19/07 azm
06/08/07 ktm
08/22/07 ktm

i like to find the number of days between max and min dates for a given
item
in column B

I do not get the 'min' part of your formula to produce the correct result.
Yes, for the data given and for item "azm" it works, but that appears to be
because of the particular layout of the data. Try changing "azm" to "naf" to
see the problem.

Rick


"Don Guillett" wrote in message
...
Try this. Kind of tricky in that the max and min are DIFFERENT.
=SUMPRODUCT(MAX((B6:B14="azm")*(A6:A14)))-SUMPRODUCT(MIN(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
Works fine Don,
in my case our subject (number of days) is just a part of a bigger
formula,
how to overcome using an arry formula. Thanks

"Don Guillett" wrote:

This is an array formula that must be entered using ctrl+shift+enter vs
just
enter. ans=1190
=MAX(IF(B6:B14="azm",A6:A14))-MIN(IF(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
hi all, please help

A B
--------- ----------
date item
--------- ----------
02/14/04 azm
07/22/05 naf
11/21/05 naf
03/26/06 naf
04/18/07 naf
04/20/07 azm
05/19/07 azm
06/08/07 ktm
08/22/07 ktm

i like to find the number of days between max and min dates for a
given
item
in column B

Actually, I wondered why it would work. Sometimes I forget to FULLY test.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
I do not get the 'min' part of your formula to produce the correct result.
Yes, for the data given and for item "azm" it works, but that appears to be
because of the particular layout of the data. Try changing "azm" to "naf"
to see the problem.

Rick


"Don Guillett" wrote in message
...
Try this. Kind of tricky in that the max and min are DIFFERENT.
=SUMPRODUCT(MAX((B6:B14="azm")*(A6:A14)))-SUMPRODUCT(MIN(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
Works fine Don,
in my case our subject (number of days) is just a part of a bigger
formula,
how to overcome using an arry formula. Thanks

"Don Guillett" wrote:

This is an array formula that must be entered using ctrl+shift+enter vs
just
enter. ans=1190
=MAX(IF(B6:B14="azm",A6:A14))-MIN(IF(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
hi all, please help

A B
--------- ----------
date item
--------- ----------
02/14/04 azm
07/22/05 naf
11/21/05 naf
03/26/06 naf
04/18/07 naf
04/20/07 azm
05/19/07 azm
06/08/07 ktm
08/22/07 ktm

i like to find the number of days between max and min dates for a
given
item
in column B

many thanks Don,
this formula does not yield an ok numbers for the given data , may be
because of the min part of it , so please fully test and revert.

"Don Guillett" wrote:

Try this. Kind of tricky in that the max and min are DIFFERENT.
=SUMPRODUCT(MAX((B6:B14="azm")*(A6:A14)))-SUMPRODUCT(MIN(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
Works fine Don,
in my case our subject (number of days) is just a part of a bigger
formula,
how to overcome using an arry formula. Thanks

"Don Guillett" wrote:

This is an array formula that must be entered using ctrl+shift+enter vs
just
enter. ans=1190
=MAX(IF(B6:B14="azm",A6:A14))-MIN(IF(B6:B14="azm",A6:A14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelFan" wrote in message
...
hi all, please help

A B
--------- ----------
date item
--------- ----------
02/14/04 azm
07/22/05 naf
11/21/05 naf
03/26/06 naf
04/18/07 naf
04/20/07 azm
05/19/07 azm
06/08/07 ktm
08/22/07 ktm

i like to find the number of days between max and min dates for a given
item
in column B

Just wondering aloud why array-entering your combined formula (ie after
combining the original array suggested into your other, presumably non-array
formula) didn't work for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

galdly yes, there is a non array formula solved my issue, please refer to
Rick Rothstien's suggested non array formula above, it's just great
excelFan

"Max" wrote:

Just wondering aloud why array-entering your combined formula (ie after
combining the original array suggested into your other, presumably non-array
formula) didn't work for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---