CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulacumulative-sumgs-vlookup
Dan
Dan

Reputation: 105

Calculating dynamic pricing on Google Sheets

I have imported data from a trading exchange listing sellers of a particular cryptocurrency.

From this data, I want to create dynamic pricing to display an average cost on an order based on given order size.

I will give an example of what I am looking for:

Example dataset

0

Within this example, we would be purchasing the cryptocurrency 'SINS'. As per the data showed on this table, if 29.06 SINS was purchased, that would fill the first order, and the total BTC paid would be 0.00459 BTC.

If an order was placed for 145 SINS, it would fill the orders up to row 12 and partially fill the order in row 13. By calculating that manually, I know that would cost 0.02293365 BTC (calculated using col D) at an average price of 0.00015816 per SIN.

What I would like to achieve is if a number is entered in a cell, it confirms the average price of an order based on the number entered and the orders imported from the trading exchange.

Upvotes: 1

Views: 767

Answers (1)

player0
player0

Reputation: 1

=INDIRECT(ADDRESS(MATCH(VLOOKUP(O2,F2:F,1),F:F,0),7,4))+(
 INDIRECT(ADDRESS(MATCH(VLOOKUP(O2,F2:F,1),F:F,0)+1,4,4))*(O2-
 INDIRECT(ADDRESS(MATCH(VLOOKUP(O2,F2:F,1),F:F,0),6,4)))/
 INDIRECT(ADDRESS(MATCH(VLOOKUP(O2,F2:F,1),F:F,0)+1,3,4)))

0

spreadsheet demo

Upvotes: 0

Related Questions