# Stumped on formula

Options
✭✭✭✭✭

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!