Need Formula Help Pls
 

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

Need Formula Help Pls
 

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

--
Regards

Roger Govier


"Don" wrote in message
...
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

Need Formula Help Pls
 

=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)

"Don" wrote in message
...
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

Need Formula Help Pls
 

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)

"Bob Phillips" wrote in message
...
=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)

"Don" wrote in message
...
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

Need Formula Help Pls
 

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
"Don" wrote in message
...
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

Need Formula Help Pls
 

=INT( 2.06 * A1 + 0.6 ) + 0.95 + ( MOD( 2.06 * A1 + 0.6, 1 ) = 0.5 ) * 0.04
--
Festina Lente


"Don" wrote:

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

Need Formula Help Pls
 

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

"Tezza" wrote in message
k...
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
"Don" wrote in message
...
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