Thread: Assign a numeric value to a text string

Hi All

I am an Excel Newbie and I am trying to create a utilisation report.This will enable a user to select from a number of data validation drop down lists so I can run a report on how much time is spent on what activity and what market segement. The only time element I am interested in is AM or PM so I have tried using named constants equaling 0.5 but cannot get it to sum the column please can anybody help.

Regards Andy C

Originally Posted by Bryan Hessey

Hi,

If this is a cell you can format the cell to Custom AM/PM

If text then =Text(A1,"AM/PM") should work

HTH

---

Hi Bryan

Thank You for your reply

I probably didn't make myself clear what I would like to achieve is have a value of 0.5 associated with an activity in a list e.g. Administration=0.5 and also a market segment and product associated with a value so I can then create a char tor pivot table showing: time / activity market segment / product. The way I thought it could work was to create named constants for each activity and then sum the column. I have attached the rough workbook if that is any help.

Regards Andy C

I am unable to download attachments due to the net nanny software on this system.
However if I understand your problem correctly from your description...

You want to create a list of headings with an associated numeric value,
You than want to apply the numeric value to a set of data that will consist of values that match your headings. Thus (for example):
Set Cells A1 to D1 to &
Set Cells A2 to D2 to:

Admin, What, This, That,
0.5, 0.6, 0.7, 0.8

Set Cell A4 to (for example) "What",
Set Cell B4 to : =INDEX(A$2:D$2,MATCH(A4,A$1:D$1))
The value calculated for B4 will be 0.6

Column A (from A4 down) has your list of values,
Cell B4 can be replicated down as required.

Is this something like what you are after?

Mark