Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two question about Excel 2007
Hello!
I have two questions: I have such data: A B 1 Date Weight (kg) 2 2008-02-27 103,5 3 2008-02-28 103,2 4 2008-02-29 103,0 5 2008-03-01 102,7 6 2008-03-02 not filled yet 7 2008-03-03 not filled yet 8 2008-03-04 not filled yet etc. etc. 1) How to get value of the last record entered. I mean: how to get value of the first day entered in February 2008 and last value of the last day of February 2008. So it the minus would like this: A B 1 Date (month) Difference (kg) = Lost kg 2 Feb 2008 0,5 (because it equals 103,5-103,0) 3 March 2008 0 (because it equals 102,7-102,7) 4 etc. etc. So above data would show me how much weight I lost in each/every month, beginning from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. 2) How to get value of the first and last record entered overall. I mean: how to get value of the first day entered in the whole data and last value of the last day entered in the whole data. So it would be like this: A B C 1 Date From Date To Difference (kg) = Lost kg 2 2008-02-27 2008-03-01 0,8 kg (because it equals 103,5-102,7) So above data would show me how much weight I lost since the beginning of loosing weight, that is from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. Thank you in advance for help I really apprieciate it Marcin from Poland |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two question about Excel 2007
Will all your data be for the *same* year?
-- Biff Microsoft Excel MVP "MZ" wrote in message ... Hello! I have two questions: I have such data: A B 1 Date Weight (kg) 2 2008-02-27 103,5 3 2008-02-28 103,2 4 2008-02-29 103,0 5 2008-03-01 102,7 6 2008-03-02 not filled yet 7 2008-03-03 not filled yet 8 2008-03-04 not filled yet etc. etc. 1) How to get value of the last record entered. I mean: how to get value of the first day entered in February 2008 and last value of the last day of February 2008. So it the minus would like this: A B 1 Date (month) Difference (kg) = Lost kg 2 Feb 2008 0,5 (because it equals 103,5-103,0) 3 March 2008 0 (because it equals 102,7-102,7) 4 etc. etc. So above data would show me how much weight I lost in each/every month, beginning from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. 2) How to get value of the first and last record entered overall. I mean: how to get value of the first day entered in the whole data and last value of the last day entered in the whole data. So it would be like this: A B C 1 Date From Date To Difference (kg) = Lost kg 2 2008-02-27 2008-03-01 0,8 kg (because it equals 103,5-102,7) So above data would show me how much weight I lost since the beginning of loosing weight, that is from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. Thank you in advance for help I really apprieciate it Marcin from Poland |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two question about Excel 2007
For now yes.
For the future no. Thank you Marcin Użytkownik "T. Valko" napisał w wiadomości ... Will all your data be for the *same* year? -- Biff Microsoft Excel MVP "MZ" wrote in message ... Hello! I have two questions: I have such data: A B 1 Date Weight (kg) 2 2008-02-27 103,5 3 2008-02-28 103,2 4 2008-02-29 103,0 5 2008-03-01 102,7 6 2008-03-02 not filled yet 7 2008-03-03 not filled yet 8 2008-03-04 not filled yet etc. etc. 1) How to get value of the last record entered. I mean: how to get value of the first day entered in February 2008 and last value of the last day of February 2008. So it the minus would like this: A B 1 Date (month) Difference (kg) = Lost kg 2 Feb 2008 0,5 (because it equals 103,5-103,0) 3 March 2008 0 (because it equals 102,7-102,7) 4 etc. etc. So above data would show me how much weight I lost in each/every month, beginning from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. 2) How to get value of the first and last record entered overall. I mean: how to get value of the first day entered in the whole data and last value of the last day entered in the whole data. So it would be like this: A B C 1 Date From Date To Difference (kg) = Lost kg 2 2008-02-27 2008-03-01 0,8 kg (because it equals 103,5-102,7) So above data would show me how much weight I lost since the beginning of loosing weight, that is from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. Thank you in advance for help I really apprieciate it Marcin from Poland |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two question about Excel 2007
Assume you have the month/year as a TEXT entry in a range of cells like:
D2 = Feb 2008 (as a TEXT entry) D3 = Mar 2008 (as a TEXT entry) D4 = Apr 2008 (as a TEXT entry) Enter this array formula** in E2 and copy down as needed: =IF(SUMPRODUCT(--(TEXT(A$2:A$8,"mmm yyyy")=D2),B$2:B$8),INDEX(B$2:B$8,MATCH(D2,TEXT(A$ 2:A$8,"mmm yyyy"),0))-LOOKUP(2,1/(TEXT(A$2:A$8,"mmm yyyy")=D2)/B$2:B$8,B$2:B$8),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) For the total, start date to end date: Enter this formula in G2 to get the start date: =MIN(A2:A8) Enter this formula in H2 to get the *last* date entered in your date range: =MAX(A2:A8) Then, for the total enter this formula in I2: =VLOOKUP(G2,A2:B8,2,0)-LOOKUP(5000,B2:B8) -- Biff Microsoft Excel MVP "MZ" wrote in message ... For now yes. For the future no. Thank you Marcin Użytkownik "T. Valko" napisał w wiadomości ... Will all your data be for the *same* year? -- Biff Microsoft Excel MVP "MZ" wrote in message ... Hello! I have two questions: I have such data: A B 1 Date Weight (kg) 2 2008-02-27 103,5 3 2008-02-28 103,2 4 2008-02-29 103,0 5 2008-03-01 102,7 6 2008-03-02 not filled yet 7 2008-03-03 not filled yet 8 2008-03-04 not filled yet etc. etc. 1) How to get value of the last record entered. I mean: how to get value of the first day entered in February 2008 and last value of the last day of February 2008. So it the minus would like this: A B 1 Date (month) Difference (kg) = Lost kg 2 Feb 2008 0,5 (because it equals 103,5-103,0) 3 March 2008 0 (because it equals 102,7-102,7) 4 etc. etc. So above data would show me how much weight I lost in each/every month, beginning from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. 2) How to get value of the first and last record entered overall. I mean: how to get value of the first day entered in the whole data and last value of the last day entered in the whole data. So it would be like this: A B C 1 Date From Date To Difference (kg) = Lost kg 2 2008-02-27 2008-03-01 0,8 kg (because it equals 103,5-102,7) So above data would show me how much weight I lost since the beginning of loosing weight, that is from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. Thank you in advance for help I really apprieciate it Marcin from Poland |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two question about Excel 2007
In case line wrap breaks at the spaces, there is a space in every instance
of: "mmm yyyy" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Assume you have the month/year as a TEXT entry in a range of cells like: D2 = Feb 2008 (as a TEXT entry) D3 = Mar 2008 (as a TEXT entry) D4 = Apr 2008 (as a TEXT entry) Enter this array formula** in E2 and copy down as needed: =IF(SUMPRODUCT(--(TEXT(A$2:A$8,"mmm yyyy")=D2),B$2:B$8),INDEX(B$2:B$8,MATCH(D2,TEXT(A$ 2:A$8,"mmm yyyy"),0))-LOOKUP(2,1/(TEXT(A$2:A$8,"mmm yyyy")=D2)/B$2:B$8,B$2:B$8),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) For the total, start date to end date: Enter this formula in G2 to get the start date: =MIN(A2:A8) Enter this formula in H2 to get the *last* date entered in your date range: =MAX(A2:A8) Then, for the total enter this formula in I2: =VLOOKUP(G2,A2:B8,2,0)-LOOKUP(5000,B2:B8) -- Biff Microsoft Excel MVP "MZ" wrote in message ... For now yes. For the future no. Thank you Marcin Użytkownik "T. Valko" napisał w wiadomooci ... Will all your data be for the *same* year? -- Biff Microsoft Excel MVP "MZ" wrote in message ... Hello! I have two questions: I have such data: A B 1 Date Weight (kg) 2 2008-02-27 103,5 3 2008-02-28 103,2 4 2008-02-29 103,0 5 2008-03-01 102,7 6 2008-03-02 not filled yet 7 2008-03-03 not filled yet 8 2008-03-04 not filled yet etc. etc. 1) How to get value of the last record entered. I mean: how to get value of the first day entered in February 2008 and last value of the last day of February 2008. So it the minus would like this: A B 1 Date (month) Difference (kg) = Lost kg 2 Feb 2008 0,5 (because it equals 103,5-103,0) 3 March 2008 0 (because it equals 102,7-102,7) 4 etc. etc. So above data would show me how much weight I lost in each/every month, beginning from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. 2) How to get value of the first and last record entered overall. I mean: how to get value of the first day entered in the whole data and last value of the last day entered in the whole data. So it would be like this: A B C 1 Date From Date To Difference (kg) = Lost kg 2 2008-02-27 2008-03-01 0,8 kg (because it equals 103,5-102,7) So above data would show me how much weight I lost since the beginning of loosing weight, that is from Feb 2008 and finishing on the last month of the date entered in the spreadsheet. Thank you in advance for help I really apprieciate it Marcin from Poland |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro question | Excel Discussion (Misc queries) | |||
Excel 2007 Question | Excel Discussion (Misc queries) | |||
Excel 2007 PivotTable Question | Excel Worksheet Functions | |||
Excel 2007 question? | Excel Worksheet Functions | |||
Excel 2007 SmartArt Question | Excel Discussion (Misc queries) |