# Trying to get AVE &SUM, considering code in the calcs; not dependent on specific cols

• The solutions below are dependent on the coded data "E.g. 95(AN)" in specific columns for each code: AN, AF, AP, SP.

These get the AVE and SUM, but are dependent on columns having the same code, and then the result doesn't display the code, only the number.

This code depends that "E.g. (AN)" are in every 4th column A, E, I and M.

Code
1. =AVERAGE(LEFT(A24,FIND("(",A24)-1),LEFT(E24,FIND("(",E24)-1),LEFT(I24,FIND("(",I24)-1),LEFT(M24,FIND("(",M24)-1)

Same for the SUM...

Code
1. =SUM(LEFT(A24,FIND("(",A24)-1),LEFT(E24,FIND("(",E24)-1),LEFT(I24,FIND("(",I24)-1),LEFT(M24,FIND("(",M24)-1))

This code below does a GREAT job getting the #1 Largest and 2nd Largest code AND it displays the result WITH the code "E.g. 98(AP)", BUT is still dependant on the coded data in certain columns...

Then I just use the formula MAXNTH(Range,1) for MAX and MAXNTH(Range,2) for 2nd largest.
E.g. MAXNTH(A1:D1,2)

---------------------------------------------------

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

I want to end up with the AVE and TOTAL SUM of HIGHEST (most often) code occurrence...
(0s are not considered)

E.g.
A B C D E F SUM of largest occurrence AVE of largest occurrence
0(AN) 96(AN) other data other data 95(AP) 93(AN) 189(AN) 95(AN)

Here's more examples of source data and columns attached:

## Files

• Book4.xls

(28.67 kB, downloaded 13 times, last: )
• Re: Trying to get AVE &amp;SUM, considering code in the calcs; not dependent on specific

Here's a more thorough explanation. I hope it helps

I need to be able to get an AVERAGE and SUM calculation, while considering the code and not be dependent on codes (AN, AF, AP, SP) being in their own separate columns.

I want to end up with the AVERAGE and TOTAL SUM of HIGHEST (largest total volume) code occurrence...
(0s are not considered)

E.g. // = new column
A // B // C // D // E // F // G-SUM of largest occurrence // H-AVERAGE of largest occurrence
0(SP) // 96(AN) // other data // other data // 95(AP) // 93(AN) // 189(AN) // 95(AN)

Since (AN) is the largest occurring code (largest total SUM), it's the ONLY one(s) considered in the calculations.

Note: It's based on the total SUM NOT the total number of occurrences. There could be 3 occurrences, like in the example below, and because the total number portion is less than 96, (AN) is considered the 'largest occurring code'.

E.g. // = new column
0(SP) // 96(AN) // other data // other data // 45(SP) // 9(SP) // 96(AN) // 96(AN)
A // B // C // D // E // F // G-SUM of largest occurrence // H-AVERAGE of largest occurrence