Excel: Replacing Value Mid-Formula

Viewed 66

enter image description here

In the data sheet I'm working on, I want to calculate the Min, Max, and Average for each row and have the formula recognize the cells that have BDL as being = 1.35 without changing the value of the cell to 1.35. Is there a way to do such a thing? I'm pretty sure I've seen it done in the past. I believe I've seen a constant value added on to the end of functions.

1 Answers

This formula should work:

=AVERAGE(IFERROR(NUMBERVALUE(SUBSTITUTE(B8:F8,"BDL","1.35")),""))

(change B8:F8 to fit which columns you have your data in.)

Related