# Trying to understand cell formulas when extra characters used, learn to SUM and AVE

• I have to apply several calculations to the number portion of a range of cell contents.

I trying to learn how to better describe a layout like this. 69(SP), 109(AN), etc. It's always this format with 1-3 digits then 2 letters in parenthesis.

Someone helped me at least removed the '(SP)' part in a whole different column so I could easily SUM, MAX, AVE, etc. for any given range, but then I lose track of the code. It's important too.

=MID(R3,FIND("(",R3)+1,FIND(")",R3)-FIND("(",R3)-1)

E.g. I need to SUM or get the AVE all the (SP)s, (AN)s, (AP)s and (AF)s in the range of A3-D3, R3-AA3 and down. Some sample values are 69(SP), 109(AN), 5(AP) and 98(AF).

I have the code so far to do MAX, and 2nd largest value in a range, but it's written in Greek too me. I've done some vba, but formulas just don't make sense to me! Here's the code: http://www.ozgrid.com/forum/showthread.php?t=203700

Thanks!
Mark

• Re: Trying to understand cell formulas when extra characters used, learn to SUM and A

[TABLE="width: 1070"]

[tr]

[TD="colspan: 6"]Ok say we get answers in columns A-D, and data columns in H-K and P-T.[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="colspan: 5"]We can get the MAX and 2nd largest, value for each row, so…[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"]1) Getting the SUM of all (AN) in A2, etc. the SUM of (AF) in B2, etc. SUM of (AP) in C2 etc. SUM of (SP) in D2 etc. [/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"]2) Even more important would be then to take it to an AVE of each code for the given range.[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"]Calculations here on the left hand columns one for each of 4 codes, per column[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"]Sometimes the source field codes are in column order, sometimes they're not. And are also a formula in itself.[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

AN

[/td]

[td]

AF

[/td]

[td]

AP

[/td]

[td]

SP

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

X(AN)

[/td]

[td]

X(AF)

[/td]

[td]

X(AP)

[/td]

[td]

X(SP)

[/td]

[td]

99(AN)

[/td]

[td]

0

[/td]

[td]

53(AN)

[/td]

[td]

96(AN)

[/td]

[td][/td]

[td]

66(AN)

[/td]

[td]

31(SP)

[/td]

[td]

82(AN)

[/td]

[td]

98(SP)

[/td]

[td]

31(AF)

[/td]

[/tr]

[tr]

[td]

X(AN)

[/td]

[td]

X(AF)

[/td]

[td]

X(AP)

[/td]

[td]

X(SP)

[/td]

[td]

100(AN)

[/td]

[td]

0

[/td]

[td]

100(AN)

[/td]

[td]

92(AN)

[/td]

[td][/td]

[td]

95(AF)

[/td]

[td]

5(AN)

[/td]

[td]

89(AF)

[/td]

[td]

57(AF)

[/td]

[td]

31(AF)

[/td]

[/tr]

[tr]

[td]

X(AN)

[/td]

[td]

X(AF)

[/td]

[td]

X(AP)

[/td]

[td]

X(SP)

[/td]

[td]

75(AN)

[/td]

[td]

18(AP)

[/td]

[td]

96(AN)

[/td]

[td]

65(AN)

[/td]

[td][/td]

[td]

67(AN)

[/td]

[td]

29(AF)

[/td]

[td]

81(AF)

[/td]

[td]

90(AN)

[/td]

[td]

10(AF)

[/td]

[/tr]

[tr]

[td]

X(AN)

[/td]

[td]

X(AF)

[/td]

[td]

X(AP)

[/td]

[td]

X(SP)

[/td]

[td]

92(AN)

[/td]

[td]

5(SP)

[/td]

[td]

43(SP)

[/td]

[td]

100(AN)

[/td]

[td][/td]

[td]

59(AN)

[/td]

[td]

33(AP)

[/td]

[td]

100(AP)

[/td]

[td]

90(AN)

[/td]

[td]

16(AF)

[/td]

[/tr]

[tr]

[td]

X(AN)

[/td]

[td]

X(AF)

[/td]

[td]

X(AP)

[/td]

[td]

X(SP)

[/td]

[td]

99(AN)

[/td]

[td]

0

[/td]

[td]

100(AN)

[/td]

[td]

96(AN)

[/td]

[td][/td]

[td]

100(AN)

[/td]

[td]

0

[/td]

[td]

100(AN)

[/td]

[td]

100(AN)

[/td]

[td]

31(AF)

[/td]

[/tr]

[tr]

[td]

X(AN)

[/td]

[td]

X(AF)

[/td]

[td]

X(AP)

[/td]

[td]

X(SP)

[/td]

[td]

93(AN)

[/td]

[td]

7(AF)

[/td]

[td]

70(AF)

[/td]

[td]

96(AN)

[/td]

[td][/td]

[td]

98(AN)

[/td]

[td]

0

[/td]

[td]

92(AN)

[/td]

[td]

97(AN)

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

X(AN)

[/td]

[td]

X(AF)

[/td]

[td]

X(AP)

[/td]

[td]

X(SP)

[/td]

[td]

100(AN)

[/td]

[td]

0

[/td]

[td]

88(AN)

[/td]

[td]

87(AN)

[/td]

[td][/td]

[td]

99(AN)

[/td]

[td]

0

[/td]

[td]

84(AN)

[/td]

[td]

99(AN)

[/td]

[td]

12(AN)

[/td]

[/tr]

[tr]

[td]

X(AN)

[/td]

[td]

X(AF)

[/td]

[td]

X(AP)

[/td]

[td]

X(SP)

[/td]

[td]

100(AN)

[/td]

[td]

0

[/td]

[td]

96(AN)

[/td]

[td]

98(AN)

[/td]

[td][/td]

[td]

95(AF)

[/td]

[td]

4(AN)

[/td]

[td]

60(AF)

[/td]

[td]

87(AF)

[/td]

[td]

24(AF)

[/td]

[/tr]

[/TABLE]

• Re: Trying to understand cell formulas when extra characters used, learn to SUM and A

SO SORRY! I was busy finishing taxes then couldn't find how to upload a file, so put it off a again.

Here's the file with examples. Another issue I ran into is running functions with cells that contain other formulas not values, within the stated range.

Thank you SO much for any help!

Quote from AlanSidman;790676

Make it easy for us to help you. Please upload a sample workbook. We are all volunteers here and no one really wants to guess at the actual format of your data and recreate your workbook.

## Files

• Book1.xls

• Re: Trying to understand cell formulas when extra characters used, learn to SUM and A

In cell A10: =AVERAGE(LEFT(E9,FIND("(",E9)-1),LEFT(F9,FIND("(",F9)-1),LEFT(G9,FIND("(",G9)-1),LEFT(H9,FIND("(",H9)-1),LEFT(I9,FIND("(",I9)-1))

Use this same concept for sums

In Cell A13: =SUM(LEFT(E13,FIND("(",E13)-1),LEFT(I13,FIND("(",I13)-1),LEFT(M13,FIND("(",M13)-1))

copy over as needed

• Re: Trying to understand cell formulas when extra characters used, learn to SUM and A

Thank you SO much!

Two quick questions. One is what's in your code (different than other code like the MAXNTH code I mentioned before: http://www.ozgrid.com/forum/showthread.php?t=203700&page=2) that makes it NO issue when a cell or range of cells contains a formula rather than just a straight value?

The other question is how can I display the answer in your formulas to include the code? [E.g. 87(AP)] that I showed it in the sample post but forgot to mention it in the sample file.

• Re: Trying to understand cell formulas when extra characters used, learn to SUM and A

I just remembered how to add text before or after a formula result! In my case I just need to add &"(AN)" as one example.

• Re: Trying to understand cell formulas when extra characters used, learn to SUM and A

Since this is along the same lines with using the exact same data format xx(code) I'm going to post this here. I hope it's ok.

There is another scenario where the columns are not used (E.g. AN, AF, AP, SP) and it has to be able to recognize each code and get SUM, AVE etc. by code now.

Here's a couple examples. More in the attached file.

91(SP) 94(SP) 86(AN) 92(SP)
Largest AVE
Result code: 92(SP)
Largest SUM
Result code: 277(SP)

82(AN) 98(AP) 86(AN) 89(AP)
Largest AVE
Result code: 94(AP)
Largest SUM
Result code: 187(AP)

## Files

• Book3.xls

• Re: Trying to understand cell formulas when extra characters used, learn to SUM and A

So does this change the game or concept completely?

I need to be able to (consider) the code in the calculations and not be dependent on AN, AF, AP, SP in their own columns.

I want to end up with the AVE or TOTAL of each code occurrence...
(0s are not considered)

Here's another example of source data and columns:

Columns AI AJ

82(SP) 53(AN)
112(AN) 24(AF)
125(AF) 9(AN)
139(AN) 0
108(AN) 67(AF)

--------------
Columns AU AV

82(SP) 13(AN)
96(AN) 0
85(AF) 9(AN)
99(AN) 0
67(AN) 27(AF)

--------------
Columns BA BB

100(SP) 0
99(AN) 0
50(SP) 34(AP)
96(AN) 5(AP)
99(AN) 0
--------------

Here's the result or target values:

AN Fullname AF Fullname AP Fullname SP Fullname
33(AN) 0(AF) 0(AP) 88(SP)
102(AN) 24(AF) 0(AP) 0(SP)
9(AN) 105(AF) 34(AP) 50(SP)
111(AN) 0(AF) 5(AP) 0(SP)
91(AN) 47(AF) 0(AP) 0(SP)