adding cells with non-traditional values

[gocolonel77]

I have been trying to figure our a way to assign a value to a non-numerical set and then have the cells automatically autosumed

For instance I want 3-C to mean 7.5 or 10-2 to mean 4.

So lets say cell C34 is the added hours I manually add up on a calculator to find the projected hours I am going to use for that day in that column. Call the column Sunday.

I apologize I have been trying to attach an example but the system is unavailable.

Thanks in advance for any help anybody can offer.

Jason

[Leith Ross]

Hello gocolonel77,

Welcome to the Forum!

Is there any relationship between how the non numerical values derive their numerical values or is it randomly assigned?

[mikerickson]

If you have a chart of codes and values you could use SUMIF.

[gocolonel77]

Yes thanks for asking their is a relationship. For instance 3-C means 3pm to 1030pm, so my numerical value would be 7.5.

1130-730 would have a numerical value of 8.

These non-numerical values would stay constant throughout the worksheet wherever they appear.

If you opened the spreadsheet "X" would represent 0

Hope this helps and thanks for your assistance

Jason

[gocolonel77]

Yes their is a relationship

For instance 3-C represents 3pm to 10:30pm which in numerical terms would be 7.5

If you opened the spreadsheet a cell with an "X" would represent 0

All non numerical values are constant throughout the worksheet. So if you see 3-C in cell A1 it also has the same value in cell C35

Hope this help clarify and thanks for your assistance,

Jason

[Leith Ross]

Hello Jason,

Do you have a table of these non numerical values and their numeric equivalents? If not, could you create one as a reference and post it? Another alternative would be to post a copy of the workbook. Having these will make it easier to find a workable solution to the problem.

[gocolonel77]

Attached is a workbook with my work schedule and a tab that has a few of the of the non-numerical references and what their actual value is. Their may be future/new combinations for instance 615-C which would be 4.25. I am hoping to have a list that I can add to when a new combination is needed, so when the cell adds the column above or across it will know what to do.


Thanks
Jason.