Work with list to make 3rd column

[Guest]

I am currently making a sheet in which i am calculating time based on a job
that is being done. I want to be able to input a number in the first column,
(eg, number of extinguishers), second column would be the type of job (eg,
service the extinguisher) and the time required would show up in the 3rd
column. What is the best way to do that? Im so confused.

 

[Bernie Deitrick]

Stuart,

You could use a formula like

=IF(B2="Service the extinguisher", A2*1.25/24,IF(B2="Repaint the hallway",
A2*10/24,0))

But that gets complicated if you have a lot of tasks.

Better would be to make a "Task-time table" where you list the tasks down
one column, and the time required down the second.

So, suppose you have 100 different tasks. Enter their descriptions in Cells
G1 to G100, and the times in H1 to H100.

Then you could use a formula like

If (B2="",0,VLOOKUP(B2,$G$1:$H$100,2,False)/24)

You could also use the list in G1:G100 as a Data Validation list to ensure
that the entries are correct: "Service the extinguisher" is not equal to
"Service the extinguishers", which makes using formulas difficult.

HTH,
Bernie
MS Excel MVP