# Formula help

#### tony0682

##### Board Regular
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### stanleydgromjr

##### Banned
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
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

##### Board Regular
Any help with this?

#### stanleydgromjr

##### Banned
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
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

##### MrExcel MVP
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
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.

##### MrExcel MVP
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...

Replies
2
Views
186
Replies
5
Views
112
Replies
3
Views
280
Replies
5
Views
157
Replies
4
Views
213

1,170,933
Messages
5,872,792
Members
432,947
Latest member
tchurzofretka

### 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.

### Which adblocker are you using?

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

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