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!

 

[masud8956]

*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.

Thanks!

Actually, that's exactly what I need. I need to do this multiplication straightforward and without reference to any table.

Regards.

 

[masud8956]

And...

Meanwhile, I tried the following formula. I do not know why it did not work.

In C1,
=IF(SEARCH(":1",$B1)=1,$A1,$A1*SEARCH(":1",$B1))
returning a value 7:30 whereas it should had been 1:00.

Similarly in C2 I tried:
=IF(SEARCH(":1",$B2)=1,$A2,$A2*SEARCH(":1",$B2))
returning a value 9:00 instead of 1:30.

Did I mess up the formula or this formula is no good for this case?

 

[RasGhul]

This works for me in C1;

Book1
	A	B	C
1	1:30	(1.5:1)	1:00
2	3:00	(2:1)	1:30
3	1:30	(1:1.5)	1:00
4	3:00	(1:2)	1:30
Sheet1

Cell Formulas
Range	Formula
C1	=A1/IF(ISNUMBER(SEARCH("(1:",B1)),VALUE(SUBSTITUTE(RIGHT(B1,LEN(B1)-SEARCH("(1:",B1)-2),")","")),VALUE(SUBSTITUTE(LEFT(B1,SEARCH(":1)",B1)-1),"(","")))

 

[masud8956]

Perfect solution for me!

Worked beautifully! Can't thank you enough!

 

[RasGhul]

You're Welcome