If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
select date in date range
I want to be able to do the following:
A B 1 26/03/2006 $20.00 2 1/12/2006 $40.00 3 1/10/2007 $100.00 4 5 6 7 28/11/2006 2 8 29/11/2006 2 I need a formula that says if the date in A7 is between date in A1 to A2, then muliply B7 by B1, else if the date in A7 is between date in A2 to A3, then multiply B7 by B2, and so on. |
#2
|
|||
|
|||
select date in date range
Assuming the dates in A1:A3 are in chrono order (as posted)
Put in C1: =IF(A2="",A1,A2) Copy C1 down to C3 Then place this in C7's formula bar, press CTRL+SHIFT+ENTER (CSE) to confirm the formula, instead of just pressing ENTER: =IF(A7="","",INDEX($B$1:$B$3,MATCH(1,($A$1:$A$3=A 7)*($C$1:$C$3=A7),0))) Copy C7 down to return required results Adapt the above to suit your actual extents -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ... I want to be able to do the following: A B 1 26/03/2006 $20.00 2 1/12/2006 $40.00 3 1/10/2007 $100.00 4 5 6 7 28/11/2006 2 8 29/11/2006 2 I need a formula that says if the date in A7 is between date in A1 to A2, then muliply B7 by B1, else if the date in A7 is between date in A2 to A3, then multiply B7 by B2, and so on. |
#3
|
|||
|
|||
select date in date range
Just to add on a little more .. The formula suggested in C7 is essentially
an array formula which needs to be array-entered via CSE. If you did the CSE confirmation correctly, you should see Excel wrap curly braces: { } around the formula in the formula bar (ie in C7's formula bar). If you don't see it (the curly braces), click inside the formula bar and try the CSE confirmation again. The visual check on the curly braces is the *only way* (afaik) to check that the formula is correctly array-entered. If the formula is NOT array-entered, of course it won't return correctly. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
|
|||
|
|||
select date in date range
=VLOOKUP(A7,$A$1:$B$3,2)*B7
" wrote: I want to be able to do the following: A B 1 26/03/2006 $20.00 2 1/12/2006 $40.00 3 1/10/2007 $100.00 4 5 6 7 28/11/2006 2 8 29/11/2006 2 I need a formula that says if the date in A7 is between date in A1 to A2, then muliply B7 by B1, else if the date in A7 is between date in A2 to A3, then multiply B7 by B2, and so on. |
#5
|
|||
|
|||
select date in date range
Thanks, just trying to wrap my mind around the formula.
I need to mutiply the number in B7 with the date result returned in either B1,B2,B3 IE if A7 isbetween $A$1:$A$2 then B7*$B$1, else if A7 isbetween $A$2:$A$3 then B7*$B$2 else if A7 isbetween $A$3:NOW() then A7*$B$3, else,"" I know these arent the Excel formula codes but my SQL gets in the way. "Max" wrote: Assuming the dates in A1:A3 are in chrono order (as posted) Put in C1: =IF(A2="",A1,A2) Copy C1 down to C3 Then place this in C7's formula bar, press CTRL+SHIFT+ENTER (CSE) to confirm the formula, instead of just pressing ENTER: =IF(A7="","",INDEX($B$1:$B$3,MATCH(1,($A$1:$A$3=A 7)*($C$1:$C$3=A7),0))) Copy C7 down to return required results Adapt the above to suit your actual extents -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ... I want to be able to do the following: A B 1 26/03/2006 $20.00 2 1/12/2006 $40.00 3 1/10/2007 $100.00 4 5 6 7 28/11/2006 2 8 29/11/2006 2 I need a formula that says if the date in A7 is between date in A1 to A2, then muliply B7 by B1, else if the date in A7 is between date in A2 to A3, then multiply B7 by B2, and so on. |
#6
|
|||
|
|||
select date in date range
Or....I could use a simple vlookup table...thansk
"Teethless mama" wrote: =VLOOKUP(A7,$A$1:$B$3,2)*B7 " wrote: I want to be able to do the following: A B 1 26/03/2006 $20.00 2 1/12/2006 $40.00 3 1/10/2007 $100.00 4 5 6 7 28/11/2006 2 8 29/11/2006 2 I need a formula that says if the date in A7 is between date in A1 to A2, then muliply B7 by B1, else if the date in A7 is between date in A2 to A3, then multiply B7 by B2, and so on. |
#7
|
|||
|
|||
select date in date range
Errata, formula in C7 should be:
=IF(A7="","",INDEX($B$1:$B$3,MATCH(1,($A$1:$A$3=A 7)*($C$1:$C$3=A7),0)))*B7 (array-entered) Forgot you wanted to multiply it by the value in B7 P/s: There's a subtle difference between TM's vlookup & the array above. If you have repeated reference dates in A1 down, and the lookup date happens to coincide with that .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
|
|||
|
|||
select date in date range
Wasn't sure if you were the original poster??
For info, I've posted a correction to the formula in C7 (yes, I forgot you wanted to multiply it by the value in B7) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Thread Tools | |
Display Modes | |
|
|