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.
 

Subhotosh Khan

Super Moderator
Staff member
Joined
Jun 18, 2007
Messages
21,538
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?
 

PeterD

New member
Joined
Jul 27, 2020
Messages
2
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
 

Subhotosh Khan

Super Moderator
Staff member
Joined
Jun 18, 2007
Messages
21,538
I have tired numerous approaches, but each breaks down with different scenarios presented
What were the "approaches" that you tried and how did those "breakdown"?
 
Top