Help to create formula for online ad/lead bids

PeterD

New member
Joined
Jul 27, 2020
Messages
2
I just started working for a business that sells ad space to the top four bidders. Each bidder specifies a quantity and a price. As each bidder's quantity is fulfilled, they are replaced by additional bidders from a waiting list until all bidder's quantities are fulfilled. I need a formula to know when the remaining bids will total less than $100. For example: if there are 7 bidders with bids and quantities as follows: $55 for 20, $54 for 15, $53 for 10, $50 for 20, $50 for 5, $40 for 10, and $30 for 100, the formula should return an answer of 21, meaning that the first 20 will be fulfilled with 4 bidders each and more than $100 each, but after that it drops to only one bidder left, paying $30, which is below the $100 needed to publish. How can I calculate this automatically for a variety of different bid scenarios that are changing in each market and every week? I have tired numerous approaches, but each breaks down with different scenarios presented. It is easy for me to see the answer to each scenario, but I can't seem to get the formula correct to make it automatically tell me the threshold in each market. Any help is appreciated. Thanks you.
 
I just started working for a business that sells ad space to the top four bidders. Each bidder specifies a quantity and a price. As each bidder's quantity is fulfilled, they are replaced by additional bidders from a waiting list until all bidder's quantities are fulfilled. I need a formula to know when the remaining bids will total less than $100. For example: if there are 7 bidders with bids and quantities as follows: $55 for 20, $54 for 15, $53 for 10, $50 for 20, $50 for 5, $40 for 10, and $30 for 100, the formula should return an answer of 21, meaning that the first 20 will be fulfilled with 4 bidders each and more than $100 each, but after that it drops to only one bidder left, paying $30, which is below the $100 needed to publish. How can I calculate this automatically for a variety of different bid scenarios that are changing in each market and every week? I have tired numerous approaches, but each breaks down with different scenarios presented. It is easy for me to see the answer to each scenario, but I can't seem to get the formula correct to make it automatically tell me the threshold in each market. Any help is appreciated. Thanks you.
You say:
"each breaks down with different scenarios presented."​

Can you please share some of these scenarios and failures of those, with us?
 
Scenarios are all over the map and change almost weekly. Some examples include:
1. Bidders at $52 for 100, $51 for 10, $50 for 5, $50 for 2, $50 for 2, and $30 for 100
2. Bidders at $55 for 2, $54 for 2, $51 for 20, $50 for 100, and $40 for 10
3. Bidders at $65 for 10, $60 for 20, $58 for 10, $56 for 10, $55 for 10, $51 for 20, $51 for 10, $50 for 8, $50 for 5, $50 for 2, $40 for 10, and $30 for 20
 
Top