CODEWITHSUNDEEP
excel
Max
Max

Reputation: 940

Calculation with Alphanumeric characters

I have data in cells A1:G1 like this: r2, r5, r7, r8, l4, l10, r20. So my data has either "r" or "l" at beginning of each value.

I'd like to carry out an operation like this. Find the values in a range(A1:G1) containing "r" (if there are any), and then take the difference between the max value and min value.

So in this example: Max value with r is 20, Min value with r is 2

Difference would be: r20 - r2 = r18

How can I achieve this in excel? Thanks for your help.

Note: I need a solution keeping the letter "r" in the result.

Upvotes: 1

Views: 261

Answers (1)

user4039065
user4039065

Reputation:

Try this array formula (finished with ctrl+shift+enter instead of just enter)

 ="R" & MAX(IF(LEFT(A1:G1)="R", --MID(A1:G1, 2, 99))) - MIN(IF(LEFT(A1:G1)="R", --MID(A1:G1, 2, 99)))

enter image description here

Upvotes: 3

Related Questions