Topic: Excel help - total cost to sell into a table of buys (Read 905 times)
Do you mean exactly like the calculator at http://bitcoin.clarkmoody.com/ ?
Try this VBA function then. The syntax is =SellIntoBuys(Offers, Prices, Amount to sell).
Hope this works for you.
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
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
I couldn't get that to work. (noob)
Ah i'm going to lose it. Excel hurts.
Googling....
Ah i'm going to lose it. Excel hurts.
Googling....
Thanks mate. I'll try this out when I get a break and let you know 
Much appreciated.
Much appreciated.
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.
=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.
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!
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!
Jump to: