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.

3 total posts (Page 1 of 1)  
| 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

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums