General discussion

Locked

Excel Formula...

By tang0312 ·
Hello,

I read this article (Ensure accurate data entry in Excel by using Data Validation to create drop-down lists)and I am constantly using the drop down list in my excel sheets. However, there is a piece of functionality that I am having difficulty with. I have 4 items in my list in column A: Project Management, Internet Programming, Graphic Design and Purchase. I have a second list in column B that are for hours: 0,1,2,3,4. What I would like to do is to assign a dollar value to each item in column A one and have excel populate column C with the correct dollar amount. For example, Project Management would be worth $25.00 and if I worked 2 hours as PM, then column C would read $50.00. I tried using the 'IF' formula in column C, but failed miserably at.

Does any one have any suggestions for me?

Thanks.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Formula...

by kees.valkenswaard In reply to Excel Formula...

The formula you need could be made in several ways. The simple solution might be a nested IF. However, I would prefer the VLOOKUP function. Make an array on a separate sheet in the same workbook with in
Column A Column B
Project Management 25,00
Internet Programming 27,50
Graphic Design 30,00
Purchase 29,00
[These are just examples !]
Select the eight cells and give these the range name Mylist
Use in Column C of your main sheet the following formula [in C2]:
=VLOOKUP(A2;Mylist;2;FALSE)*B2
That is all. propagate the formula.
NOTE If you are living in a country where they have never heard of international standardisation, your decimal symbol may be . [and not ,] while your list separator would be , [and not
With VLOOKUP if is very simple to add a value in the list [add a row somewhere in the middle, not at the ends or you would have to redefine the range]
Good luck

Collapse -

Excel Formula...

by Shanghai Sam In reply to Excel Formula...

Hi thanks.
Worked like a charm. But, the thing that I had a problem with was the seperators ',' and the ';'. If I used the ';' it wouldn't work but by using the ',' it worked beautifully. Now, I'm in Canada and I thought that we had heard of international standarisation. But, I guess not.

Thanks again.

Regards,

tk

Collapse -

Excel Formula...

by tang0312 In reply to Excel Formula...

This question was closed by the author

Related Discussions

Related Forums