Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Max and Min date from listing by account no.
Hi,
I have a list of customer account numbers each with a sales value and date e.g. columns A = AccNo, B = Sales and C = Date, there are many entries for each account no. In another sheet, I have the account number shown once and want to show alongside this the min and max date with sale i.e. columns A = AccNo, B = MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max). Do I use and array formula? Thanks, Rob |
#2
|
|||
|
|||
Hi Rob
yes, you can use the following array formuls to return the min & max dates =MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,"")) =MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,"")) -- need to format these as dates to return the min and max sales =INDEX(Sheet1!$B$1:$B$13,MATCH(A2& B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0)) where -Sheet1!$B$1:$B$13 is the sales figures on your first sheet -A2&B2 is the account number and min date on this sheet -Sheet1!$A$1:$A$13 is the account number on your first sheet -Sheet1!$C$1:$C$13 is the min date on your first sheet the formula for max sales would be =INDEX(Sheet1!$B$1:$B$13,MATCH(A2& D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0)) all of these formulas are array formuls so enter using control & shift & enter not just enter -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rob" wrote in message ... Hi, I have a list of customer account numbers each with a sales value and date e.g. columns A = AccNo, B = Sales and C = Date, there are many entries for each account no. In another sheet, I have the account number shown once and want to show alongside this the min and max date with sale i.e. columns A = AccNo, B = MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max). Do I use and array formula? Thanks, Rob |
#3
|
|||
|
|||
Thanks Julie for quick reply, I'll try this out now. Rob
"JulieD" wrote in message ... Hi Rob yes, you can use the following array formuls to return the min & max dates =MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,"")) =MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,"")) -- need to format these as dates to return the min and max sales =INDEX(Sheet1!$B$1:$B$13,MATCH(A2& B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0)) where -Sheet1!$B$1:$B$13 is the sales figures on your first sheet -A2&B2 is the account number and min date on this sheet -Sheet1!$A$1:$A$13 is the account number on your first sheet -Sheet1!$C$1:$C$13 is the min date on your first sheet the formula for max sales would be =INDEX(Sheet1!$B$1:$B$13,MATCH(A2& D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0)) all of these formulas are array formuls so enter using control & shift & enter not just enter -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Rob" wrote in message ... Hi, I have a list of customer account numbers each with a sales value and date e.g. columns A = AccNo, B = Sales and C = Date, there are many entries for each account no. In another sheet, I have the account number shown once and want to show alongside this the min and max date with sale i.e. columns A = AccNo, B = MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max). Do I use and array formula? Thanks, Rob |
#4
|
|||
|
|||
let us know how you get on
-- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rob" wrote in message ... Thanks Julie for quick reply, I'll try this out now. Rob "JulieD" wrote in message ... Hi Rob yes, you can use the following array formuls to return the min & max dates =MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,"")) =MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,"")) -- need to format these as dates to return the min and max sales =INDEX(Sheet1!$B$1:$B$13,MATCH(A2& B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0)) where -Sheet1!$B$1:$B$13 is the sales figures on your first sheet -A2&B2 is the account number and min date on this sheet -Sheet1!$A$1:$A$13 is the account number on your first sheet -Sheet1!$C$1:$C$13 is the min date on your first sheet the formula for max sales would be =INDEX(Sheet1!$B$1:$B$13,MATCH(A2& D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0)) all of these formulas are array formuls so enter using control & shift & enter not just enter -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Rob" wrote in message ... Hi, I have a list of customer account numbers each with a sales value and date e.g. columns A = AccNo, B = Sales and C = Date, there are many entries for each account no. In another sheet, I have the account number shown once and want to show alongside this the min and max date with sale i.e. columns A = AccNo, B = MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max). Do I use and array formula? Thanks, Rob |
#5
|
|||
|
|||
Thanks Julie for quick reply, I'll try this out now. Rob
"JulieD" wrote in message ... Hi Rob yes, you can use the following array formuls to return the min & max dates =MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,"")) =MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,"")) -- need to format these as dates to return the min and max sales =INDEX(Sheet1!$B$1:$B$13,MATCH(A2& B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0)) where -Sheet1!$B$1:$B$13 is the sales figures on your first sheet -A2&B2 is the account number and min date on this sheet -Sheet1!$A$1:$A$13 is the account number on your first sheet -Sheet1!$C$1:$C$13 is the min date on your first sheet the formula for max sales would be =INDEX(Sheet1!$B$1:$B$13,MATCH(A2& D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0)) all of these formulas are array formuls so enter using control & shift & enter not just enter -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Rob" wrote in message ... Hi, I have a list of customer account numbers each with a sales value and date e.g. columns A = AccNo, B = Sales and C = Date, there are many entries for each account no. In another sheet, I have the account number shown once and want to show alongside this the min and max date with sale i.e. columns A = AccNo, B = MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max). Do I use and array formula? Thanks, Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|