Formula help

tony0682

Board Regular
Joined
May 19, 2005
Messages
164
Book3
ABCDEFGHIJKLMNOPQRST
1CusipSecurityABCDEFGHIJKLMStandardDevAverageMaxPricePriceDealerUsed
2XXXXXXXXXXXx100.0000100.0000100.00000.00000100.0000100.0000100.0000D
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Joined
Jul 30, 2006
Messages
3,656
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

Board Regular
Joined
May 19, 2005
Messages
164
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"))))
 
Joined
Jul 30, 2006
Messages
3,656

ADVERTISEMENT

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

Board Regular
Joined
May 19, 2005
Messages
164
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Board Regular
Joined
May 19, 2005
Messages
164
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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...
 

Forum statistics

Threads
1,136,995
Messages
5,679,037
Members
419,801
Latest member
spinjector

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top