Stumped on formula

Debbie Petersen

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

Paul Newcome

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))

Paul Newcome

Happy to help. 👍️

Paul Newcome

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))

Debbie Petersen

Hi Paul - just got back in to this and YES! THIS! THANK you so much!

Paul Newcome

Happy to help. 👍️

Debbie Petersen

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!

Paul Newcome

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)))

Debbie Petersen

No wonder I'm stumped! Never did do well in math! Thank you AGAIN!!! You're a lifesaver!!

Paul Newcome

Happy to help. 👍️