Excel Help

[Alice]

I don't know if this is possible... but I'm hoping someone will know the formula that I am looking for.

I have a chart made... and every day I'm going to be adding a number.  Is there a formula that will subract the latest number added from the first number?


Like if I had a column that said:

100
87
54
64

I'd want a formula to subtract 64 from 100. 

Then if the next day I put in a new cell so it looked like:

100
87
54
64
66

Then the formula would subtract 66 from 100.  Is this possible?

[BigDun]

In Excel, go into Tools-Macros-Visual Basic Editor.
In the VB Editor, go to Insert-Module.
Paste the following into the empty window:

Function LASTINCOLUMN(rngInput As Range)
    Dim WorkRange As Range
    Dim i As Integer, CellCount As Integer
    Application.Volatile
    Set WorkRange = rngInput.Columns(1).EntireColumn
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            LASTINCOLUMN = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

Then select File-Close and Return to Excel from the VB Editor.

Save your worksheet.

You will now have a LASTINCOLUMN function that will do exactly what you need.

Example

       A                  B

1    200
2   40
3   100
4    50
5   60

Put the following formula in B1: =A1-LASTINCOLUMN(A1)

Will give B1 the value of 140

[dazie]

If that works then

Me    You

[BigDun]

I didn't write the function myself. I just googled it and then wrote up the instructions on how to add the function to the workbook.

[Alice]

Thank you so much.  

I'll try it when I get back from grocery shopping.

[JJ]

It would be simpler to just write =c#-$c$# in all the cells you need answers for. I'd approach it that way but then again I dunno what you need it for.

[Alice]

It would be simpler to just write =c#-$c$# in all the cells you need answers for. I'd approach it that way but then again I dunno what you need it for.

That didn't do anything.

[Alice]

Wow, it totally works.  Thanks Cheese Duck!

[ReBurn]

VBA is awesome. I once wrote a macro that turned a spreadsheet full of purchase order data into a relatively stripped-down EDI 856 transaction because the company didn't want to buy software that would map their notices from their flat-file format to X12. Their system generated flat files and someone else had written a macro that read the flat file and imported it into the spreadsheet. Then my macro did the other conversion. That was hard.

I bet that they ended up paying more to develop the stupid macros and for their CSR's to run the stupid macros than they would have spent to buy some decent EDI software and automate the process.

[JJ]

It would be simpler to just write =c#-$c$# in all the cells you need answers for. I'd approach it that way but then again I dunno what you need it for.

That didn't do anything.

Not literally that. for example, "=a4-$a$1" will subtract "a4" from "always a1" so you can drag it down and it will automatically subtract whatever number from the first cell. You could set it up such that each time you entered a new number, the subtraction would pop up beside it.

Anyway it's all done.