Calculation for Average with char(160)

[gauravkrgautam]

Hi,

how should i use average functioin in attached sheet but i dont want it with helping columns.

As it contain char(160) in it .
please help me on this


Regards,

 

[Mike H..]

Hi,

how should i use average functioin in attached sheet but i dont want it with helping columns.

As it contain char(160) in it .
please help me on this


Regards,

Hi,


Well I'd get rid of all the chr(160) but if you want to do it as is then try this. See attached workbook.



=SUMPRODUCT((TRIM(SUBSTITUTE($B$2:$B$11,CHAR(160),""))*(TRIM(SUBSTITUTE($A$2:$A$11,CHAR(160),""))=TRIM(SUBSTITUTE($G$4,CHAR(160),"")))))/SUMPRODUCT(--(TRIM(SUBSTITUTE($A$2:$A$11,CHAR(160),""))=TRIM(SUBSTITUTE(G4,CHAR(160),""))))

 

[gauravkrgautam]

thanks for your effort bt answer are not matching

PFA

 

[Mike H..]

thanks for your effort bt answer are not matching

PFA

Hi,

My bad, I had an absolute reference to g4 and it should have been relative.

=SUMPRODUCT((TRIM(SUBSTITUTE($B$2:$B$11,CHAR(160),""))*(TRIM(SUBSTITUTE($A$2:$A$11,CHAR(160),""))=TRIM(SUBSTITUTE(G4,CHAR(160),"")))))/SUMPRODUCT(--(TRIM(SUBSTITUTE($A$2:$A$11,CHAR(160),""))=TRIM(SUBSTITUTE(G4,CHAR(160),""))))

 

[gauravkrgautam]

No problem, i should also take care of that little thing.
However i would like to tell you that you have make my work too much easy & quicker.
i will save around 1 hr of a day with this.


Regards,
Gaurav