Finding a cell not agreeing the trend!

S

shantanuo

New Member
Joined
Apr 13, 2003
Messages
25
Hi,

1) I know the actual cost of product a=30
2) I want to find out who is selling it at too high or too low price
3) I have a table that look like this

Product Dealer1 Dealer2 Dealer3 Dealer4 Check
a 29 31 32 90

4) I want Cell reference of Dealer 4 in the check column since he is selling it at a price that is more than 20%

How is it possible to do?

Shantanu Oak
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Book1
ABCDEFG
1ProductDealer1Dealer2Dealer3Dealer4CheckActual
2a29313290$E$230
3
Sheet1


The formula in F2 is...

=CELL("address",INDEX(B2:E2,MATCH(TRUE,INDEX(B2:E2>=(G2+G2*20%),1,0),0)))
 
Upvote 0
Please refine the formula

Dear Sir,
Thanks for you prompt reply.
I want to know + or - 20% of actual
And there can be more than one dealer cheating!

Shantanu Oak
 
Upvote 0
Re: Please refine the formula

shantanuo said:
...
I want to know + or - 20% of actual
And there can be more than one dealer cheating!...
Click to expand...


Changed your spec of "cell ref" to "dealer"...
Book1
ABCDEFGH
1ProductDealer1Dealer2Dealer3Dealer4CheckAllActual
2a22313290Dealer1Dealer1,Dealer430
3b16152620Dealer2Dealer2,Dealer320
Sheet1


The formula in F2 can anly return the first match/cheater...

=INDEX($B$1:$E$1,MATCH(1,INDEX((B2:E2>(H2+H2*20%))+(B2:E2<(H2-H2*20%)),1,0),0))

The array-formula in G2 can return all cheaters...

=SUBSTITUTE(MCONCAT(IF((B2:E2>(H2+H2*20%))+(B2:E2<(H2-H2*20%)),","&$B$1:$E$1,"")),",","",1)

which must be entered by hitting control+shift+enter at the same time, not by just hitting enter. Moreover, it requires themorefunc.xlladd-in.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Very Complicated Stock Formula's that Ignore 4th, 5th, 6th, etc Day Trends and Isolate 3 Day Trends
Replies
3
Views
293
rebel123
R
F
  • Locked
  • Question
Please help me to find formula,trying from 2 months now
Replies
1
Views
190
Fluff
Fluff
D
  • Question
Need Assistance on IF/NESTED IF function
Replies
9
Views
321
Fluff
Fluff
J
  • Solved
Do i need a formula or macro to do this?
Replies
2
Views
406
jaugent27
J
willow1985
  • Solved
VBA Select all rows from active cell (selected cell) on table to 2nd last row
Replies
3
Views
124
rlv01
R

Forum statistics

Threads
1,216,604
Messages
6,131,704
Members
449,666
Latest member
Tommy2Tables365

Share this page

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