When you use a formula to calculate totals in a bidding spreadsheet, by default, Excel calculates a value of "0.00" for the final calculated cell.
If the final calculated cell is mapped to a lot or item in a project, and the bidder leaves the cells used to calculate that cell total blank (which usually means they are not bidding on that particular lot or item), a bid of "0.00" will be submitted rather than a "null" or no bid. This can cause problems with bidder ranking, especially in a reverse auction.
There are two workarounds for this problem:
- Enter "#N/A" into blank cells used in calculations before you distribute the bidding spreadsheet. This forces a value of "#N/A" into the final calculation cell instead of "0.00." Bidders may then input their information into the cells by deleting the #N/A or typing over it. In essence, this method is setting the default entry as "#N/A," and is ideal for administrators who might not be experienced in Excel or with Excel formulas.
Workaround Example One
- Enter "#N/A" only in mapped cells by using a specific formula. In the following example, the "Complete Location Bid" cell is the only mapped cell affected by a formula. The formula for the cell currently results in a value of "0.00;" however, you can modify the formula to make sure that a bid of "$0.00" is not submitted during the Live Event.
The example formula for the "Complete Location Bid" (mapped) cell is: IF (SUM(H161:H168) <>0, (SUM (H160:168)), #N/A). This formula inserts "#N/A" into the cell if the formula cells are left blank. The following base formula can be used to create this effect: IF ((formula)<>0, (formula), #N/A)
Workaround Example Two
Related Topics