Stumped on formula
I need a formula that I'm not sure I can do. I have tried many different things, and only have a portion working. I need to be able to determine the "difference" between our $$ bid and either the low bidder, if we are not, or the 2nd bidder if we are the low bid. I've tried several If, or versions but getting nowhere beyond this:
=INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0)) - [Low Bidder Amount]1
The formula is if GBI is low bid amt, then subtract GBI from 2nd bid amt; if GBI is NOT low bid amt 1, then subtract low bid amt from GBI.
Any help is appreciated!!
Best Answers
-
So lets start by pulling each of the pieces individually.
We know the INDEX/MATCH pulls YOUR dollar amount, so we can go ahead and keep it. (your_bid)
=INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0))
Next we want to find out the low bid. (low_bid)
=MIN([Bid Amount 1]5:[Bid Amount 1]19)
We also want to figure out the 2nd lowest bid (in case YOU are the low bid). (2nd_lowest)
=SMALL([Bid Amount 1]5:[Bid Amount 1]19, 2)
Now we can piece them all together. We want a formula that says
your_bid minus (if your_bid equals the low_bid, then use 2nd_lowest, otherwise use low_bid)
Which looks like...
=your_bid - IF(your_bid = low_bid, 2nd_lowest, low_bid)
Which we can then use to plug in the appropriate formulas into each section to have a final formula of:
=INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0)) - IF(INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0)) = MIN([Bid Amount 1]5:[Bid Amount 1]19), SMALL([Bid Amount 1]5:[Bid Amount 1]19, 2), MIN([Bid Amount 1]5:[Bid Amount 1]19))
-
Happy to help. 👍️
Answers
-
So lets start by pulling each of the pieces individually.
We know the INDEX/MATCH pulls YOUR dollar amount, so we can go ahead and keep it. (your_bid)
=INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0))
Next we want to find out the low bid. (low_bid)
=MIN([Bid Amount 1]5:[Bid Amount 1]19)
We also want to figure out the 2nd lowest bid (in case YOU are the low bid). (2nd_lowest)
=SMALL([Bid Amount 1]5:[Bid Amount 1]19, 2)
Now we can piece them all together. We want a formula that says
your_bid minus (if your_bid equals the low_bid, then use 2nd_lowest, otherwise use low_bid)
Which looks like...
=your_bid - IF(your_bid = low_bid, 2nd_lowest, low_bid)
Which we can then use to plug in the appropriate formulas into each section to have a final formula of:
=INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0)) - IF(INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0)) = MIN([Bid Amount 1]5:[Bid Amount 1]19), SMALL([Bid Amount 1]5:[Bid Amount 1]19, 2), MIN([Bid Amount 1]5:[Bid Amount 1]19))
-
Hi Paul - just got back in to this and YES! THIS! THANK you so much!
-
Happy to help. 👍️
-
Hey Paul - thought I would see if you can help tweak this - what it is doing incorrectly I just discovered is - when GBI is the low bidder, it is subtracting the 2nd bidder amount from the GBI (low bid) amt and showing a negative number.
When GBI is low, it should be subtracting GBIs amount from the 2nd bidders amount thereby giving a positive number. Tried to revise the above, but the =SMALL is throwing me . Any suggestions would be awesome!
-
We can wrap the whole thing in an ABS function to generate the absolute value which will always be a positive number.
=ABS(INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0)) - IF(INDEX([Bid Amount 1]5:[Bid Amount 1]19, MATCH("GBI", [Bidder Name 1]5:[Bidder Name 1]19, 0)) = MIN([Bid Amount 1]5:[Bid Amount 1]19), SMALL([Bid Amount 1]5:[Bid Amount 1]19, 2), MIN([Bid Amount 1]5:[Bid Amount 1]19)))
-
No wonder I'm stumped! Never did do well in math! Thank you AGAIN!!! You're a lifesaver!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!