| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
I need some help with a formula
I have 2 colonnes A and B In colonne A I have dates and in B values , but for some dates the value in B es 0, so I need a formula that gives the last value in B <0, or the date I have the last value anyone knows? thank you |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
Try the below. With your data as below try the below formula in cell D2.
Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(B1:B9,MAX(IF(A1:A9=C2,IF(B1:B90,ROW(B1:B9) )))) Col A Col B Col C Col D Date Value Query date Result 9/8/2009 0 9/8/2009 =formula 9/8/2009 3 9/8/2009 2 9/8/2009 1 9/9/2009 2 9/9/2009 2 9/9/2009 3 If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: I need some help with a formula I have 2 colonnes A and B In colonne A I have dates and in B values , but for some dates the value in B es 0, so I need a formula that gives the last value in B <0, or the date I have the last value anyone knows? thank you |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
with the formula I have =0
in my example I have 31/09/08 100 31/10/209 0 30/11/08 0 31/12/08 84.06 31/01/09 0 28/02/09 0 31/03/09 78.77 30/04/09 0 31/05/09 0 30/06/09 79.17 31/07/09 0 31/08/09 0 and I want to have as a result 30/6/09 or 79.17 to calculate the value for the 31/8/09 I need to know the previous value "Jacob Skaria" wrote: Try the below. With your data as below try the below formula in cell D2. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(B1:B9,MAX(IF(A1:A9=C2,IF(B1:B90,ROW(B1:B9) )))) Col A Col B Col C Col D Date Value Query date Result 9/8/2009 0 9/8/2009 =formula 9/8/2009 3 9/8/2009 2 9/8/2009 1 9/9/2009 2 9/9/2009 2 9/9/2009 3 If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: I need some help with a formula I have 2 colonnes A and B In colonne A I have dates and in B values , but for some dates the value in B es 0, so I need a formula that gives the last value in B <0, or the date I have the last value anyone knows? thank you |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
Now I use this formula =INDEX(B3:B600;MATCH(9.99999999999999E+307;B3:B600 ))
and works column B:B I have my values but I also need the date in column A How can I get the date corresponding the value I get in the formula? thanks "Ana" wrote: with the formula I have =0 in my example I have 31/09/08 100 31/10/209 0 30/11/08 0 31/12/08 84.06 31/01/09 0 28/02/09 0 31/03/09 78.77 30/04/09 0 31/05/09 0 30/06/09 79.17 31/07/09 0 31/08/09 0 and I want to have as a result 30/6/09 or 79.17 to calculate the value for the 31/8/09 I need to know the previous value "Jacob Skaria" wrote: Try the below. With your data as below try the below formula in cell D2. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(B1:B9,MAX(IF(A1:A9=C2,IF(B1:B90,ROW(B1:B9) )))) Col A Col B Col C Col D Date Value Query date Result 9/8/2009 0 9/8/2009 =formula 9/8/2009 3 9/8/2009 2 9/8/2009 1 9/9/2009 2 9/9/2009 2 9/9/2009 3 If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: I need some help with a formula I have 2 colonnes A and B In colonne A I have dates and in B values , but for some dates the value in B es 0, so I need a formula that gives the last value in B <0, or the date I have the last value anyone knows? thank you |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
To return date
=INDEX(A1:A100,MAX(IF(B1:B1000,ROW(B1:B100)))) To return value =INDEX(B1:B100,MAX(IF(B1:B1000,ROW(B1:B100)))) Note that both are array formulas If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: Now I use this formula =INDEX(B3:B600;MATCH(9.99999999999999E+307;B3:B600 )) and works column B:B I have my values but I also need the date in column A How can I get the date corresponding the value I get in the formula? thanks "Ana" wrote: with the formula I have =0 in my example I have 31/09/08 100 31/10/209 0 30/11/08 0 31/12/08 84.06 31/01/09 0 28/02/09 0 31/03/09 78.77 30/04/09 0 31/05/09 0 30/06/09 79.17 31/07/09 0 31/08/09 0 and I want to have as a result 30/6/09 or 79.17 to calculate the value for the 31/8/09 I need to know the previous value "Jacob Skaria" wrote: Try the below. With your data as below try the below formula in cell D2. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(B1:B9,MAX(IF(A1:A9=C2,IF(B1:B90,ROW(B1:B9) )))) Col A Col B Col C Col D Date Value Query date Result 9/8/2009 0 9/8/2009 =formula 9/8/2009 3 9/8/2009 2 9/8/2009 1 9/9/2009 2 9/9/2009 2 9/9/2009 3 If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: I need some help with a formula I have 2 colonnes A and B In colonne A I have dates and in B values , but for some dates the value in B es 0, so I need a formula that gives the last value in B <0, or the date I have the last value anyone knows? thank you |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
Thank you a lot
it works "Jacob Skaria" wrote: To return date =INDEX(A1:A100,MAX(IF(B1:B1000,ROW(B1:B100)))) To return value =INDEX(B1:B100,MAX(IF(B1:B1000,ROW(B1:B100)))) Note that both are array formulas If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: Now I use this formula =INDEX(B3:B600;MATCH(9.99999999999999E+307;B3:B600 )) and works column B:B I have my values but I also need the date in column A How can I get the date corresponding the value I get in the formula? thanks "Ana" wrote: with the formula I have =0 in my example I have 31/09/08 100 31/10/209 0 30/11/08 0 31/12/08 84.06 31/01/09 0 28/02/09 0 31/03/09 78.77 30/04/09 0 31/05/09 0 30/06/09 79.17 31/07/09 0 31/08/09 0 and I want to have as a result 30/6/09 or 79.17 to calculate the value for the 31/8/09 I need to know the previous value "Jacob Skaria" wrote: Try the below. With your data as below try the below formula in cell D2. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(B1:B9,MAX(IF(A1:A9=C2,IF(B1:B90,ROW(B1:B9) )))) Col A Col B Col C Col D Date Value Query date Result 9/8/2009 0 9/8/2009 =formula 9/8/2009 3 9/8/2009 2 9/8/2009 1 9/9/2009 2 9/9/2009 2 9/9/2009 3 If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: I need some help with a formula I have 2 colonnes A and B In colonne A I have dates and in B values , but for some dates the value in B es 0, so I need a formula that gives the last value in B <0, or the date I have the last value anyone knows? thank you |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
Try the below (array formula Use CTRL+SHIFT+ENTER to enter the formula)
=INDEX(B1:B100,MAX(IF(B1:B1000,ROW(B1:B100)))) The earlier formula retrives the last value for the date mentioned in cell C2 If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: with the formula I have =0 in my example I have 31/09/08 100 31/10/209 0 30/11/08 0 31/12/08 84.06 31/01/09 0 28/02/09 0 31/03/09 78.77 30/04/09 0 31/05/09 0 30/06/09 79.17 31/07/09 0 31/08/09 0 and I want to have as a result 30/6/09 or 79.17 to calculate the value for the 31/8/09 I need to know the previous value "Jacob Skaria" wrote: Try the below. With your data as below try the below formula in cell D2. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(B1:B9,MAX(IF(A1:A9=C2,IF(B1:B90,ROW(B1:B9) )))) Col A Col B Col C Col D Date Value Query date Result 9/8/2009 0 9/8/2009 =formula 9/8/2009 3 9/8/2009 2 9/8/2009 1 9/9/2009 2 9/9/2009 2 9/9/2009 3 If this post helps click Yes --------------- Jacob Skaria "Ana" wrote: I need some help with a formula I have 2 colonnes A and B In colonne A I have dates and in B values , but for some dates the value in B es 0, so I need a formula that gives the last value in B <0, or the date I have the last value anyone knows? thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
