Bitcoin Forum
June 21, 2024, 10:34:09 AM *
News: Latest Bitcoin Core release: 27.0 [Torrent]
 
   Home   Help Search Login Register More  
Pages: [1]
  Print  
Author Topic: Excel help - total cost to sell into a table of buys  (Read 879 times)
psilan (OP)
Sr. Member
****
Offline Offline

Activity: 364
Merit: 250



View Profile
April 15, 2013, 01:08:41 AM
 #1

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! Smiley

dip
botsofbitcoin
Member
**
Offline Offline

Activity: 62
Merit: 10



View Profile WWW
April 15, 2013, 02:18:40 AM
 #2

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.

See my localbitcoins ads here: https://localbitcoins.com/accounts/profile/botsofbitcoin/?ch=2mk
Visit Bots of Bitcoin here: http://www.botsofbitcoin.com/
Follow on Twitter: @botsofbitcoin
psilan (OP)
Sr. Member
****
Offline Offline

Activity: 364
Merit: 250



View Profile
April 15, 2013, 02:28:47 AM
 #3

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

dip
psilan (OP)
Sr. Member
****
Offline Offline

Activity: 364
Merit: 250



View Profile
April 15, 2013, 05:21:19 AM
 #4

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

dip
botsofbitcoin
Member
**
Offline Offline

Activity: 62
Merit: 10



View Profile WWW
April 15, 2013, 08:26:27 AM
 #5

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

See my localbitcoins ads here: https://localbitcoins.com/accounts/profile/botsofbitcoin/?ch=2mk
Visit Bots of Bitcoin here: http://www.botsofbitcoin.com/
Follow on Twitter: @botsofbitcoin
xorglub
Full Member
***
Offline Offline

Activity: 204
Merit: 100


View Profile
April 15, 2013, 10:22:40 AM
 #6

Do you mean exactly like the calculator at http://bitcoin.clarkmoody.com/ ?
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!