Summing non-numerical data

Question

I am trying to quantify non-quantifiable data; I have a few columns of data and I need to find a formula that converts the data to numbers and sums them.

Example: One of my columns has data points "high" "medium" and "low" while another column has dollar amounts that need to be assigned a single number based on the dollar range (i.e. 1-249,999 is assigned a "1", 250,000-4,999,999 is assigned a "2", etc.) and another column has non-sequential text that needs to be converted (FFP = 1, LH=2, etc.) I have tried a number of different functions but haven't found a formula yet that can both convert my data into numbers and also sum the data in the same cell/function.

Answer 1

Hi

You can add corresponding assist column to convert text to number, then get result by sum them. You can hide the column to make the view clear.

H2=IFERROR(IFS(D2="High",5,D2="Medium-High",4,D2="Medium",3,D2="Low-Medium",2,D2="Low",1),"")

I2=IFERROR(IFS(E2="High",5,E2="Medium-High",4,E2="Medium",3,E2="Low-Medium",2,E2="Low",1),"")

J2=IFERROR(IFS(F2="IAA",6,F2="Award FEE",5,F2="TO/DO",4,F2="IDIQ",3,F2="FFP - A&E",2,F2="FFP",1),"")

K2=IFERROR(IFS(AND(G2>0,G2<250000),1,AND(G2>=250000,G2<5000000),2,AND(G2>=5000000,G2<100000000),3,G2>=100000000,4),"")

Drag down to fulfill the column. 

Note: you can set your rules like FFP = 1, LH=2, by modify the formula. See IFS fucntion. 

Regards,

Eric

Answer 2

To:  KPitek

re:  need formula

Start by using 3 additional columns to convert the existing data to the numbers you want.

So if your data is in Columns A, B, C , insert or use Columns D, E, F for the conversion formulas.

Sum each row in Columns D, E, F and you are done.

If you must have a single formula then you can combine the three existing formulas into one formula.

That is generally how the complicated formulas, posted in this forum as answers, are arrived at.

'---

Some new, some older Excel programs (now free) at MediaFire...Download (no ads) from... *http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents*

Answer 3

Hi KPitek，

I'm willing to help you but it's difficult to know your condition better without picture.

Could you capture the screenshot of the sample and describe what you want to get again based on the screenshot?  

To upload screenshot, click picture icon.  

Thanks for your understanding. 

Regards,

Eric

Answer 4

Eric,

I am trying to calculate "Contract Complexity" (Column H) by somehow converting the non-numerical values in Columns D, E, F, and G and summing them. I can do it manually, but Excel has so many functions, I know there has to be a better way that will reduce human error!

I would ideally like to assign values like High = 5, High-Medium = 4, Medium = 3, etc., and I want to assign values 1 through 5 to ranges of the dollar amounts as well.

Answer 5

Thank you! This worked perfectly.