| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MAXIF?
I can't figure out this array formula - can anyone assist?
Assume it is column A, B, C and row 2 starts the info I am reviewing. I want to find the maximum date for any value in column b that has a value greater than zero. The issue I am having is there are multiple months and multiple people so a "SUMIF" type of formula would work but I can't figure out the formula to do a "MAXIF" type of function. Any help would be greatly appreciated. Thx Carrie Example data: Sum per line unique Date AnthonyJan-08 0.0 01/01/08 AnthonyJan-08 0.0 01/02/08 AnthonyJan-08 0.0 01/03/08 AnthonyJan-08 0.0 01/04/08 AnthonyJan-08 0.0 01/05/08 AnthonyJan-08 0.0 01/06/08 AnthonyJan-08 0.0 01/07/08 AnthonyJan-08 0.0 01/08/08 AnthonyJan-08 0.0 01/09/08 AnthonyJan-08 0.0 01/10/08 AnthonyJan-08 0.0 01/11/08 AnthonyJan-08 0.0 01/12/08 AnthonyJan-08 0.0 01/13/08 AnthonyJan-08 0.0 01/14/08 AnthonyJan-08 0.0 01/15/08 AnthonyJan-08 0.0 01/16/08 AnthonyJan-08 2.0 01/17/08 AnthonyJan-08 2.0 01/18/08 AnthonyJan-08 2.0 01/19/08 AnthonyJan-08 2.0 01/20/08 AnthonyJan-08 2.0 01/21/08 AnthonyJan-08 2.0 01/22/08 AnthonyJan-08 0.0 01/23/08 AnthonyJan-08 0.0 01/24/08 AnthonyJan-08 2.0 01/25/08 AnthonyJan-08 2.0 01/26/08 AnthonyJan-08 2.0 01/27/08 AnthonyJan-08 2.0 01/28/08 AnthonyJan-08 0.0 01/29/08 AnthonyJan-08 0.0 01/30/08 AnthonyJan-08 0.0 01/31/08 AnthonyFeb-08 2.0 02/01/08 AnthonyFeb-08 0.0 02/02/08 AnthonyFeb-08 0.0 02/03/08 AnthonyFeb-08 2.0 02/04/08 AnthonyFeb-08 2.0 02/05/08 AnthonyFeb-08 0.0 02/06/08 AnthonyFeb-08 2.0 02/07/08 AnthonyFeb-08 2.0 02/08/08 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200809/1 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAXIF?
Try this array-entered** formula...
=MAX(IF(B2:B100<0,C2:C100,"")) **Commit the formula with Ctrl+Shift+Enter, not just Enter by itself. Change the upper end of the ranges from 100 to the maximum row number that will ever contain data. -- Rick (MVP - Excel) "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:8a94bcacb1ec5@uwe... I can't figure out this array formula - can anyone assist? Assume it is column A, B, C and row 2 starts the info I am reviewing. I want to find the maximum date for any value in column b that has a value greater than zero. The issue I am having is there are multiple months and multiple people so a "SUMIF" type of formula would work but I can't figure out the formula to do a "MAXIF" type of function. Any help would be greatly appreciated. Thx Carrie Example data: Sum per line unique Date AnthonyJan-08 0.0 01/01/08 AnthonyJan-08 0.0 01/02/08 AnthonyJan-08 0.0 01/03/08 AnthonyJan-08 0.0 01/04/08 AnthonyJan-08 0.0 01/05/08 AnthonyJan-08 0.0 01/06/08 AnthonyJan-08 0.0 01/07/08 AnthonyJan-08 0.0 01/08/08 AnthonyJan-08 0.0 01/09/08 AnthonyJan-08 0.0 01/10/08 AnthonyJan-08 0.0 01/11/08 AnthonyJan-08 0.0 01/12/08 AnthonyJan-08 0.0 01/13/08 AnthonyJan-08 0.0 01/14/08 AnthonyJan-08 0.0 01/15/08 AnthonyJan-08 0.0 01/16/08 AnthonyJan-08 2.0 01/17/08 AnthonyJan-08 2.0 01/18/08 AnthonyJan-08 2.0 01/19/08 AnthonyJan-08 2.0 01/20/08 AnthonyJan-08 2.0 01/21/08 AnthonyJan-08 2.0 01/22/08 AnthonyJan-08 0.0 01/23/08 AnthonyJan-08 0.0 01/24/08 AnthonyJan-08 2.0 01/25/08 AnthonyJan-08 2.0 01/26/08 AnthonyJan-08 2.0 01/27/08 AnthonyJan-08 2.0 01/28/08 AnthonyJan-08 0.0 01/29/08 AnthonyJan-08 0.0 01/30/08 AnthonyJan-08 0.0 01/31/08 AnthonyFeb-08 2.0 02/01/08 AnthonyFeb-08 0.0 02/02/08 AnthonyFeb-08 0.0 02/03/08 AnthonyFeb-08 2.0 02/04/08 AnthonyFeb-08 2.0 02/05/08 AnthonyFeb-08 0.0 02/06/08 AnthonyFeb-08 2.0 02/07/08 AnthonyFeb-08 2.0 02/08/08 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200809/1 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAXIF?
Thanks but my issue is to try and get the max date in the range only for
those unique instances. Example: any that are labeled "AnthonyJan-08" then another max for any that are labeled "AnthonyFeb-08". With a SUMIF I can point to a cell and define the range to look in by that cell value. Secondly, since my range cycles through different people and all the calendar months I was hoping not to have to rewrite the formula over and over again like a SUMIF. Is it possible? Rick Rothstein wrote: Try this array-entered** formula... =MAX(IF(B2:B100<0,C2:C100,"")) **Commit the formula with Ctrl+Shift+Enter, not just Enter by itself. Change the upper end of the ranges from 100 to the maximum row number that will ever contain data. I can't figure out this array formula - can anyone assist? [quoted text clipped - 49 lines] AnthonyFeb-08 2.0 02/07/08 AnthonyFeb-08 2.0 02/08/08 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200809/1 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAXIF?
It is hard to know from your postings if you know in advance all the
possible entry values in Column A or not. Assuming you don't, will this be acceptable? Place this formula... =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",MAX(IF((B$ 2:B$100<0)*(A$2:A$100=A2),C$2:C$100,"")))) In Row 2 of some unused column and copy it down... the first occurrence of a value in Column A will show the maximum date you are looking for. -- Rick (MVP - Excel) "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:8a95b0252cffd@uwe... Thanks but my issue is to try and get the max date in the range only for those unique instances. Example: any that are labeled "AnthonyJan-08" then another max for any that are labeled "AnthonyFeb-08". With a SUMIF I can point to a cell and define the range to look in by that cell value. Secondly, since my range cycles through different people and all the calendar months I was hoping not to have to rewrite the formula over and over again like a SUMIF. Is it possible? Rick Rothstein wrote: Try this array-entered** formula... =MAX(IF(B2:B100<0,C2:C100,"")) **Commit the formula with Ctrl+Shift+Enter, not just Enter by itself. Change the upper end of the ranges from 100 to the maximum row number that will ever contain data. I can't figure out this array formula - can anyone assist? [quoted text clipped - 49 lines] AnthonyFeb-08 2.0 02/07/08 AnthonyFeb-08 2.0 02/08/08 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200809/1 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAXIF?
Try this (assumes your data is sorted by column A):
In D2 enter =IF(A2=A1,D1,D1+1) and copy down. In E2 enter =MAX(IF(($B$2:$B$400)*($D$2:$D$40=D2),$C$2:$C$40, 0)) and copy down. THis is an array formula, so instead os Enter you need to pres Ctrl+Shift+Enter to commit the formula. In F2 enter =IF(D2<D1,E2,"") and copy down. Hope this helps, Hutch "Carrie_Loos via OfficeKB.com" wrote: Thanks but my issue is to try and get the max date in the range only for those unique instances. Example: any that are labeled "AnthonyJan-08" then another max for any that are labeled "AnthonyFeb-08". With a SUMIF I can point to a cell and define the range to look in by that cell value. Secondly, since my range cycles through different people and all the calendar months I was hoping not to have to rewrite the formula over and over again like a SUMIF. Is it possible? Rick Rothstein wrote: Try this array-entered** formula... =MAX(IF(B2:B100<0,C2:C100,"")) **Commit the formula with Ctrl+Shift+Enter, not just Enter by itself. Change the upper end of the ranges from 100 to the maximum row number that will ever contain data. I can't figure out this array formula - can anyone assist? [quoted text clipped - 49 lines] AnthonyFeb-08 2.0 02/07/08 AnthonyFeb-08 2.0 02/08/08 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200809/1 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAXIF?
Thank you - Both ideas in the post worked well and gave me some terrific
ideas for other data I need to pull in this excercise. I love to learn new things! Rick Rothstein wrote: It is hard to know from your postings if you know in advance all the possible entry values in Column A or not. Assuming you don't, will this be acceptable? Place this formula... =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",MAX(IF((B $2:B$100<0)*(A$2:A$100=A2),C$2:C$100,"")))) In Row 2 of some unused column and copy it down... the first occurrence of a value in Column A will show the maximum date you are looking for. Thanks but my issue is to try and get the max date in the range only for those unique instances. Example: any that are labeled "AnthonyJan-08" then [quoted text clipped - 20 lines] AnthonyFeb-08 2.0 02/07/08 AnthonyFeb-08 2.0 02/08/08 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200809/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Maxif equivalent | Excel Worksheet Functions | |||
| How to do a MAXIF formula | Excel Worksheet Functions | |||
| how can i check for #Value! ? ... or how do I do MaxIf?? | Excel Worksheet Functions | |||
| Minif / Maxif ? | Excel Worksheet Functions | |||
| maxif | Excel Worksheet Functions | |||
Linear Mode
