# Average data when cells contain text and data

• I am want to figure out the formula to average the weekly weight when the cells contain the day(as text) and the weight would entered after it. Below is an example: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 905"]

[tr]

[td]

Sunday/238.4

[/td]

[td]

Monday/236.4

[/td]

[td]

Tuesday/236.6

[/td]

[td]

Wednesday/235.6

[/td]

[td]

Thursday/236.0

[/td]

[td]

Friday/239.0

[/td]

[td]

Saturday/

[/td]

[/tr]

[/TABLE]
I would also like for it to show the average even is some of the weight has not been entered on some of the days. The weight will always be the last 5 characters counting the decimal point.

I did come up with the following but i was hoping for something simpler and also be able to show avg even when a weight was not yet entered.
=AVERAGE(VALUE(RIGHT(B3,5)),RIGHT(D3,5),RIGHT(C3,5),RIGHT(E3,5),RIGHT(F3,5),RIGHT(G3,5),RIGHT(H3,5))

• Try this:

=AVERAGE(VALUE(RIGHT(\$B\$3:\$H\$3,5))

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Thanks, but that gave me a #VALUE error. I had tried that previously with the same results. I thought maybe I was placing the 5 in the wrong place or something. Separating them all out was the only way I could get it to work. Also, is there a way to place an IF statement or something to only include the days that have the weight entered? It can be if the last character is a "/" or something so that it does not include that day?

I used to work with Excel quite a bit but haven't done much in the last several years and now that I'm retired, I'm realizing my brain loses cells as quickly as I lose my muscles when they haven't been used.

Thanks again.
Ed

• Maybe try changing it to an AVERAGEIFS statement.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!