Skip to main content
Web Applications

Return to Question

added 131 characters in body
Source Link
Clayton
Clayton
  • 45
  • 1
  • 6

I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text. If the cell contains text, it will only be an L to the right of the numeric value.

Example of cells I would like to take the average of: Average of Cells

I have tried something like this:

=Average(VALUE(LEFT(A1:H1,IF(RIGHT(A1:H1,L),VALUE(LEFT(A1:H1,LEN(A1:H1)-1)),VALUE(LEN(A1:H1))))))

which is an attempt to remove the L from the value if it's there, otherwise to just give the value in the cell. In my research, I have found people talking about using an array formula, but my attempts to make that work have been unsuccessful.

Edit: Another thing that would be ideal is if the function could skip over empty cells like the average function normally does.

I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text. If the cell contains text, it will only be an L to the right of the numeric value.

Example of cells I would like to take the average of: Average of Cells

I have tried something like this:

=Average(VALUE(LEFT(A1:H1,IF(RIGHT(A1:H1,L),VALUE(LEFT(A1:H1,LEN(A1:H1)-1)),VALUE(LEN(A1:H1))))))

which is an attempt to remove the L from the value if it's there, otherwise to just give the value in the cell. In my research, I have found people talking about using an array formula, but my attempts to make that work have been unsuccessful.

I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text. If the cell contains text, it will only be an L to the right of the numeric value.

Example of cells I would like to take the average of: Average of Cells

I have tried something like this:

=Average(VALUE(LEFT(A1:H1,IF(RIGHT(A1:H1,L),VALUE(LEFT(A1:H1,LEN(A1:H1)-1)),VALUE(LEN(A1:H1))))))

which is an attempt to remove the L from the value if it's there, otherwise to just give the value in the cell. In my research, I have found people talking about using an array formula, but my attempts to make that work have been unsuccessful.

Edit: Another thing that would be ideal is if the function could skip over empty cells like the average function normally does.

Improved formatting, added tags
Source Link
marikamitsos
marikamitsos
  • 13.1k
  • 4
  • 34
  • 59

I am trying to figure out a formula that will allow me to find the averageaverage of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and textsome cells contain both numeric values and text. If the cell contains text, it will only be an LL to the right of the numeric value.

Example of cells I would like to take the average of: Average of Cells

I have tried something like this:

=Average(VALUE(LEFT(A1:H1,IF(RIGHT(A1:H1,L),VALUE(LEFT(A1:H1,LEN(A1:H1)-1)),VALUE(LEN(A1:H1))))))

which is an attempt to remove the LL from the value if it's there, otherwise to just give the value in the cell. In my research, I have found people talking about using an array formula, but my attempts to make that work have been unsuccessful.

Any help with this would be amazing!

I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text. If the cell contains text, it will only be an L to the right of the numeric value.

Example of cells I would like to take the average of: Average of Cells

I have tried something like this:

=Average(VALUE(LEFT(A1:H1,IF(RIGHT(A1:H1,L),VALUE(LEFT(A1:H1,LEN(A1:H1)-1)),VALUE(LEN(A1:H1))))))

which is an attempt to remove the L from the value if it's there, otherwise to just give the value in the cell. In my research, I have found people talking about using an array formula, but my attempts to make that work have been unsuccessful.

Any help with this would be amazing!

I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text. If the cell contains text, it will only be an L to the right of the numeric value.

Example of cells I would like to take the average of: Average of Cells

I have tried something like this:

=Average(VALUE(LEFT(A1:H1,IF(RIGHT(A1:H1,L),VALUE(LEFT(A1:H1,LEN(A1:H1)-1)),VALUE(LEN(A1:H1))))))

which is an attempt to remove the L from the value if it's there, otherwise to just give the value in the cell. In my research, I have found people talking about using an array formula, but my attempts to make that work have been unsuccessful.

Source Link
Clayton
Clayton
  • 45
  • 1
  • 6

Average of only the numeric part of cell values when some of the cells contain text and numeric values

I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text. If the cell contains text, it will only be an L to the right of the numeric value.

Example of cells I would like to take the average of: Average of Cells

I have tried something like this:

=Average(VALUE(LEFT(A1:H1,IF(RIGHT(A1:H1,L),VALUE(LEFT(A1:H1,LEN(A1:H1)-1)),VALUE(LEN(A1:H1))))))

which is an attempt to remove the L from the value if it's there, otherwise to just give the value in the cell. In my research, I have found people talking about using an array formula, but my attempts to make that work have been unsuccessful.

Any help with this would be amazing!