Using Fractions as 5ths?

[larry12445]

Hello Everyone,

I've got a question. I'm trying to add and subtract values in Excel in 5ths.

For example:
I want to add 45.3 to 0.4 and have it equal 46.2
Likewise if I subtract 0.4 from 45.3 I want it to equal 44.4

I've looked at setting up the format for cells yet I do not see for fractions in 5ths.

Any help, assistance or ideas are much appreciated.

Thanks,

Larry

 

[fairwinds]

Welcome to the board!

Try:

=DOLLARFR(DOLLARDE(A1,5)+DOLLARDE(A2,5),5)

Analysis toolpack must be enabled.

 

[larry12445]

Thank you fairwinds!

That worked.

However, if I do the following;

45.4 - 48.1 = -2.2

With this -2.2 (which is the correct cal since I'm using 5ths) how do I get that to convert into -12 (which is the number of units in difference between the two in 5ths)?

Another example would be if I have 1.3 How would I get that to display "8" which is the number of units that represents in 5th?

Thanks,

Larry

 

[fairwinds]

Maybe this is not the best way but....

=(INT(ABS(A1))*5+MOD(ABS(A1),1)*10)*SIGN(A1)

 

[larry12445]

Thank you again - fairwinds!

 

[larry12445]

The following formula is working for me.

=DOLLARFR(DOLLARDE(A1,5)+DOLLARDE(A2,5),5)


However, it only appears to work fully when adding to values that haven't been previously calculated or manipulated.

When I use this formula and A1 or A2 are calculated values using the same format I'm gettting instances of such numbers as 45.5 as opposed to 46.0

I.e., I have values 15.4 + 17.1 I'm getting 32.5 instead of 33.0

However, if I manually enter the same values in the appropriate cells the formula is calculating correctly.

Any ideas on how I'm screwing this up?

Thanks,

Larry

 

[fairwinds]

I guess your previous calculations return some more decimals, but you do not see them becaus of your formatting.

Try:

=DOLLARFR(DOLLARDE(ROUND(A1,1),5)+DOLLARDE(ROUND(A2,1),5),5)

 

[larry12445]

Thanks once again fairwinds.

I figured it had to be something regarding the rounding becuase it was only occuring with the .5 everything else was working?