Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
The Something Awful Forums > Discussion > Serious Hardware/Software Crap > Haus of Tech Support > Excel Question - Specialized Max Value of Row + Address of Max Value
 
  • Post
  • Reply
Dog Kisser
Mar 30, 2005

But People have fears that beasts do not. Questions, too.
Been banging my head against this wall for a bit, and didn't think of checking here until just now. Essentially, I want to find the max value of a row and then subsequently the cell address of that cell. Normally easy enough, but there's a bit of a hiccup here.

These numbers with "<" signs need to be included too. My current formula for finding the max is
code:
{=MAX(--SUBSTITUTE(SUBSTITUTE((C14:O14),"<",""),"-",0))}
which will accurately give me the magnitude of the highest cell - 1.5, in row 14, for instance. Conditional formatting is set up such that if a value in column C-O is higher than the standard in column B it'll flag it orange, BUT if the number is higher and preceded with a "<" it'll flag it blue. Currently, if a "<" number is above the standard it'll show up in our max concentration column as just the number.

SO: I need something that will first strip the non-number characters out, find the max, and then put the 'less than' sign back if the highest value had one there in the first place! Following that I need a separate formula to find the address of the cell containing that value within the row. Let me know if any of that is confusing!

* # ? Jun 4, 2019 16:41
  • Quote
Adbot
ADBOT LOVES YOU

# ? May 6, 2024 23:52
  • Quote
  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply
The Something Awful Forums > Discussion > Serious Hardware/Software Crap > Haus of Tech Support > Excel Question - Specialized Max Value of Row + Address of Max Value