Maximum Value

Einar Osei Sies 41 Reputation points
2021-04-14T03:30:54.19+00:00

Please I need help in finding the maximum value in a range. Capture.JPG
In column J are a combination of strings and formulas. I attached an info column K to aid in understanding my problem and formula in column J.
87601-capture.jpg
How can I input a formula to get the largest or maximum value in column J?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,638 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz._ 8,986 Reputation points
    2021-04-14T06:15:37.077+00:00

    Hi @Einar Osei Sies

    (my decimal delimiter is the comma)

    PIC

    Option 1 (complex & inefficient), in A7:
    ="ABC"&TEXT(MAX(VALUE(SUBSTITUTE(A2:A5,"ABC",))),"###.00")

    NB: you might have to validate it with Ctrl+Shift+Enter

    Option 2 (more efficient):

    • In your column J, only keep the SUM part of your current formula, i.e. in J4: =SUM(F4-G4)
    • Select cells in column J > Format cells > Number > Custom: "ABC"###.00,"ABC"-###.00,"ABC"0.00
      Then use a regular MAX formula, i.e.: =MAX(J4:J104)
    0 comments