Formula that averages letters

**cs1014** · 07-29-2009, 05:58 PM

I have a formula that averages letters(See formula 1). The letters are TRUE argument results of another formula(See formula 2). However, Formula 1 will only work if all of my letter arguments are "true". Most of my cells have a letter, however some do not and are blank or "" since that was my 'false' statement. Does anyone have a work around for this?

Formula 1
=CHAR(ROUND(AVERAGE(IF(ISTEXT(BB3:BB24),CODE(BB3:BB24),"")),0))

Formula 2
=IF(BB5>74%,"A",IF(AND(BB5>=45%,BB5<=74%),"B",IF(BB5<45%,"C","")))

**DonkeyOte** · 07-29-2009, 06:15 PM

Sorry, I don't understand quite where formula 2 is placed - formula 1 would imply the resulting letters appear in BB3:BB24 but formula 2 would imply otherwise (ie must be somewhere other than BB - unless some sort of iterative process is in operation)

On an aside, Formula 2 can be simplified given the rule that an IF exits upon first TRUE being established and action completed:

Code:

=IF(ISNUMBER(BB5),IF(BB5>74&#37;,"A",IF(BB5>=45%,"B","C")),"")

If for args sake we assume letters are in fact in range BC3:BC24 (rather than BB) and that the range may include Nulls to be excluded and you want the most frequently occuring letter then perhaps:

Code:

=INDEX(BC3:BC24,MODE(IF(ISTEXT(BC3:BC24)*(BC3:BC24<>""),MATCH(BC3:BC24,BC3:BC24,0))))
committed with CTRL + SHIFT + ENTER

I'm not quite sure how your Average will work given it will tend to give additional weight to the latter letters in the sequence (as they are worth more), ie a range containing 5 A's, 2 B's and 3 C's would using the Average method of:

Code:

=CHAR(ROUND(AVERAGE(IF(ISTEXT(BC3:BC24)*(BC3:BC24<>""),CODE(BC3:BC24))),0))
committed with CTRL + SHIFT + ENTER

generate a result of B as opposed to A (as generated by Mode method)... not sure which you want... (if either)

**cs1014** · 07-29-2009, 06:42 PM

You are a genius!!! Thank YOU!!!!!!!!! My VP will appreciate this =)

**cs1014** · 04-06-2010, 02:17 PM

I have four cells that need to be averaged together, but each cell is weighted differently.

C8= A - worth 25%
C13 = B - worth 30%
C18 = A - worth 10%
C24 = C - worth 35%

Is there a formula that would take the values in each cell, weight them, then average them together to create a letter?

I tried to do this on my own but the formula doesn't work:
=CHAR(ROUNDUP(SUM((CODE(C8)*0.25),C13,C18,C24),0.5))

**cs1014** · 04-06-2010, 02:58 PM

=CHAR(ROUNDUP(SUM(CODE((C8)*0.25),((C13)*0.30),((C18)*0.10),((C24)*0.35)),0.5))

That is the complete formula I have and it gives me a result of #VALUE.