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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
tony0682,

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

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


Have a great day,
Stan
 
Upvote 0
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"))))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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
Back
Top