Converting [h]:mm value with toggle switch

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
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.

A15B15C15D15E15F15G15H15I15
A16DURATIONRatio
(0.75:1)/
(1:0.75)
Ratio
1:1
Ratio
(1.5:1)/(1:1.5)
Ratio
(2:1)/(1:2)
Ratio
(2.5:1)/(1:2.5)
A170:451:00/0:330:450:30/1:070:22/1:300:18/1:52
A181:001:20/0:451:000:40/1:300:30/2:000:24/2:30
A191:302:00/1:071:301:00/2:150:45/3:000:36/3:45
A202:002:40/1:302:00 1:20/3:001:00/4:000:48/5:00
A212:303:20/1:522:301:40/3:451:15/5:001:00/6:15

<tbody>
</tbody>

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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
ABCDEFGHI
10Data Validation for E16:I16
110.75:11:11.5:12:12.5:1
121:0.751:11:1.51:21:2.5
13
14DURATIONRatioRatioRatioRatioRatio
15DURATIONDURATIONDURATION0.7511.522.5
161:0.751:11.5:11:021:2.5
170:450:330:450:300:221:52
181:000:451:000:400:302:30
191:301:071:301:000:453:45
202:001:302:001:201:005:00
212:301:522:301:401:156:15
Sheet1
Cell Formulas
RangeFormula
E17=IFERROR(IF(E$16="1:1",$D17*1,IF(SEARCH("1:",E$16)=1,$D17*E$15,"")),$D17/E$15)
 
Upvote 0
@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!!
 
Upvote 0
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!
 
Upvote 0
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
DEFGHIJKL
7OPTION 1RatioFactor
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
15DURATIONRatioRatioRatioRatioRatioDURATION(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
170:451:001:071:071:520:18
181:001:201:301:302:300:24
191:302:002:152:153:450:36
202:002:403:003:005:000:48
212:303:203:453:456:151:00
Sheet1
Cell Formulas
RangeFormula
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
DEFGHIJKL
7OPTION 2RatioFactor
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
15DURATIONRatioRatioRatioRatioRatioDURATION(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
170:451:001:071:071:520:18
181:001:201:301:302:300:24
191:302:002:152:153:450:36
202:002:403:003:005:000:48
212:303:203:453:456:151:00
Sheet1
Cell Formulas
RangeFormula
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
NameRefers ToCells
ratios=Sheet1!$K$8:$L$30
 
Upvote 0
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! (y)
 
Upvote 0
You're welcome
Let me know if you need any further help
 
Upvote 0
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:

ABC
11:30(1.5:1)1:00
23:00(2:1)1:30

<tbody>
</tbody>

Thanks!
 
Upvote 0
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
ABCDEFGHI
15DURATIONRatioRatioRatioRatioRatio
16ABC(0.75:1)(1:1.5)(1:1.5)(1:2.5)(2.5:1)
171:30(1.5:1)1:000:451:001:071:071:520:18
183:00(2:1)1:301:001:201:301:302:300:24
191:30(1.5:1)2:151:302:002:152:153:450:36
203:00(2:1)6:002:002:403:003:005:000:48
Sheet1
Cell Formulas
RangeFormula
C17=A17/VLOOKUP(B17,$K$8:$L$29,2,0)
C19=A19*VLOOKUP(B19,$K$8:$L$29,2,0)
 
Upvote 0
*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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top