Print Page - Excel help - total cost to sell into a table of buys

Title: Excel help - total cost to sell into a table of buys
Post by: psilan on April 15, 2013, 01:08:41 AM

Any excel gurus about?

UNITS / PRICE PER UNIT
1 / $80
1.5 / $75
3.5 / $70

If I want to sell x units, what would be the formula to calculate the total sale value? Obviously this table is very simplified. Eg: I want to sell 4 units into the table. How much $ total will I get back?

Thanks! :)

Title: Re: Excel help - total cost to sell into a table of buys
Post by: botsofbitcoin on April 15, 2013, 02:18:40 AM

I can't think of a quick (single cell) way to do it without VBA but you can put the following in column C and drag it down.

=IF(SUM(A$1:A2)<=OrderSize,Offers*Price,MAX(0,(Offers-(SUM(A$1:A2)-OrderSize))*Price))

The table starts in A1 and, columns are named Offers (Col A) and Price (Col B). The order size you're considering is in a cell called OrderSize. The total cost is then the sum of Col C.

Title: Re: Excel help - total cost to sell into a table of buys
Post by: psilan on April 15, 2013, 02:28:47 AM

Thanks mate. I'll try this out when I get a break and let you know :) Much appreciated.

Title: Re: Excel help - total cost to sell into a table of buys
Post by: psilan on April 15, 2013, 05:21:19 AM

I couldn't get that to work. (noob)
Ah i'm going to lose it. Excel hurts.
Googling....

Title: Re: Excel help - total cost to sell into a table of buys
Post by: botsofbitcoin on April 15, 2013, 08:26:27 AM

Try this VBA function then. The syntax is =SellIntoBuys(Offers, Prices, Amount to sell).

Hope this works for you.

Code:

Option Explicit

Function SellIntoBuys(ByVal Offers As Range, _
                      ByVal Prices As Range, _
                      Units As Double)
Dim UnitsOffered As Double
Dim UnitsRemaining As Double
Dim Price As Double
Dim SaleValue As Double
Dim row As Long

UnitsRemaining = Units
SaleValue = 0

For row = 1 To UBound(Offers.Value2)
    UnitsOffered = Offers.Value2(row, 1)
    Price = Prices.Value2(row, 1)
    If (UnitsRemaining > 0) Then
        If (UnitsRemaining >= UnitsOffered) Then
            SaleValue = SaleValue + UnitsOffered * Price
        Else
            SaleValue = SaleValue + UnitsRemaining * Price
        End If
        UnitsRemaining = UnitsRemaining - UnitsOffered
    End If
Next row
SellIntoBuys = SaleValue

End Function

Title: Re: Excel help - total cost to sell into a table of buys
Post by: xorglub on April 15, 2013, 10:22:40 AM

Do you mean exactly like the calculator at http://bitcoin.clarkmoody.com/ ?

Bitcoin Forum

Economy => Economics => Topic started by: psilan on April 15, 2013, 01:08:41 AM