Converting [h]:mm value with toggle switch

[masud8956]

Hi all,

I am trying to automatically convert a few 'durations' (in [h]:mm format) located at range D17:D21.

I need to use some toggle switches at E16:I16 to select what ratio to be displayed at cells E16:I16. e.g. at E16, the switch should either show Ratio 0.75:1 or Ratio 1:0.75 and that way it goes all the way up to I16 (except F16 as it shows just 1:1, no toggle required). The ratios are demarcated by "/" and colored for easy understanding.

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"]A15[/TD]
[TD="align: center"]B15[/TD]
[TD="align: center"]C15[/TD]
[TD="align: center"]D15[/TD]
[TD="align: center"]E15[/TD]
[TD="align: center"]F15[/TD]
[TD="align: center"]G15[/TD]
[TD="align: center"]H15[/TD]
[TD="align: center"]I15[/TD]
[/TR]
[TR]
[TD="align: center"]A16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]DURATION[/TD]
[TD="align: center"]Ratio
(0.75:1)/(1:0.75)[/TD]
[TD="align: center"]Ratio
1:1[/TD]
[TD="align: center"]Ratio
(1.5:1)/(1:1.5)[/TD]
[TD="align: center"]Ratio
(2:1)/(1:2)[/TD]
[TD="align: center"]Ratio
(2.5:1)/(1:2.5)[/TD]
[/TR]
[TR]
[TD="align: center"]A17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]1:00/0:33[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]0:30/1:07[/TD]
[TD="align: center"]0:22/1:30[/TD]
[TD="align: center"]0:18/1:52[/TD]
[/TR]
[TR]
[TD="align: center"]A18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]1:20/0:45[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]0:40/1:30[/TD]
[TD="align: center"]0:30/2:00[/TD]
[TD="align: center"]0:24/2:30[/TD]
[/TR]
[TR]
[TD="align: center"]A19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1:30[/TD]
[TD="align: center"]2:00/1:07[/TD]
[TD="align: center"]1:30[/TD]
[TD="align: center"]1:00/2:15[/TD]
[TD="align: center"]0:45/3:00[/TD]
[TD="align: center"]0:36/3:45[/TD]
[/TR]
[TR]
[TD="align: center"]A20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2:00[/TD]
[TD="align: center"]2:40/1:30[/TD]
[TD="align: center"]2:00[/TD]
[TD="align: center"] 1:20/3:00[/TD]
[TD="align: center"]1:00/4:00[/TD]
[TD="align: center"]0:48/5:00[/TD]
[/TR]
[TR]
[TD="align: center"]A21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2:30[/TD]
[TD="align: center"]3:20/1:52[/TD]
[TD="align: center"]2:30[/TD]
[TD="align: center"]1:40/3:45[/TD]
[TD="align: center"]1:15/5:00[/TD]
[TD="align: center"]1:00/6:15[/TD]
[/TR]
</tbody>[/TABLE]

When the toggle switch is selected to "Ratio 0.75:1" at E16, I want E17:E21 to calculate values at D17:D21 and convert it to 0.75:1 ratio and display only the black colored value.

And when the toggle switch is selected to "Ratio 1:0.75" at E16, I want E17:E21 to calculate values at D17:D21 and convert it to 1:0.75 ratio andto display only the green colored value.

Similar is the case for G16, H16 and I16 for toggle switches and their respective ranges to display converted values accordingly. The source is always D17:D21 for all the columns.

I have manually populated E17:I21 with the converted values for clarity.

Can anyone please help me with suitable formula so I can populate E17:I21 automatically?

Thank in advance!

 

[RasGhul]

Hi masud,

You can place the DV table anywhere but can be used for your toggles, paste this formula into E17 copy across and down;

Book1
	A	B	C	D	E	F	G	H	I
10					Data Validation for E16:I16
11					0.75:1	1:1	1.5:1	2:1	2.5:1
12					1:0.75	1:1	1:1.5	1:2	1:2.5
13
14				DURATION	Ratio	Ratio	Ratio	Ratio	Ratio
15	DURATION	DURATION	DURATION		0.75	1	1.5	2	2.5
16					1:0.75	1:1	1.5:1	1:02	1:2.5
17				0:45	0:33	0:45	0:30	0:22	1:52
18				1:00	0:45	1:00	0:40	0:30	2:30
19				1:30	1:07	1:30	1:00	0:45	3:45
20				2:00	1:30	2:00	1:20	1:00	5:00
21				2:30	1:52	2:30	1:40	1:15	6:15
Sheet1

Cell Formulas
Range	Formula
E17	=IFERROR(IF(E$16="1:1",$D17*1,IF(SEARCH("1:",E$16)=1,$D17*E$15,"")),$D17/E$15)

 

[masud8956]

@RasGhul,

Thank you for the reply!

The excel sheet works nicely with your formula. I am sure this is the ultimate solution as it returns correct values.

But I still have not figured how I am going to arrange my cells and data for toggle switch. Actually never used toggle before. If you kindly guide me a little more elaborately.

Thanks!!

 

[masud8956]

Just to make it simpler,

Now I am thinking instead of toggle may be drop down list will be better for me. That way with a slight modification may be I can work it out.

If I want to select the conversion ratio at C17 of the example #2 , and I want the output at F17, then what modification do I have to make? In C17 the drop down list will contain two options: 0.75:1 and 1:1.

That way I will be able to choose different ratios of my choice in different rows if I need. I will also have the option to insert more type of ratios if I need.

Thanks!

 

[RasGhul]

We could make a ratio table and then you can choose or add ratios to the table which will update your drop down lists (E16:I16), then you can make as many columns as needed. Keep the same bracket format for new ratios or they won't work in the main table.

When setting the Drop list range I selected from $K$2:$K$30, this allows extra ratios to be added to the Drop lists automatically. Note that the VLOOKUP range VLOOKUP(E$16,$K$8:$L$30,2,0)) in the formula needs to be the same size as your ratio table if you add to it.

Option 2 – Named range

If you're comfortable with named ranges we can change the VLOOKUP part to be similar to VLOOKUP(E$16,ratios,2,0)). Then you can expand the named range which saves you trying to update all your table formulas.

Book1
	D	E	F	G	H	I	J	K	L
7	OPTION 1							Ratio	Factor
8								(1:0.75)	0.75
9								(0.75:1)	0.75
10								(1:1)	1
11								(1.5:1)	1.5
12								(1:1.5)	1.5
13								(1:2)	2
14								(2:1)	2
15	DURATION	Ratio	Ratio	Ratio	Ratio	Ratio	DURATION	(1:2.5)	2.5
16		(0.75:1)	(1:1.5)	(1:1.5)	(1:2.5)	(2.5:1)		(2.5:1)	2.5
17	0:45	1:00	1:07	1:07	1:52	0:18
18	1:00	1:20	1:30	1:30	2:30	0:24
19	1:30	2:00	2:15	2:15	3:45	0:36
20	2:00	2:40	3:00	3:00	5:00	0:48
21	2:30	3:20	3:45	3:45	6:15	1:00
Sheet1

Cell Formulas
Range	Formula
E17	=IFERROR(IF(E$16="(1:1)",$D17*1,IF(SEARCH("(1:",E$16)=1,$D17*VLOOKUP(E$16,$K$8:$L$30,2,0),"")),$D17/VLOOKUP(E$16,$K$8:$L$30,2,0))

Book1
	D	E	F	G	H	I	J	K	L
7	OPTION 2							Ratio	Factor
8								(1:0.75)	0.75
9								(0.75:1)	0.75
10								(1:1)	1
11								(1.5:1)	1.5
12								(1:1.5)	1.5
13								(1:2)	2
14								(2:1)	2
15	DURATION	Ratio	Ratio	Ratio	Ratio	Ratio	DURATION	(1:2.5)	2.5
16		(0.75:1)	(1:1.5)	(1:1.5)	(1:2.5)	(2.5:1)		(2.5:1)	2.5
17	0:45	1:00	1:07	1:07	1:52	0:18
18	1:00	1:20	1:30	1:30	2:30	0:24
19	1:30	2:00	2:15	2:15	3:45	0:36
20	2:00	2:40	3:00	3:00	5:00	0:48
21	2:30	3:20	3:45	3:45	6:15	1:00
Sheet1

Cell Formulas
Range	Formula
E17	=IFERROR(IF(E$16="(1:1)",$D17*1,IF(SEARCH("(1:",E$16)=1,$D17*VLOOKUP(E$16,ratios,2,0),"")),$D17/VLOOKUP(E$16,ratios,2,0))

Named Ranges
Name	Refers To	Cells
ratios	=Sheet1!$K$8:$L$30

 

[masud8956]

Thank you very very much for taking the time and giving me two wonderful options!!

I think I am going to use option 1. That was just a simple sample that I have uploaded at #1 . I am going to use the formula in my actual worksheet now which will take about 1-2 days. I am sure it will work nicely. I will definitely post a feedback.

Thanks again. Learned a lot from you!

 

[RasGhul]

You're welcome
Let me know if you need any further help

 

[masud8956]

You're welcome
Let me know if you need any further help

Hi @RasGhul,

No problem with your code in my project. Thanks a lot again!

I was wondering if you could help me with one more small issue which I am adding to my project now. I fiddled around with your code:

=IFERROR(IF(E$16="1:1",$D17*1,IF(SEARCH("1:",E$16)=1,$D17*E$15,"")),$D17/E$15)

.........but could not make it work.

I simply need to multiply a cell value (in [h]:mm format) at A1, with a ratio (in text format) at B1. I want the result in C1 (in [h]:mm format). I prefer to avoid named ranges or lists because; I think it is not required in this case as the value in B1 is not variable and simple multiplication should work. Similar is the case for A2, B2 with result in C2 and so on.

Sample below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1:30[/TD]
[TD](1.5:1)[/TD]
[TD]1:00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3:00[/TD]
[TD](2:1)[/TD]
[TD]1:30[/TD]
[/TR]
</tbody>[/TABLE]

​

Thanks!

 

[RasGhul]

Hi Masud,

I would leave the original formula for the main ratio area, I've done 2 formulas for this new part as the output in your last post is from divide rather than multiply but you can test both and see what suits

Book1
	A	B	C	D	E	F	G	H	I
15				DURATION	Ratio	Ratio	Ratio	Ratio	Ratio
16	A	B	C		(0.75:1)	(1:1.5)	(1:1.5)	(1:2.5)	(2.5:1)
17	1:30	(1.5:1)	1:00	0:45	1:00	1:07	1:07	1:52	0:18
18	3:00	(2:1)	1:30	1:00	1:20	1:30	1:30	2:30	0:24
19	1:30	(1.5:1)	2:15	1:30	2:00	2:15	2:15	3:45	0:36
20	3:00	(2:1)	6:00	2:00	2:40	3:00	3:00	5:00	0:48
Sheet1

Cell Formulas
Range	Formula
C17	=A17/VLOOKUP(B17,$K$8:$L$29,2,0)
C19	=A19*VLOOKUP(B19,$K$8:$L$29,2,0)

 

[RasGhul]

*Note that the formula is getting the factor value from the ratio table from post #5 , if you want the formula to work without using the table we will need a different approach.