Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT help
I need to find the difference between the maximum and minimum values
of several different groups. I have the following data: A B 1 1/1/2008 2008 2 1/15/2008 2008 3 1/31/2008 2008 4 4/1/2009 2009 5 4/21/2009 2009 6 7/1/2010 2010 7 7/14/2010 2010 8 7/26/2010 2010 I want to know what the range of dates is for each year. For example, in analyzing 2008, I want a formula that will produce a value of 30. For 2009, it should be 20. For 2010, it should be 25. My list will be very large and will not necessarily be in the right order. I tried to use a combination of "min", "max", and "sumproduct", but I couldn't get it to work. This was what I came up with. =SUMPRODUCT((A1:A500=MIN(A1:A500))*(A1:A500<=MAX( A1:A500))*(B1:B500="2008")) Any help would be very much appreciated. Kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT help
One way:
Try this array formula** : D1 = year number =MAX(IF(YEAR(A$1:A$500)=D1,A$1:A$500))-MIN(IF(YEAR(A$1:A$500)=D1,A$1:A$500)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "DoooWhat" wrote in message ... I need to find the difference between the maximum and minimum values of several different groups. I have the following data: A B 1 1/1/2008 2008 2 1/15/2008 2008 3 1/31/2008 2008 4 4/1/2009 2009 5 4/21/2009 2009 6 7/1/2010 2010 7 7/14/2010 2010 8 7/26/2010 2010 I want to know what the range of dates is for each year. For example, in analyzing 2008, I want a formula that will produce a value of 30. For 2009, it should be 20. For 2010, it should be 25. My list will be very large and will not necessarily be in the right order. I tried to use a combination of "min", "max", and "sumproduct", but I couldn't get it to work. This was what I came up with. =SUMPRODUCT((A1:A500=MIN(A1:A500))*(A1:A500<=MAX( A1:A500))*(B1:B500="2008")) Any help would be very much appreciated. Kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT help
DoooWhat,
Using your example, I would put a list of all possible years below the data (you could put it along side or on a different sheet if you want). So in cells B10:B12, I would have 2008, 2009, & 2010. Then in A10, I would enter this formula: =sumproduct((max($A$1:$A$8))*($B$1:$B$8=$B10))-sumproduct((min($A$1:$A$8))*($B$1:$B$8=$B10)) Then copy that formula down to A11 & A12. Format A10:A12 as a number instead of a date. (I did not test this formula...I'm taking an educated guess and I hope my syntax is correct) HTH, Conan "DoooWhat" wrote in message ... I need to find the difference between the maximum and minimum values of several different groups. I have the following data: A B 1 1/1/2008 2008 2 1/15/2008 2008 3 1/31/2008 2008 4 4/1/2009 2009 5 4/21/2009 2009 6 7/1/2010 2010 7 7/14/2010 2010 8 7/26/2010 2010 I want to know what the range of dates is for each year. For example, in analyzing 2008, I want a formula that will produce a value of 30. For 2009, it should be 20. For 2010, it should be 25. My list will be very large and will not necessarily be in the right order. I tried to use a combination of "min", "max", and "sumproduct", but I couldn't get it to work. This was what I came up with. =SUMPRODUCT((A1:A500=MIN(A1:A500))*(A1:A500<=MAX( A1:A500))*(B1:B500="2008")) Any help would be very much appreciated. Kevin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT help
I just used 8 cells to test (need not see the need for column B)
This array formula (use CTRL+SHIFT+ENTER to compete it) =MAX(IF(YEAR(A1:A8)=2008,A1:A8)) gives the MAX date when year is 2008 Similarly =MIN(IF(YEAR(A1:A8)=2008,A1:A8)) will give MIN value Subtract the two for range Or combine the two as =MAX(IF(YEAR(A1:A8)=2008,A1:A8)) - MIN(IF(YEAR(A1:A8)=2008,A1:A8)) to get answer in one step If you get #VALUE! you forgot CTRL+SHIFT+ENTER With 2008, 2009 etc in B1....use =MAX(IF(YEAR($A$1:$A$8)=B1,$A$1:$A$8)) - MIN(IF(YEAR($A$1:$A$8)=B1,$A$1:$A$8)) copy down to do all years -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "DoooWhat" wrote in message ... I need to find the difference between the maximum and minimum values of several different groups. I have the following data: A B 1 1/1/2008 2008 2 1/15/2008 2008 3 1/31/2008 2008 4 4/1/2009 2009 5 4/21/2009 2009 6 7/1/2010 2010 7 7/14/2010 2010 8 7/26/2010 2010 I want to know what the range of dates is for each year. For example, in analyzing 2008, I want a formula that will produce a value of 30. For 2009, it should be 20. For 2010, it should be 25. My list will be very large and will not necessarily be in the right order. I tried to use a combination of "min", "max", and "sumproduct", but I couldn't get it to work. This was what I came up with. =SUMPRODUCT((A1:A500=MIN(A1:A500))*(A1:A500<=MAX( A1:A500))*(B1:B500="2008")) Any help would be very much appreciated. Kevin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT help
I decided to work through it. Looks like this will work:
=SUMPRODUCT((MAX(($A$1:$A$8)*($B$1:$B$8=$B10))))-SUMPRODUCT(SMALL(($A$1:$A$8)*($B$1:$B$8=$B10),COUN TIF($B$1:$B$8,"<"&$B10)+1)) But a couple others came up with others that will probably work. HTH, Conan "Conan Kelly" wrote in message ... DoooWhat, Using your example, I would put a list of all possible years below the data (you could put it along side or on a different sheet if you want). So in cells B10:B12, I would have 2008, 2009, & 2010. Then in A10, I would enter this formula: =sumproduct((max($A$1:$A$8))*($B$1:$B$8=$B10))-sumproduct((min($A$1:$A$8))*($B$1:$B$8=$B10)) Then copy that formula down to A11 & A12. Format A10:A12 as a number instead of a date. (I did not test this formula...I'm taking an educated guess and I hope my syntax is correct) HTH, Conan "DoooWhat" wrote in message ... I need to find the difference between the maximum and minimum values of several different groups. I have the following data: A B 1 1/1/2008 2008 2 1/15/2008 2008 3 1/31/2008 2008 4 4/1/2009 2009 5 4/21/2009 2009 6 7/1/2010 2010 7 7/14/2010 2010 8 7/26/2010 2010 I want to know what the range of dates is for each year. For example, in analyzing 2008, I want a formula that will produce a value of 30. For 2009, it should be 20. For 2010, it should be 25. My list will be very large and will not necessarily be in the right order. I tried to use a combination of "min", "max", and "sumproduct", but I couldn't get it to work. This was what I came up with. =SUMPRODUCT((A1:A500=MIN(A1:A500))*(A1:A500<=MAX( A1:A500))*(B1:B500="2008")) Any help would be very much appreciated. Kevin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT help
Thanks so much Conan! That worked perfectly.
Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Help with SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct? | Excel Worksheet Functions | |||
sumproduct? | Excel Worksheet Functions | |||
Sumproduct ?? | Excel Worksheet Functions |