Most valuable professional
  • Hot Topics

Excel Determining out the Appropriate Price

Hello! I am looking to create a formula based on the following table: 1 2 3 4 5 *That last row is the average of the column (ignoring zeroes). The first row is the column letters that each column represents in my spreadsheet. What I want to do is decipher an appropriate bid price based on the data from the past 2 bids (1/9/2014 and 2/11/2014). Currently, I have this formula in the "May Bid Price" row 2 within my spreadsheet (as an example): -0.25 Which means that if the bid from February 11th is zero, then I will consider the average of that region (row 5) minus $.25 as my bid price; however, if there is a price there (greater than zero), then I will pull that price minus $.25 as my bid price. The only other thing is, I realized I should be looking at both columns O (1/9/2014) and P (2/11/2014), but I am not sure how to incorporate both of them in my formula. I want to essentially say that if BOTHcolumns are zero, then the formula should pull the price from the average of the column (row 5), BUT if there is a price in either column O or P or both, then I want it to take that most recent price minus $.25 (i.e. If there is a price in 1/9/2014, but not for 2/11/2014, it would take the price from 1/9/2014 minus $.25 and vise versa). I hope that makes sense. I know it's confusing and was a bit hard to try and explain. Feel free to ask me if you have any questions and thank you for your help!

This question generated 22 answers. To proceed to the answers, click here.

This thread is current as of May 7, 2014.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel