MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help on easy function subtract first from last in dynamic list


Posted by Rick on January 23, 2001 7:46 AM

I have a spreadsheet that I use to keep disk space figures on. We enter data on this sheet several times a day. At the bottom, I would like to have a formula that subtracts the starting value from the last entered providing a running variance. Seems like it shouldnt be a big deal but it eludes me!


Posted by Aladin Akyurek on January 23, 2001 9:34 AM

Assuming that the figures occupy a range from A2 on. A6 contains the last usage figure.

Type in A8

=OFFSET(A7,-1,0)-A2

May I suggest a different scheme which might be more flexible to use.

I still assume that the figures are entered in column A from A2 on and that this column is used only for disk space usage entries.

Type in

B2 ="$A$2:"&"$A$"&COUNTA(A:A)+1

Select B2, go to Insert, Name, Define, and enter the name DiskUsage in the sapce for Names for workbook. Then type the following in the space for Refers to:

=INDIRECT(Sheet1!$B$2)

and click OK or Add.

You may replace the name Sheet1 something meaningful if you rename Sheet1.

Now you may compute many things about the figures in the range DiskUsage. For example the difference between the first and last value:

C2 =INDEX(DiskUsage,ROWS(DiskUsage),COLUMNS(DiskUsage))-$A$2

C3 =VAR(DiskUsage)

C4 =STDEV(DiskUsage)

etc.

Aladin

Posted by Rick on January 24, 2001 6:21 AM


Thanks for the recommendation.
What I am finding that there must be a figure in the cell above the cell referenced in the paren. In this example =OFFSET(A7,-1,0)-A2 there must be a number in cell A6. If it is blank or zero, I get a negative of the cell in A2 retruned.
IN my case I have a column say from A1 through A10 I will use for data. At intervals I am filling in disk space figures. I want to use cell A12 to calculate the difference from A1 to the last cell filled in the sequence. It will subtract A1 from A2, then 2 hours later A1 from A3 as I fill in A3. When I fill in A3 A4-A10 are empty.
Any other thoughts?

Posted by Aladin Akyurek on January 24, 2001 10:07 AM

: I have a spreadsheet that I use to keep disk space figures on. We enter data on this sheet several times a day. At the bottom, I would like to have a formula that subtracts the starting value from the last entered providing a running variance. Seems like it shouldnt be a big deal but it eludes me!

Hi Rick: You can try

A12 =INDIRECT("$A"&COUNT($A$1:$A$10))-$A$1

Aladin