Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |