How to have excel add/subtract fractions to satisfy given relationships

[devpatel85]

I have a table (A14:I21) with data given in measurements of feet (A1=88.5542'). The values in that table are based on simple math relationships that I set. I then converted these values to feet-inches (see table K14:AA21) by separating the whole number and decimal portions (0.5542'*12 = 6.6504"). The decimal portion (6.6504") is formatted to show as a fraction to 1/16" accuracy (except for values in the Q column).

I want excel to instead provide the fractions so that they STILL satisfy the math relationships I set. Since the fractions are rounded, it's not always adding up as I would like. In other words, I want excel to now treat the fractions as they are formatted, not their decimal values. For instance, C+F+G should equal A. If you look at Beam 6, however, this does not hold true.

EDIT: I've attached the file.

Any suggestions? Thanks in advance!

[ChemistB]

To attach a spreadsheet, "Go Advanced" > manaage Attachments

[devpatel85]

I attached the file. Can someone please help?

[XOR LX]

"In other words, I want excel to now treat the fractions as they are formatted, not their decimal values. For instance, C+F+G should equal A."

Not sure I understand. The mathematics with the fractions as formatted is no less inaccurate, e.g. summing cells P20, Q20 ("C"), V20, W20 ("F"), X20, Y20 ("G") gives:

(86+2+1)' + (9+0+8)" + ((7/8)+(8/16)+(6/16))"

= 89'+17"+(28/16)" = 89'+1'5"+1 12/16" = 90' 6 3/4"

which is not the same as the value in L20 and M20: 90' 6 13/16"

Regards

[devpatel85]

That's what I mean, it's not the same value. I would like to have excel adjust the fractions as needed to satisfy the requirements. There are a few cases (Beam 6, C+F+G=/A) where excel has rounded a number up/down in the wrong direction, throwing off the result. The fractions have to work out exactly. As you can see, that second table is just converting the decimal feet values to feet-inch fractions. The formulas are not being applied to those values however. There has to be a way to make excel decide which fractions to adjust so that the equalities are met.

Thanks

[XOR LX]

Yes, but each decimal must be rounded (to the nearest 1/16 in you case) on its own individual basis.

How you would inform Excel that this degree of rounding must be amended subject to further (future) calculations with that value is not something I envisage being easy to do.

Rather than keeping the actual decimal values and displaying them as fractions, in order to achieve what you wish you may have to devise a custom text format, though mathematical operations on those text values would have to be defined through formulae in each case.

Is this something you would be willing to work with?

Regards

[ChemistB]

I suggest that when you are doing your math, use MROUND and (0.0625 assuming 1/16 is your smallest value not 1/32). For example in M15,

=MROUND(12*(B15-INT(B15)),0.0625)
then when you add cells, they will be exact multiples of 1/16. Does that help?

[devpatel85]

That does help ChemistB, and I think we're on the right track, but what now? I imagine I'll have to create another table that will handle the fractions and apply the operations. It's a bit tricky though. That second table is set up so that feet and inches are given in two separate columns. What function(s) can I use to combine the two while still being able to use the operations I give in table 3.

I updated the worksheet (uses MROUND) and cleaned it up. Feel free to mess around with it and re-upload the file. Take a look at the functions

I really appreciate the help guys.

[devpatel85]

I'm still looking for a function that can add/subtract feet-inches from two separate columns.

[ChemistB]

In N18 for example

=R18+X18+Z18+INT((S18+Y18+AA18)/12)

In O18

=MOD(S18+Y18+AA18,12)
Does that work for you?

[devpatel85]

Thank you very much ChemistB! Really appreciate your help.