Microsoft Office Forums need help pulling max + or - value from a pool of numbers

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-09-2017, 03:38 PM
kevinU kevinU is offline need help pulling max + or - value from a pool of numbers Windows 10 need help pulling max + or - value from a pool of numbers Office 2016
Novice
need help pulling max + or - value from a pool of numbers
 
Join Date: Mar 2017
Posts: 5
kevinU is on a distinguished road
Default need help pulling max + or - value from a pool of numbers

I cant figure out how to retrieve the data from a different tab/sheet i need without ABS but I want to retrieve it if its negative or positive not just positive. Here is how I have it set up.

I have one tab/sheet with column A containing variables like 1L, 1R, 2L, 2R, etc. Then in column B-Z there are numbers like .024, -.50, 1.5, -3.5, ect.

In a second tab/sheet i'm trying to first reference which row I want by matching the 1L, 2L data in column A. There is a lot of data and so there are multiple cells that will have 1L or 2L or others. Once I find all the cells with 1L for example, I want to then look at column B and retrieve the cell with the max value whether its positive or negative. The data sheet would look like this: Trying to make it look like an excel sheet.

A | b | c, etc
____________________
1l | .25 |
2l | -.50 |
1l | 1.5 |
4R | -2.5 |
5r | -.4 |
1l | -.1.7 |
2l | 1.1 |

The sheet im pulling data to would look like this:



A | B
_____________
1L |?????
1R |
2L |
2R |
3L |
3R |


In the ??? cell i want to search the first sheet and find all rows with 1L then return the largest value in column B. In this example I want the end result to return -1.7 because its the largest absolute number in the 1L catagory.

So far I've only got this far. I can get the number but its absolute only. I want it to be positive or negative depending on which is further from 0. If it helps to know I'm working with data that fits in 2394 rows by 30 colums so the formula below is with that data range.

What I have so far:
=MAX(ABS(IF('sheet 1'!$A$1:$A$2394=A1,'sheet 1'!$B$1:$B$2394)))

Thanks and sorry if im missing a better explanation im new to excel

Kevin
Reply With Quote
  #2  
Old 03-09-2017, 05:40 PM
jeffreybrown jeffreybrown is offline need help pulling max + or - value from a pool of numbers Windows Vista need help pulling max + or - value from a pool of numbers Office 2007
Expert
 
Join Date: Apr 2016
Posts: 549
jeffreybrown will become famous soon enoughjeffreybrown will become famous soon enough
Default

Hi Kevin,

Based on your sample, put this in the 2nd sheet in B1 and copy down...

=IFERROR(INDEX(Sheet1!$B$1:$B$7,LARGE(IF(Sheet1!$A $1:$A$7=A1,ROW(Sheet1!$A$1:$A$7)-ROW(Sheet1!$A$1)+1),1)),"")

IMPORTANT
  • This is an array formula
  • Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
  • If entered correctly, the formula will be enclosed in {brackets}
  • Do not enter the {brackets} manually
Reply With Quote
  #3  
Old 03-09-2017, 06:14 PM
kevinU kevinU is offline need help pulling max + or - value from a pool of numbers Windows 10 need help pulling max + or - value from a pool of numbers Office 2016
Novice
need help pulling max + or - value from a pool of numbers
 
Join Date: Mar 2017
Posts: 5
kevinU is on a distinguished road
Default

Jeff thanks so much for the help. It's not working but I'm trying to transpose it to what i have and i may be messing it up. My actual formula is this:

=MAX(ABS(IF('Power data'!$A$1:$A$2394=A2,'Power data'!$B$1:$B$2394)))

I tried to make it simple on the post but these are the correct coordinates. Can you tell me what you had again with this info? I tried and got this:

=IFERROR(INDEX('Power data'!$B$1:$B$2394,LARGE(IF('Power data'!$A$1:$A$2394=A1,ROW('Power data'!$A$1:$A$2394)-ROW('Power data'!$A$1)+1),1)),"")

Is this right? it woked on one sell but when i drag to auto fill to the cell below it doesnt work.

Kevin
Reply With Quote
  #4  
Old 03-09-2017, 07:29 PM
jeffreybrown jeffreybrown is offline need help pulling max + or - value from a pool of numbers Windows Vista need help pulling max + or - value from a pool of numbers Office 2007
Expert
 
Join Date: Apr 2016
Posts: 549
jeffreybrown will become famous soon enoughjeffreybrown will become famous soon enough
Default

Can you attach a sample file and what results you expect on the 2nd sheet?
Reply With Quote
  #5  
Old 03-09-2017, 07:55 PM
kevinU kevinU is offline need help pulling max + or - value from a pool of numbers Windows 10 need help pulling max + or - value from a pool of numbers Office 2016
Novice
need help pulling max + or - value from a pool of numbers
 
Join Date: Mar 2017
Posts: 5
kevinU is on a distinguished road
Default

ok here it is. The first tab is what it should look like. The second is the data, i had to cut 2/3 out to make it small enough so it should be 2394 rows by 30 columns, but you can still use it to find 1L, 1R through 10L, 10R to test it). The third tab is where i need the formula to put the results.

In case it helps to visualize this is an EEG map of the brain so im looking at different frequencies from 1-30 in 44 different parts of the brain both left and right. So in this case I'm trying to find the max score, or furthest from 0 whether its positive or negative at 1hz in area 1 left of the brain. Some areas have 3 different points and some like 20 have almost 40 different points i have to compare.

again thanks so much.

Kevin
Attached Files
File Type: xlsx comparison.xlsx (490.3 KB, 3 views)
Reply With Quote
  #6  
Old 03-10-2017, 02:23 PM
kevinU kevinU is offline need help pulling max + or - value from a pool of numbers Windows 10 need help pulling max + or - value from a pool of numbers Office 2016
Novice
need help pulling max + or - value from a pool of numbers
 
Join Date: Mar 2017
Posts: 5
kevinU is on a distinguished road
Default

Found the answer on a different forum, Thanks for the suggestion Jeff , here are the 2 codes that ended up working in case anyone was interested.

=IFERROR(LOOKUP(2,1/(MAX(IFERROR(ABS(IF('Power data'!$A$1:$A$2394=A2,'Power data'!$B$1:$B$2394,"")),""))=ABS(IF('Power data'!$A$1:$A$2394=A2,'Power data'!$B$1:$B$2394,""))),'Power data'!$B$1:$B$2394),"")

Attached is the code in action.


The other code that worked is this, it was able to be copied down and left which was nice.

=IF(MAX(ABS(INDEX(IF($A2='Power Data'!$A$2:$A$1397,'Power Data'!$B$2:$AE$1397),,MATCH(B$1,'Power Data'!$B$1:$AE$1,0))))=-MIN(INDEX(IF($A2='Power Data'!$A$2:$A$1397,'Power Data'!$B$2:$AE$1397),,MATCH(B$1,'Power Data'!$B$1:$AE$1,0))),MIN(INDEX(IF($A2='Power Data'!$A$2:$A$1397,'Power Data'!$B$2:$AE$1397),,MATCH(B$1,'Power Data'!$B$1:$AE$1,0))),MAX(INDEX(IF($A2='Power Data'!$A$2:$A$1397,'Power Data'!$B$2:$AE$1397),,MATCH(B$1,'Power Data'!$B$1:$AE$1,0))))

wish i could explain it but its way above my head but here it is none the less

Kevin
Attached Files
File Type: xlsx Book1.xlsx (10.9 KB, 2 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
need help pulling max + or - value from a pool of numbers Central Resource Pool OTPM Project 3 12-01-2015 11:27 AM
Resource pool with Sharepoint codeghi Project 3 02-28-2014 11:39 AM
Resource Pool / export Bec82 Project 3 08-16-2011 11:11 AM
Use only rsource pool gdallas Project 0 11-25-2010 01:10 PM
need help pulling max + or - value from a pool of numbers Need help with pulling out information mtnguye9 Excel 6 07-24-2010 07:52 PM


All times are GMT -7. The time now is 05:11 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft