Maximum Value

Question

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.

How can I input a formula to get the largest or maximum value in column J?

Answer 1

Hi @Einar Osei Sies

(my decimal delimiter is the comma)

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)