0

I am trying to use the absolute value formula to gather some information from another excel document. It works fine with positive values, but it does not work with negative values. I tried the max and min function also and it does not work. If anyone can give any recommendation it would be great! Thanks in advance for all your help.

This is the formula that I am trying to use:

=INDEX('folder[file.xlsx]tab'!$H$13:$M$13,MATCH(MAX(ABS('folder[file.xlsx]tab'!H17),ABS('folder[file.xlsx]tab'!I17),ABS('folder[file.xlsx]tab'!J17),ABS('folder[file.xlsx]tab'!M17)),'folder[file.xlsx]tab'!H17:M17,0))

It just gives N/A for negative variances or small decimal numbers, but works fine for positive numbers. I cannot change the information that I'm trying to extract from the other excel file. If anyone knows another version of formula that would work, I would appreciate it!

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
dkuka
  • 17
  • 4
  • Your MATCH is trying to find only positive numbers within H17:M17. So any negative numbers in there will not be found and returns an error. – Luck Oct 01 '19 at 16:41
  • Thank you, if I change to minimum it finds negative numbers. But if there are decimal point numbers it still returns error. I was wondering if there was a way to capture all under one formula. – dkuka Oct 01 '19 at 16:58

1 Answers1

1

Edited: This should find whether the ABS of the MAX or MIN is greater and then MATCH to that.

=IF(ABS(MAX(H17:O17))>ABS(MIN(H17:O17)),MATCH(MAX(H17:O17),H17:O17,0),MATCH(MIN(H17:O17),H17:O17,0))

Luck
  • 269
  • 1
  • 9
  • Thanks for your response! I actually have 8 variables but I made the formula smaller so its easy to read. – dkuka Oct 01 '19 at 17:52
  • @dkuka I've edited my answer. Try this in you INDEX formula instead of the MATCH you have. You can change H17:O17 to whatever your range is of your 8 variables. – Luck Oct 01 '19 at 18:35
  • this formula does not work with what I am trying to achieve. Im trying to compare the values in row H17:M17. And for example if H17 is the largest value I want the formula to return H13. Because the formula itself is much larger I thought maybe there is an easier way to do this. Thank you for your response! :) – dkuka Oct 04 '19 at 14:25
  • 1
    @dkuka This should be able to replace the MATCH portion of your INDEX function. Together it would look something like this: `=INDEX(H13:O13,IF(ABS(MAX(H17:O17))>ABS(MIN(H17:O17)),MATCH(MAX(H17:O17),H17:O17,0),MATCH(MIN(H17:O17),H17:O17,0)))` – Luck Oct 04 '19 at 16:38