need sum to always end $.90. First part ($00.) will change but last part must be $.90

[brettalan]

Working on a sales sheet where the dollar amount most always be $.90 (as in $45.90, $32.90, $67.90). Can not uses round up because sometimes you will round down. Yea I need some help.
=SUM(($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5
problem is in cell k9 and k10

Thanks

 

[NARAYANK991]

Hi Brett ,

If your amount is in A1 , the following formula should do ?

=IF(INT(A1)<>A1,INT(A1)+0.9,A1)

Instead of A1 being a simple value , if it is an expression , replace A1 in the above formula by the expression ; in K9 for example , you could have :

=IF(INT((($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5)<>((($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5),INT((($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5)+0.9,INT((($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5))

Narayan

 

[brettalan]

okay that is cool but is their a way to combine the fomula =IF(INT(A1)<>A1,INT(A1)+0.9,A1) with =SUM(($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5?

Thanks

 

[SirJB7]

Hi, brettalan!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your question, NARAYANK991's formula is written for cell A1, if your data is in K9:K10 and you want the 90 rounding to be calculated on the same cells you should use the formula posted in 2nd place, which is the same as the 1st one but replacing A1 by your formula. Now if you want to keep both the not rounded value and the rounded value, well, just select an empty cell, place there the 1st formula (shorter) and replace A1 by the related cell reference (K9, K10, etc.):
=IF(INT(K9)<>A1,INT(A1)+0.9,K9)

Regards!

 

[NARAYANK991]

Hi Brett ,

I think you might have resolved your problem by now ; I have posted the solution in the second part of my earlier post.

Your initial formula in K9 is :

=SUM(($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5

In this , the SUM function does nothing , and can be eliminated to get :

=(($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5

This is now your expression , which I have mentioned.

Thus in the formula =IF(INT(A1)<>A1,INT(A1)+0.9,A1) , if you replace A1 by the expression , you will get :

=IF(INT((($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5)<>((($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5),INT((($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5)+0.9,(($B$2/40)*26.5+(0.10189-0.045)*26.5)*2+5)

where the portions in BOLD are the direct replacements of A1 by the expression.

Narayan