-4

I'm trying to find an average of a large array of candidates compensation. Some of the cells contain text with multiple numbers showing a range such as, "$100k - $120k". Others are labeled as TC("120k TC") for total composition.

How would I be able to find the average of these numbers by using a something along the lines of substituting letters or parsing the string into a number WITHOUT changing the actual values listed? I do NOT want to mutate the original cell value of I only want to find an average of them all through a formula to bypass the additional "k", "TC" and "-" rendering them un-averageable as they are not parsed as numbers.

Iceyz
  • 13
  • 3
  • 2
    please see [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) and read [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) and don't forget to read [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) . Also check out the [tour](https://stackoverflow.com/tour), and ["help center".](https://stackoverflow.com/help/on-topic) – Foxfire And Burns And Burns Aug 08 '22 at 07:07
  • 1
    You can use helper columns that return the pure values – Ike Aug 08 '22 at 07:07
  • Make an extra data column and make decisions like do you take the average when two values are given. So $100k - $120k is then 110000 – Solar Mike Aug 08 '22 at 08:08

1 Answers1

0

Would need to clean up the texts in stages.

find if a certain text is present: eg.

 =IF(IFERROR(FIND("-",A1,1),"")<>"","- is present","")
 =IF(IFERROR(FIND("TC",A1,1),"")<>"","TC is present","")
 =IF(IFERROR(FIND("$",A1,1),"")<>"","$ is present","")

then split left and right price values if "-" is present: eg.

=LEFT(A1,FIND("-",A1,1))
=RIGHT(A1,FIND("-",A1,1))

then if texts are present, remove those texts: eg.

 =SUBSTITUTE(A1,"-","")
 =SUBSTITUTE(A1,"$","")
 =SUBSTITUTE(A1,"k","")

then can use trim() to remove spaces on ends, value() to convert text to number etc...

guest33
  • 16