Using Fractions as 5ths?

larry12445

New Member
Joined
Jan 17, 2005
Messages
16
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Welcome to the board!

Try:

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

Analysis toolpack must be enabled.
 

larry12445

New Member
Joined
Jan 17, 2005
Messages
16
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Maybe this is not the best way but....

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

larry12445

New Member
Joined
Jan 17, 2005
Messages
16

ADVERTISEMENT

Thank you again - fairwinds!
 

larry12445

New Member
Joined
Jan 17, 2005
Messages
16
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

New Member
Joined
Jan 17, 2005
Messages
16
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?
 

Forum statistics

Threads
1,148,397
Messages
5,746,457
Members
424,020
Latest member
LongDoo

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
Top