question

EinarOseiSies-4413 avatar image
0 Votes"
EinarOseiSies-4413 asked erinding-msft commented

Maximum Value

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?


office-excel-itpro
capture.jpg (210.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EinarOseiSies-4413
Please check Lz-3068's reply.
Any updates, please let us know.

0 Votes 0 ·

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

Hi @EinarOseiSies-4413

(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)



demo.png (28.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.