Need Formula Help Pls

[Don]

I have the following list of $$ amounts in column A with corresponding
calculation in column B (calculating markup).....

In column A In Column B (cell values are set to currency with 2
decimal places - rounding it)

$3.30 =((3.30*2)+(3.30*2*0.03)+0.6) = 7.40
$8.30 =((8.30*2)+(8.30*2*0.03)+0.6) = 17.70
$2.95 =((2.95*2)+(2.95*2*0.03)+0.6) = 6.68

What I need, is to fix the column B function so that the result is if the
"cents" are < 50 then add a calculation so that it the dollar amount remains
the same but the cents display .95 (i.e., instead of 7.40, it would be
7.95) and if the cents are 50 and above, then add the calculation so the
cents displayed are .99 (i.e., instead of 17.70, it would be 17.99 or 6.68
would display as 6.99).

Hope I've explained this well enough.

Thanks,
Don

 

[Roger Govier]

Hi Don

Try entering in B1
=INT(A1*2*(1+0.03)+0.6)+IF(MOD((A1*2*(1+0.03)+0.6),1)<0.5,0.95,0.99)
and copy down

 

[Bob Phillips]

=INT(A1)+0.95+(MOD(A1,1)>=0.5)*0.04

17.00 shows as 17.95, is that correct?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

BTW, my formula would work on the resultant cell, not the original.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Tezza]

Don

This formula (in B1) will do it for you:

=IF((((A1*2)+(A1*2*0.03)+0.6)-ROUNDDOWN(((A1*2)+(A1*2*0.03)+0.6), 0))>=0.5,
ROUNDDOWN(((A1*2)+(A1*2*0.03)+0.6), 0)+0.99,
ROUNDDOWN(((A1*2)+(A1*2*0.03)+0.6), 0)+0.95)

Hope this helps

Terry Rees

 

[Guest]

=INT( 2.06 * A1 + 0.6 ) + 0.95 + ( MOD( 2.06 * A1 + 0.6, 1 ) >= 0.5 ) * 0.04

 

[Don]

Terry,

Thanks.....yours is the best one I see here.....I've implemented it......had
to correct 1 error in the if statement......found that if I take the initial
extra para ( ) set off from the start of the statement, it works fine.....

=IF(((D3*2)+(D3*2*0.03)+0.6)-ROUNDDOWN(((D3*2)+(D3*2*0.03)+0.6),
0)>=0.5,ROUNDDOWN(((D3*2)+(D3*2*0.03)+0.6),
0)+0.99,ROUNDDOWN(((D3*2)+(D3*2*0.03)+0.6), 0)+0.95)

Again, thank you very much

Don