Determining out the Appropriate Price

[tbeigi]

Hello!

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!

 

[shift-del]

Hi

[COLOR=#222222]=IF[/COLOR][COLOR=#0000dd](ISNA[/COLOR][COLOR=#222222](LOOKUP[/COLOR][COLOR=#0000dd](42,1/[/COLOR][COLOR=#222222](O5:P5>0)[/COLOR][COLOR=#0000dd],O5:P5)[/COLOR][COLOR=#222222])[/COLOR][COLOR=#0000dd],LOOKUP[/COLOR][COLOR=#222222](9^99,O5:P5,O$17:P$17)[/COLOR][COLOR=#0000dd],LOOKUP[/COLOR][COLOR=#222222](42,1/[/COLOR][COLOR=#0000dd](O5:P5>0)[/COLOR][COLOR=#222222],O5:P5)[/COLOR][COLOR=#0000dd])[/COLOR][COLOR=#222222]-$U$225[/COLOR]

 

[tbeigi]

Hi

[COLOR=#222222]=IF[/COLOR][COLOR=#0000dd](ISNA[/COLOR][COLOR=#222222](LOOKUP[/COLOR][COLOR=#0000dd](42,1/[/COLOR][COLOR=#222222](O5:P5>0)[/COLOR][COLOR=#0000dd],O5:P5)[/COLOR][COLOR=#222222])[/COLOR][COLOR=#0000dd],LOOKUP[/COLOR][COLOR=#222222](9^99,O5:P5,O$17:P$17)[/COLOR][COLOR=#0000dd],LOOKUP[/COLOR][COLOR=#222222](42,1/[/COLOR][COLOR=#0000dd](O5:P5>0)[/COLOR][COLOR=#222222],O5:P5)[/COLOR][COLOR=#0000dd])[/COLOR][COLOR=#222222]-$U$225[/COLOR]

Hello!

Thank you again for your help on this. I tried this formula and it gives me a negative number. Here is an example of what I am looking at:

	O	P	Q
1	1/9/2014	2/11/2014	May Bid Price
2	$ -	$ -	???
3	$ 13.42	$ -	???
4	$ -	$ -	???
5	$ 12.50	$ -	???
6	$12.96	$ -	???

<tbody>
</tbody>

I want the formula to recognize that since 2/11/2014 comes out as a zero since there were no bids, it should be looking at the 1/9/2014 price and using that value. If there is no bid in 2/11/2014 AND in 1/9/2014, then it pulls from the last row (row 6) which is the (regional) average. :/

Any ideas? Thank you again for your patience and willingness to help!

 

[shift-del]

Hi

What is the content of cell O2?
Is it a blank cell or does it contain a zero or does it contain a dash?

 

[tbeigi]

Hi

What is the content of cell O2?
Is it a blank cell or does it contain a zero or does it contain a dash?

Hello,

That is a zero ($0.00).

 

[shift-del]

Hi

Try this.

	O	P	Q
1	09.01.2014	11.02.2014	May Bid Price
2	0	0	12,71
3	13,42	0	13,17
4	0	0	12,71
5	12,5	0	12,25
6	12,96	#DIV/0!	12,71

<colgroup><col style="width: 28ptpx"><col width="53,25pt"><col width="53,25pt"><col width="68,25pt"></colgroup><tbody>
</tbody>

Zelle	Formel
Q2	{=IF(ISNA(LOOKUP(42,1/(O2:P2>0),O2:P2)),LOOKUP(9^99,IF(O2:P2=0,O$6:P$6)),LOOKUP(42,1/(O2:P2>0),O2:P2))-0.25}
O6	=AVERAGEIF(O2:O5,">0")

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

<tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

 

[tbeigi]

Hi

Try this.

	O	P	Q
1	09.01.2014	11.02.2014	May Bid Price
2	0	0	12,71
3	13,42	0	13,17
4	0	0	12,71
5	12,5	0	12,25
6	12,96	#DIV/0!	12,71

<tbody>
</tbody>

Zelle	Formel
Q2	{=IF(ISNA(LOOKUP(42,1/(O2:P2>0),O2:P2)),LOOKUP(9^99,IF(O2:P2=0,O$6:P$6)),LOOKUP(42,1/(O2:P2>0),O2:P2))-0.25}
O6	=AVERAGEIF(O2:O5,">0")

<tbody>
</tbody>

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

<tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

Hello! I just tried inputting that formula and it comes out as #VALUE! unfortunately. Are you doing something differently than me, perhaps?

 

[shift-del]

Unfortunately the tool doesn't have a built-in translation for the text.
Here is what Google makes of it:

Attention, array formula included!
The braces {} are not entered.
Exit the cell editor with Ctrl Shift Enter, instead of Enter alone.

 

[tbeigi]

Unfortunately the tool doesn't have a built-in translation for the text.
Here is what Google makes of it:

Hello!

So do I plug this formula somewhere else? I plugged it into Q2, but it came out as a #VALUE! Should I be adding something more to it? :/

 

[shift-del]

Hi

The part you didn't quote had the answer.

 

[tbeigi]

Hi

The part you didn't quote had the answer.

Hello,

So I inputted the formula in the cell and pressed "CTRL+SHIFT+ENTER," and it came out as #N/A - I apologize. I may be missing a step here to make this calculation work. Do I have to input it somewhere else? Is this a VBA technique?