How to select data in a row that is higher/lower and post it in another column

E

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
49
Office Version
  1. 2010
Platform
  1. Windows
Hi, I have data in three columns and want to pick out any one cell in a row that is more or less than the others by some variable (which I will need to adjust). Then express it as the difference from the average of the other two. For example in the table below the data in A2 is higher than the others in the row but I never know which it might be, if any, and by how much so I would like to search for those that are and post them in another column. Preferably with all the rows below the criteria blank. The difference of interest is probably only in the first three decimal places.
I have little experience of macros and would really appreciate some help.

74.59273​
74.59296​
74.593292​
74.65717​
74.59275​
74.592827​
74.58994​
74.59055​
74.590813​
74.5908​
74.5908​
74.59066​
74.59274​
74.5932​
74.593582​
74.59204​
74.59278​
74.592674​
74.59032​
74.59058​
74.590736​
74.59113​
74.59095​
74.590981​
74.59248​
74.59348​
74.593651​
74.59275​
74.59312​
74.593178​
74.59061​
74.59101​
74.591003​
 

Attachments

  • 1606671184516.png
    1606671184516.png
    4.9 KB · Views: 8

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Is this what you mean?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

20 11 30.xlsm
ABCDE
1HighestDiff
274.5927374.5929674.59329274.5932920.0004470
374.6571774.5927574.59282774.657170.0643815
474.5899474.5905574.59081374.5908130.0005680
574.590874.590874.5906674.59080.0000700
674.5927474.593274.59358274.5935820.0006120
774.5920474.5927874.59267474.592780.0004230
874.5903274.5905874.59073674.5907360.0002860
974.5911374.5909574.59098174.591130.0001645
1074.5924874.5934874.59365174.5936510.0006710
1174.5927574.5931274.59317874.5931780.0002430
1274.5906174.5910174.59100374.591010.0002035
Highest & Diff
Cell Formulas
RangeFormula
D2:D12D2=MAX(A2:C2)
E2:E12E2=D2-AVERAGE(MIN(A2:C2),MEDIAN(A2:C2))
 
Upvote 0
That's really great thanks, although, the value I want is the one that departs from the others so may be lower or higher. So I reversed you solution in two more columns and took the lowest value away from the average to get the lowest difference. Now all I need to do is figure out how to put just the bigger difference cells in another column so that I can find them easily as I have more than 20k rows!
 
Upvote 0
Ed Harris said:
the value I want is the one that departs from the others so may be lower or higher.
Click to expand...
What about this then?

20 12 01.xlsm
ABCDE
1High/LowDiff
274.5927374.5929674.59329274.59329274.593062
374.6571774.5927574.59282774.6571774.657093
474.5899474.5905574.59081374.5899474.591076
574.590874.590874.5906674.5906674.5908
674.5927474.593274.59358274.5927474.593964
774.5920474.5927874.59267474.5920474.592886
874.5903274.5905874.59073674.5903274.590892
974.5911374.5909574.59098174.5911374.591099
1074.5924874.5934874.59365174.5924874.593822
1174.5927574.5931274.59317874.5927574.593236
1274.5906174.5910174.59100374.5906174.591017
High or Low & Diff
Cell Formulas
RangeFormula
D2:D12D2=IF(MAX(A2:C2)-MEDIAN(A2:C2)>=MEDIAN(A2:C2)-MIN(A2:C2),MAX(A2:C2),MIN(A2:C2))
E2:E12E2=H2-(MEDIAN(A2:C2)-IF(D2=MAX(A2:C2),MIN(A2:C2),MAX(A2:C2)))
 
Upvote 0
Well, your previous solution was more useful as it gave the difference and I can just set the data to 3 decimal places and get quite a clear result. I haven't tried editing your formula to get this same result yet.
 
Upvote 0
Ed Harris said:
Well, your previous solution was more useful as it gave the difference ..
Click to expand...
Sorry, I posted in a hurry and that formula in column E was completely wrong. :oops:

Is this more what you want (or perhaps you want to wrap it with an ABS() function to always return the difference as a positive)?

20 12 01.xlsm
ABCDE
1High/LowDiff
274.5927374.5929674.59329274.5932920.000447
374.6571774.5927574.59282774.657170.0643815
474.5899474.5905574.59081374.58994-0.0007415
574.590874.590874.5906674.59066-0.00014
674.5927474.593274.59358274.59274-0.000651
774.5920474.5927874.59267474.59204-0.000687
874.5903274.5905874.59073674.59032-0.000338
974.5911374.5909574.59098174.591130.0001645
1074.5924874.5934874.59365174.59248-0.0010855
1174.5927574.5931274.59317874.59275-0.000399
1274.5906174.5910174.59100374.59061-0.0003965
High or Low & Diff
Cell Formulas
RangeFormula
D2:D12D2=IF(MAX(A2:C2)-MEDIAN(A2:C2)>=MEDIAN(A2:C2)-MIN(A2:C2),MAX(A2:C2),MIN(A2:C2))
E2:E12E2=D2-(MEDIAN(A2:C2)+IF(D2=MAX(A2:C2),MIN(A2:C2),MAX(A2:C2)))/2
 
Upvote 0
Solution
That is perfect the negatives will be useful in fact. Many thanks for your help!
 
Upvote 0
Cheers. Glad to get there in the end. :)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,737
Messages
6,126,566
Members
449,318
Latest member
Son Raphon

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