Formula help

[tony0682]

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Cusip	Security	A	B	C	D	E	F	G	H	I	J	K	L	M	StandardDev	Average	MaxPrice	Price	DealerUsed
2	XXXXXXXX	XXX			x	100.0000				100.0000	100.0000					0.00000	100.0000	100.0000	100.0000	D
Sheet1

I need some help modifying the formula in cell T2. As you can see, there are marks from the same dealers in D2, H2 and I2. T2 should show Average instead of the marked used by dealer D since there are 3 of the same marks. Any way I cna modify this formula to show Average if there the final marked used is shown by 3 different dealers.

 

[stanleydgromjr]

tony0682,

The cells 'D2', 'H2', and 'I2' are blank.

And, can you show us the formula in cell 'T2'?


Have a great day,
Stan

 

[tony0682]

F2. J2 andd K2 is what I meant, the formula in T2 is

=IF(COUNT(C2:O2)<2," ",IF(SUM(C2:O2)=0," ",IF(ISNUMBER(MATCH(S2,C2:O2,0)),INDEX($C$1:$O$1,MATCH(S2,C2:O2,0)),IF(ISERROR(Q2),"","Average"))))

 

[tony0682]

Any help with this?

 

[stanleydgromjr]

I separated all the parts of your foumula.

Try changing this:
INDEX($C$1:$O$1

to this:
INDEX($C$2:$O$2


Have a great day,
Stan

 

[tony0682]

That isn't the result I'm looking for. Changing the range to C2:O2 would return the number, I'm trying to make it show "Average" if there are more than one dealer showing the same price if it is used as the final mark. So instead of showing D in dealer used, it would show Average

 

[Aladin Akyurek]

I felt to take up all of your formulas...

P2:

=IF(COUNT(C2:O2)<2,"",STDEV(C2:O2))

Q2:

=IF(COUNT(C2:O2)<2,"",AVERAGE(C2:O2))

R2:

=IF(N(P2),IF(P2>0.125,Q2,MAX(C2:O2)),Q2)

S2:

=IF(N(R2),R2,"")

T2:

=IF(N(R2),IF(COUNTIF(C2:O2,R2)>1,"Average",INDEX($C$1:$O$1,MATCH(R2,C2:O2,0))),"")

Does the foregoing meet your needs?

 

[tony0682]

Seems to work except for the last formula, where if lets say there are two prices that exceed the standard deviation limit .125, then it takes the Average, that is another case where Average shoulud show up.

 

[Aladin Akyurek]

Seems to work except for the last formula, where if lets say there are two prices that exceed the standard deviation limit .125, then it takes the Average, that is another case where Average shoulud show up.

=IF(N(R2),IF(Q2=R2,"Average",IF(COUNTIF(C2:O2,R2)>1,"Average",INDEX($C$1:$O$1,MATCH(R2,C2:O2,0)))),"")

Not sure all this qua structure is better than what you already had...