Finding max value in a mixed data set.

chuggins143

Board Regular
Joined
Nov 10, 2009
Messages
100
Good morning one and all! I'm working on a sorting problem that someone here might be able to shed some light on... I have two columns of data, the first column contains voltages. A mixture of 120, 208, and 240 values in no particular order, and in column two there are various amp draw values. I've been trying different ways to look down through the data set and pull the max amp draw for each voltage with little success. I've attempted a couple of variations with Lookup, Index, Match, and so forth, but am coming up dry. Anyone ever run into this and possibly have a solution they might be willing to share?
Thanks!
Chad
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi there,

Have you tried a pivot table?

Voltage = Row Area
Amps = Data Items Area (set to MAX instead of SUM).
 
Upvote 0
ok say your data is in column a and b. highlight both column and sort by a ascending and b ascending.

in c1,c2,c3 put 120,208,240.

then in c1 use the formula =VLOOKUP(C1,A:B,2,1) and it will return the max amps number for each
 
Upvote 0
Ok, I'm trying to get this data into a pivot table, and it looks like I did it... got the amp values reset to Max, and it pulls the correct amp value when I creat the table, but it doesn't update if I go into the list and change anything. Is there a way to have it active?
C
 
Upvote 0
ok say your data is in column a and b. highlight both column and sort by a ascending and b ascending.

in c1,c2,c3 put 120,208,240.

then in c1 use the formula =VLOOKUP(C1,A:B,2,1) and it will return the max amps number for each

There's the rub, I can't sort because my data is being looked up from somewhere else so I have to look for it where it's currently located. If it was only 5 or 10 points then it wouldn't be that big of a deal, but I have several thousand data points I'm sifting through.
 
Upvote 0
if they are formulas and being looked up from somewhere else. just copy both columns and paste special then the values on a new sheet. then sort and do the vlookup
 
Upvote 0
Ok, one of our guys here at work got it figured out... to do it he used an array formula and named ranges... Name the volts column "volts" and the amps column "amps" and use =MAX(IF(Volts=Target,Amps,0)) where Target is a named cell where I can put in 120, 208, or 240 and once you put in the formula do a <Ctrl> + <Shift> + <Enter> and then there should be {} around the formula so it looks like this on the formula bar... {=MAX(IF(Volts=Target,Amps,0))} ...it updates properly and works quite nicely. Just remember every time you go to that cell it resets to a regular formula so you have to hit <Ctrl> + <Shift> + <Enter> to make it an array formula! I'd never used array formulas before so I guess I learned something today! :biggrin:
Thanks for all the responses!
C
 
Upvote 0
Ok, one of our guys here at work got it figured out... to do it he used an array formula and named ranges... Name the volts column "volts" and the amps column "amps" and use =MAX(IF(Volts=Target,Amps,0)) where Target is a named cell where I can put in 120, 208, or 240 and once you put in the formula do a <ctrl> + <shift> + <enter> and then there should be {} around the formula so it looks like this on the formula bar... {=MAX(IF(Volts=Target,Amps,0))} ...it updates properly and works quite nicely. Just remember every time you go to that cell it resets to a regular formula so you have to hit <ctrl> + <shift> + <enter> to make it an array formula! I'd never used array formulas before so I guess I learned something today! :biggrin:
Thanks for all the responses!
C

Apparently I used some bad symbols or something... to enter the formula as a array formula use Ctrl + Shift + Enter

</enter></shift></ctrl></enter></shift></ctrl>
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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