How to calculate values with dashes

[missy22]

dear all,

I am writing to seek a little help on creating a macro, which calculates values with dashes as the following below:

example
input column F
71-22
66-17
85-05

conversion logic
input (71-22) --> (71+22.5/32) --> 17.70 (output)
input(66-17) --> (66+17.5/32) --> 66.55(output)
etc...

Output (column G)
17.70
66.55
85.17

Please note: only convert values where column K row equals "fraction"
The attach file shows sample data which is highlighted as green, to show values which needs to be converted and other rows are highlighted as red, to show they should NOT be converted.

testing_dashes.xls

Thank you for your time and support.

[MickG]

Try:-

Please Login or Register  to view this content.

Regards Mick

[AndyLitch]

How does .... (71+22.5/32) = 17.70 (output) ?

[shg]

How about just a formula?

In G2 and copy down,

=IF(TRIM(K2) <> "fractions", "", DOLLARDE(SUBSTITUTE(F2, "-", ".") & 5, 32))

Please Login or Register  to view this content.

[AndyLitch]

Still doesn't return 17.7 ..

[missy22]

How does .... (71+22.5/32) = 17.70 (output) ?

I am sorry that's my typo mistake, it suppose to say 71.70. I am extremely sorry for the confusion. Thank you for taking your time to read my query and responding to help.
Apology for the confusion.

Thank you.

[missy22]

Dear Mick,

Thank you so much for quick response and help. I really appreciate your time and solution.
The solution works great.

Thanks a million.

Have a great day.

[missy22]

Dear shg,

Thank you for responding to my query and providing a great working solution. I really appreciate your help and time.
Thank you so much.

[shg]

You're welcome.

[missy22]

Dear Mick/or anyone

I am really sorry to be a bother, but when i tested the input values out manually, the code was not adding the 0.5 to the second values after the dash.

I tried updating the code but I could not get it work. However, I would like to add in a new condition to the original code, if possible.

New condition --> if input value = "23-45" use this formula = 23 + 45/32, elseif input value = "98-12+" then use this formula = 98 + 12.5/32.

Please Login or Register  to view this content.

input column F
71-22
66-17+
85-05

conversion logic
input (71-22) --> (71+22.5/32) --> 71.68 (output)
input(66-17+) --> (66+17/32) --> 66.55(output)
etc...

Output (column G)
17.70
66.55
85.15

I have attached a sample file for further reference to the data:
testing_dashes 2.xls

Apology for the trouble. Thank you for your time and help.

[shg]

=IF(TRIM(K2) <> "fractions", "", DOLLARDE(SUBSTITUTE(SUBSTITUTE(F2, "-", "."), "+", 5), 32))

[missy22]

Dear Shg,

I really appreciate your time and help. I am really sorry to inform, that I can not use formulas because the worksheet i am working on has two macros and one of the macro overrides the any formulas.

I am sorry to sound ungrateful to your great working solution, but i require a little help coming up with a if statement for input values with (+) at the end, to be able to solve the above problem.

Thank you for your time and help.
kind regards

[shg]

Please Login or Register  to view this content.

[AndyLitch]

I'm sorry to inform you that you're solution doesn't work

Is it just me that finds that irritating ?

[missy22]

Please Login or Register  to view this content.

Apology to cause so much trouble over the new condition. Thank you so much for your help and converting the formula into a macro code. I am sorry, if i did not clarify from the beginning, that I required macro solution and I am also sorry, if I came across rude in my previous post.

I honestly appreciate your hardwork and solution. Thank you so much.
Have a great day

[missy22]

Dear all,

Sorry to cause nuisance again, I have come across an confusing issue with the code below:

(input) --> (ouput)
100-5+ 101.72
100-8+ 102.66

the macro outputs an correct answer for the first input data but incorrect for the second data input.

desired output for input (100-8+)
100.27

Please Login or Register  to view this content.

Any guidance would very much appreciated.

Thank you

[cytop]

Also on OzGrid: www.ozgrid.com/forum/showthread.php?t=178837. Probably solved.