| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if search formula incl date range
I need to write a formula to do the following and it's just feeling way over
my head. I know I need an "if" at the beginning but then I get lost. Can anyone help? Col A Col B Col C Col D Date $Amt Type Text Code If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if search formula incl date range
Oops - forgot a criteria:
If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1, ONLY SEARCHING the rows where the Text Code is equal to the Text Code in row 1. "Laury" wrote: I need to write a formula to do the following and it's just feeling way over my head. I know I need an "if" at the beginning but then I get lost. Can anyone help? Col A Col B Col C Col D Date $Amt Type Text Code If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if search formula incl date range
What I have working so far:
=IF(C2="MCD",MIN(IF($D$2:$D$13="1001",$B$2:$B$13)) ," ") When I try to add an AND statement to make the min calculate on rows that meet 2 criteria, it's not working: =IF(C4="MCD",MIN(IF(AND($D$2:$D$13="1001",$C$2:$C$ 13="Private"),$B$2:$B$13))," ") "Laury" wrote: Oops - forgot a criteria: If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1, ONLY SEARCHING the rows where the Text Code is equal to the Text Code in row 1. "Laury" wrote: I need to write a formula to do the following and it's just feeling way over my head. I know I need an "if" at the beginning but then I get lost. Can anyone help? Col A Col B Col C Col D Date $Amt Type Text Code If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if search formula incl date range
Almost there but still need help on the date range part:
=IF(C2="MCD",MIN(IF(($D$2:$D$13="1001")*($C$2:$C$1 3<"MCD"),$B$2:$B$13))," ") How do I add in a 3rd criteria that says pick the min amount that meets the 1st two criteria AND is within 30 days (before or after) the date on row 1? "Laury" wrote: What I have working so far: =IF(C2="MCD",MIN(IF($D$2:$D$13="1001",$B$2:$B$13)) ," ") When I try to add an AND statement to make the min calculate on rows that meet 2 criteria, it's not working: =IF(C4="MCD",MIN(IF(AND($D$2:$D$13="1001",$C$2:$C$ 13="Private"),$B$2:$B$13))," ") "Laury" wrote: Oops - forgot a criteria: If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1, ONLY SEARCHING the rows where the Text Code is equal to the Text Code in row 1. "Laury" wrote: I need to write a formula to do the following and it's just feeling way over my head. I know I need an "if" at the beginning but then I get lost. Can anyone help? Col A Col B Col C Col D Date $Amt Type Text Code If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if search formula incl date range
I got it working but thanks!
=IF(C2="MCD",MIN(IF(($D$2:$D$13=D2)*((A2-($A$2:$A$13))<31)*($C$2:$C$13<"MCD"),IF(($D$2:$D$ 13=D2)*((($A$2:$A$13)-A2)<31)*($C$2:$C$13<"MCD"),$B$2:$B$13," ")," "))," ") "Laury" wrote: What I have working so far: =IF(C2="MCD",MIN(IF($D$2:$D$13="1001",$B$2:$B$13)) ," ") When I try to add an AND statement to make the min calculate on rows that meet 2 criteria, it's not working: =IF(C4="MCD",MIN(IF(AND($D$2:$D$13="1001",$C$2:$C$ 13="Private"),$B$2:$B$13))," ") "Laury" wrote: Oops - forgot a criteria: If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1, ONLY SEARCHING the rows where the Text Code is equal to the Text Code in row 1. "Laury" wrote: I need to write a formula to do the following and it's just feeling way over my head. I know I need an "if" at the beginning but then I get lost. Can anyone help? Col A Col B Col C Col D Date $Amt Type Text Code If the type in row 1 = X, find the minimum $Amt from all the rows where Type=Y or Z, AND Date is within 30 days of the Date in row 1. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
| Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
| If formula for date range | Excel Discussion (Misc queries) | |||
| how do i use the sum if formula with a date range? | Excel Worksheet Functions | |||
Linear Mode
