Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up/down to .95
This is a Mircosoft Office 2007 Excel spread sheet.
I need to figure out a formula that can round up or down to $xx.95 using the following situation cost: 18.23 mark up: 1.8 Retail-based on this alone would be $32.81 A1= Cost A3= Retail A2= Mark up Formula used in A3 is- =A1*A2 My problem is that the owner wants all cents to be rounded up or down to .95 and costs are all different throught the cost columns. Is there a formula that can be entered to do such a funtion? Thanks for any assistance you can offer. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up/down to .95
hi, Marc !
try with something like: [A3] =int(a1*a2)+1-0.05 hth, hector. __ OP __ This is a Mircosoft Office 2007 Excel spread sheet. I need to figure out a formula that can round up or down to $xx.95 using the following situation cost: 18.23 mark up: 1.8 Retail-based on this alone would be $32.81 A1= Cost A3= Retail A2= Mark up Formula used in A3 is- =A1*A2 My problem is that the owner wants all cents to be rounded up or down to .95 and costs are all different throught the cost columns. Is there a formula that can be entered to do such a funtion? ... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up/down to .95
Hello,
I suggest to use =ROUND(A1*A2+0.05,0)-0.05 Please check against Hectors suggestions with values like 0.44 and 0,45 which version you really need... Regards, Bernd |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up/down to .95
=ROUNDUP(A1*A2,0)-0.05
-- Have a Great Day! HagridC "MarcV" wrote: This is a Mircosoft Office 2007 Excel spread sheet. I need to figure out a formula that can round up or down to $xx.95 using the following situation cost: 18.23 mark up: 1.8 Retail-based on this alone would be $32.81 A1= Cost A3= Retail A2= Mark up Formula used in A3 is- =A1*A2 My problem is that the owner wants all cents to be rounded up or down to .95 and costs are all different throught the cost columns. Is there a formula that can be entered to do such a funtion? Thanks for any assistance you can offer. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up/down to .95
Hi MarcV
The formulae to go in A3 is: = INT(A1*A2) + IF( ROUND( MOD(A1*A2 , 1) ,2) < 0.45 , -1 , 0) + 0.95 If instead you want to leave the formula you currently have in A3 and enter the above in A4, it would read: = INT(A3) + IF( ROUND( MOD(A3 , 1) ,2) < 0.45 , -1 , 0) + 0.95 This would give you the means to check each result, though I have tested the above and it seemed to work fine for me. Good luck. Hope this helps. David "MarcV" wrote: This is a Mircosoft Office 2007 Excel spread sheet. I need to figure out a formula that can round up or down to $xx.95 using the following situation cost: 18.23 mark up: 1.8 Retail-based on this alone would be $32.81 A1= Cost A3= Retail A2= Mark up Formula used in A3 is- =A1*A2 My problem is that the owner wants all cents to be rounded up or down to .95 and costs are all different throught the cost columns. Is there a formula that can be entered to do such a funtion? Thanks for any assistance you can offer. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up/down to .95
Hi again
Just noticed HagridC's response. Just to clarify: If you have $31.01 and you wanted it to be rounded up to $31.95, then HagridC's formula will do that. His formula basically works on even if there is just one cent, round up to the next 95 cents. I assumed you wanted rounding based on the 45c mark, so if is $31.44, it will round down to $30.95, but $31.45 will round up to $31.95. If this is the case, use my formula. Hope this clarifies. David "MarcV" wrote: This is a Mircosoft Office 2007 Excel spread sheet. I need to figure out a formula that can round up or down to $xx.95 using the following situation cost: 18.23 mark up: 1.8 Retail-based on this alone would be $32.81 A1= Cost A3= Retail A2= Mark up Formula used in A3 is- =A1*A2 My problem is that the owner wants all cents to be rounded up or down to .95 and costs are all different throught the cost columns. Is there a formula that can be entered to do such a funtion? Thanks for any assistance you can offer. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up/down to .95
My mistake. I neglected to consider the round down.
-- Have a Great Day! HagridC "David-Melbourne-Australia" wrote: Hi again Just noticed HagridC's response. Just to clarify: If you have $31.01 and you wanted it to be rounded up to $31.95, then HagridC's formula will do that. His formula basically works on even if there is just one cent, round up to the next 95 cents. I assumed you wanted rounding based on the 45c mark, so if is $31.44, it will round down to $30.95, but $31.45 will round up to $31.95. If this is the case, use my formula. Hope this clarifies. David "MarcV" wrote: This is a Mircosoft Office 2007 Excel spread sheet. I need to figure out a formula that can round up or down to $xx.95 using the following situation cost: 18.23 mark up: 1.8 Retail-based on this alone would be $32.81 A1= Cost A3= Retail A2= Mark up Formula used in A3 is- =A1*A2 My problem is that the owner wants all cents to be rounded up or down to .95 and costs are all different throught the cost columns. Is there a formula that can be entered to do such a funtion? Thanks for any assistance you can offer. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding up/down to .95
On Sat, 6 Feb 2010 13:00:03 -0800, MarcV
wrote: This is a Mircosoft Office 2007 Excel spread sheet. I need to figure out a formula that can round up or down to $xx.95 using the following situation cost: 18.23 mark up: 1.8 Retail-based on this alone would be $32.81 A1= Cost A3= Retail A2= Mark up Formula used in A3 is- =A1*A2 My problem is that the owner wants all cents to be rounded up or down to .95 and costs are all different throught the cost columns. Is there a formula that can be entered to do such a funtion? Thanks for any assistance you can offer. If you are rounding "up or down", where is the "dividing line". In other words, at what value to you change from rounding down to rounding up? --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding | Excel Discussion (Misc queries) | |||
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH | Excel Worksheet Functions | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Rounding | Excel Discussion (Misc queries) | |||
Rounding | Excel Worksheet Functions |