Formula to Average 3 numbers separated by "/"

[dkennedycx]

Hello masters of excel!

I work in a data critical career and need to average voltage readings measured across three phases. Typically we do this manually and I'm hoping to average all in a single cell.

Example;
a. Cell A1 contains the following data "481/485/488"
b. Cell B1 outputs the Average of these values through a formula as "484"

We typically separate the values with a forward slash "/" but I'm content with any variable as long as I can do this without a macro. I greatly appreciate any help or advice!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1
2	481/485/488	484.6667
Data

Cell Formulas
Range	Formula
B2	B2	=AVERAGE(FILTERXML("<k><m>"&SUBSTITUTE(A2,"/","</m><m>")&"</m></k>","//m"))

 

[dkennedycx]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1
2	481/485/488	484.6667
Data

Cell Formulas
Range	Formula
B2	B2	=AVERAGE(FILTERXML("<k><m>"&SUBSTITUTE(A2,"/","</m><m>")&"</m></k>","//m"))

This works perfectly! Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.