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

xorglub

full member

Activity: 204

Merit: 100

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

botsofbitcoin

member

Activity: 62

Merit: 10

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

psilan

sr. member

Activity: 364

Merit: 250

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

psilan

sr. member

Activity: 364

Merit: 250

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

Much appreciated.

botsofbitcoin

member

Activity: 62

Merit: 10

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.

psilan

sr. member

Activity: 364

Merit: 250

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!

Topic: Excel help - total cost to sell into a table of buys (Read 896 times)