Formula to write to read from 1 cell

[Kenjiorson]

Cell E5 auto add minutes from cell D5 when cell C5 input either 1 or 2.
1 is auto add 45 minutes and 2 is 90 minutes, 1 formula is in cell E1 and 2 is in cell E2.
=IF($C5=1,$D5+$E$1,IF($C5=2,$D5+$E$2,$D5))

If i want to add in Cell C5, S1 is 45 minutes and S2 is 90 minutes.
how do i edit the formula or need write a new one?

If cell i4 read from cell C5,
1=120, 2=160, S1=100 and S2=130
How do i write this formula too

Thank you in advance

 

[Alex Blakenburg]

You already have the beginnings of a lookup table in E1 & E2, why not make is a real lookup table.
The lookup table can be on another sheet and will autoexpand if you use an actual Excel Table,
This will make future maintenance much easier.

PS: The time function looks like this
=time(hour, minute, second)
Since you are working in mins I have set hour and second to 0 and just using the minutes position.
(you could use mins/60/24)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1											Lookup
2											ID	Mins to Add
3					Method 1	Method 2	Logic Function				1	60
4					Time()	/60/24					2	90
5		1	1	2:00 AM	3:00 AM	3:00 AM	Vlookup				S1	100
6		1	S2	2:00 AM	4:10 AM	4:10 AM	If				S2	130
7
Sheet1

Cell Formulas
Range	Formula
E5	E5	=$D5+TIME(0,IFERROR(VLOOKUP($C5,$K$3:$L$6,2,FALSE),0),0)
F5	F5	=$D5+IFERROR(VLOOKUP($C5,$K$3:$L$6,2,FALSE),0)/60/24
E6	E6	=$D6+TIME(0,IF($C6=1,60,IF($C6=2,90,IF($C6="S1",100,IF($C6="S2",130)))),0)
F6	F6	=$D6+IF($C6=1,60,IF($C6=2,90,IF($C6="S1",100,IF($C6="S2",130))))/60/24

GE]

 

[Kenjiorson]

You already have the beginnings of a lookup table in E1 & E2, why not make is a real lookup table.
The lookup table can be on another sheet and will autoexpand if you use an actual Excel Table,
This will make future maintenance much easier.

PS: The time function looks like this
=time(hour, minute, second)
Since you are working in mins I have set hour and second to 0 and just using the minutes position.
(you could use mins/60/24)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1											Lookup
2											ID	Mins to Add
3					Method 1	Method 2	Logic Function				1	60
4					Time()	/60/24					2	90
5		1	1	2:00 AM	3:00 AM	3:00 AM	Vlookup				S1	100
6		1	S2	2:00 AM	4:10 AM	4:10 AM	If				S2	130
7
Sheet1

Cell Formulas
Range	Formula
E5	E5	=$D5+TIME(0,IFERROR(VLOOKUP($C5,$K$3:$L$6,2,FALSE),0),0)
F5	F5	=$D5+IFERROR(VLOOKUP($C5,$K$3:$L$6,2,FALSE),0)/60/24
E6	E6	=$D6+TIME(0,IF($C6=1,60,IF($C6=2,90,IF($C6="S1",100,IF($C6="S2",130)))),0)
F6	F6	=$D6+IF($C6=1,60,IF($C6=2,90,IF($C6="S1",100,IF($C6="S2",130))))/60/24

GE]

Hi sorry i think i wrote wrongly, i will edit the part

If cell i4 read from cell C5,
1=$120, 2=$160, S1=$100 and S2=$130
How do i write this formula with only input in C5 and cell i4 will show

Thanks thanks for the help