Hello!
I am looking to create a formula based on the following table:
<tbody>
</tbody>
*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):
=IF(P2>0,P2-0.25,$P$5-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!
I am looking to create a formula based on the following table:
| O | P | Q | |
| 1 | 1/9/2014 | 2/11/2014 | May Bid Price |
| 2 | $0.00 | $0.00 | ???? |
| 3 | $12.49 | $12.33 | ???? |
| 4 | $0.00 | $13.12 | ???? |
| 5 | $12.49 | $12.73 | ???? |
<tbody>
</tbody>
*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):
=IF(P2>0,P2-0.25,$P$5-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!
