Two question about Excel 2007

[MZ]

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

 

[T. Valko]

Will all your data be for the *same* year?

 

[MZ]

For now yes.
For the future no.

Thank you
Marcin

 

[T. Valko]

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)

 

[T. Valko]

In case line wrap breaks at the spaces, there is a space in every instance
of:

"mmm yyyy"