dividing currency as equally as possible

I'm trying to figure out a function/formula to calculate how to dispurse
say $10.00 between 3 cells as equally as possible.

IF A1 = $10.00, and B1, C1, and D1 each equal A1/3,
then each get $3.33 equalling $9.99, when in fact I need two of them to
get $3.33 and one of them to get $3.34 for the accurate total of
$10.00.

Financially, I MUST account for each penny!  Interestingly, it's not
important which cell gets which amount!

Any help would be appreciated!


---
Message posted from http://www.ExcelForum.com/

0
1/18/2004 10:42:52 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
284 Views

Similar Articles

[PageSpeed] 37

You could just set D1 to A1-B1-C1. For division of an amount into 3 parts
this should be accurate enough, but for the division of an amount into more
parts, to spread it as evenly as possible you need to use the 'reducing
balance' method. In each cell, calculate the amount remaining divided by the
number of cells remaining and round it.
So:
B1 will be =ROUND(A1/3,2)
C1 will be =ROUND((A1-B1)/2,2)
D1 will be =(A1-B1-C1)    (or ROUND((A1-B1-C1)/1,2), which will give the
same result).

"Healingbear >" <<Healingbear.1091he@excelforum-nospam.com> wrote in message
news:Healingbear.1091he@excelforum-nospam.com...
> I'm trying to figure out a function/formula to calculate how to dispurse
> say $10.00 between 3 cells as equally as possible.
>
> IF A1 = $10.00, and B1, C1, and D1 each equal A1/3,
> then each get $3.33 equalling $9.99, when in fact I need two of them to
> get $3.33 and one of them to get $3.34 for the accurate total of
> $10.00.
>
> Financially, I MUST account for each penny!  Interestingly, it's not
> important which cell gets which amount!
>
> Any help would be appreciated!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
stephenbye (32)
1/18/2004 11:22:58 PM
OK this should do it, A1 = $10

B1 = Rounddown(($A$1)/3,2)              = 3.33
C1 = Rounddown(($A$1)/3,2)              =3.33
D1 = Roundup(($A$1)/3,2)                  =3.34
                                                         =10

Hope that helps

Quarkey
0
anonymous (74721)
1/18/2004 11:36:14 PM
Reply:

Similar Artilces:

If I format a currency formula in excel ...
I have been attemtping to mailmerge in word, bringing through information from an Excel Spreadsheet. All the information is correct in the mailmerge - however a cell that appears correctly as currency (calculated by way of formula) in Excel does not come through into Word with currency/ decimal points... anything. I have formatted, reformatted and banged my head against a brick wall to try and work this out! I was wondering whether it may be something I have completely missed or a bug in the system? Any suggestions are greatly received! (ps - I am not using XP yet!) T...

Stock Currency
MS Money 11.0 International Version A stock in my portfolio trades in a different currency to the national curency of the stock exchange. Money defaults to the currency of the stock exchange. How can I change the currency of only that stock? ...

Decimals / Functional Currency
Has anyone used more than 2 places beyond the decimal marker and then decided they want to go back??? Our thoughts are to create a new functional currency. We're wondering if anyone's got experience changing Decimals or Functional Currency. Please contact me. Thanks! Craig@bturnkey.com Craig, I went through this once with a customer. Depending on how many transactions are in the system already, it's possible to fix this in the tables, but you have to be sure that you go through every table that might have values with the additional decimal places and change the numbers....

Unable to set a cell's format as currency
I have this code to set a cells format whenever data is entered from a user form. Private Sub TextBox5_Enter() Range("R$25").Value = Round((Range("$m$25").Value + (Range("$n$25").Value)) * (1 + Range("$q$25").Value)) TextBox5.Value = Round((Range("$m$25").Value + (Range("$n$25").Value)) * (1 + Range("$q$25").Value)) Range("R$25").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" End Sub It works the first time I use it. The second and each subsequent time I get a ...

Money 2005 SB: Currencies in Investment a/c
Hi all, I have an investment account set up in Euro. I bought shares in US$ but Money wants the amounts in Euro. How can I select to choose which currency is for each investment? Thanks, Ivan In microsoft.public.money, Ivan Debono wrote: > >I have an investment account set up in Euro. I bought shares in US$ but >Money wants the amounts in Euro. How can I select to choose which currency >is for each investment? Enter the an amount in US$ and hit function key F8. You can also search for "currency converter" in help. I suspect the commission will be in euro. Won...

Using Solver for dividing
hello ! I wonder how to use Solver for dividing something for example a rod on several part in such way to achieve as little as it is possible waste of that material. For example I have 25 rods , each is 12 meters long. I want to divide them on 3 meters, and 4 meters, and 5 meters. Question is , how to do that in order to achieve the littlest wast of the rods. Thank you in advance for your help Tomek You can download the CutStock sample workbook from the Solver site: http://www.solver.com/proframe.htm Tomek wrote: > hello ! > I wonder how to use Solver for dividing something ...

Dividing a chart with a horizontal line
I have a chart that has 15 vertical items down the side, and measurements across the bottom. I want to add a horizontal line to the chart that will separate the first 5 items down the side to group them together to the eye. The same thing with the second 5 items and third 5 items. Each of these is a "group" that falls into a category that I want to show on the chart. The first 5 items down the side fall under the group "professional", the second 5 items fall under a group called "Manager", etc. Is there any way to add a horizontal line that will make the...

please help, is it possible to find...
Dear Sir/Mam Is it possible to detect that "the text in a MS-Word 2003 document has not been typed manually" or copied from other sources like notepad or clipboard, etc., Could it be possible to find, actually what i mean to say is when someone copied some text in notepad it will be stored in clipboard temporarily right? and now if they pasted that text into a MS-Word 2003 document, actually they didn't typed it manually right. At this situation is there any process to find that the text they just pasted into the word doc has not been typed manually rather it is br...

Request for Change in currency when transferring contract
When you are transferring or renewing a contract it will only transfer in the originating currency. You will not be able to change the currency even after the contract has been transferred or renewed. The only option is to enter in a new contract with the new currency. We would like a functionality change wherein, we can change the currency id when transfering or renewing a contract. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pa...

euro currency format
recently someone posted asking how to get the ms download for euro formatting. you can download it here. go to office97. excel. add ins. http://office.microsoft.com/home/default.aspx ...

Currency Exchange Rates
Using M2000 (Canadian Edition). About one month ago I changed the base currency to EUR from USD. Everything worked fine. Quotes and currency exchange rates all downloaded without problem. Now, for the last week or so, I get the following message: Online Quotes and Exchange Rates: Money was unable to retrieve currency exchange rates for Euro. If the problem persists, click Options on the Tools menu, and then go to the Currencies tab to disable online update for this currency. Money was unable to retrieve currency exchange rates for Austrian Schilling. If the problem persists, click Opt...

Currency ID
I just created a profile for an Inventory user, i took off the system access as well as the other modules she would not need to have access to, when she goes to price lists and attempts to change a currency ID the systems says she does not have adequate privileges. What did I miss??? Try giving the user access to Financial >>Currencies. "rcr" wrote: > I just created a profile for an Inventory user, i took off the system access > as well as the other modules she would not need to have access to, when she > goes to price lists and attempts to change a curre...

Datevalue Equal to Today
I have a spreadsheet that calculates age I need the "Today" date to be converted to a new value automatically every day. The formula =TODAY() will update every time the worksheet is recalculated. This includes opening the workbook best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JennPVito" <JennPVito@discussions.microsoft.com> wrote in message news:2911789A-B375-4B0A-8A20-99F77A538D70@microsoft.com... >I have a spreadsheet that calculates age I need the "Today" date to be > converted to a new value automatically...

Printing in equal thirds to perfed paper
I have a 3 up form that I have designed and I want to have it print in equal 1/3'rds on a single 8.5x11.0 page. Is there a way to set this without playing with different row heights to make it fit? ...

xy scatter chart-How can you make the axis equal for a 1H:1V plot
I want to plot similar values in the x and y directions and I want the axis to be the same scale. If it were a map. I would want the map to be the same scale in both the x and y directions. I could then measure angles or distances on the graph correctly. The xy scatter chart seem to allow you to change the scale on the x and y axis independantly and then automatically stretches the axis (x and y) to fit the page. the result is that the graph is not true scale in both directions. Otherwise, I need to use another program or autocad to plot my values. mark wrote on Fri, 19 May 2006 12:...

Currency not working properly
Hello; I am living in Canada and when I create a invoice it changes the amount. For instance, I have created a product for $12.00. When I put it in the invoice it comes up as $15.28, the U.S. equivelent. I have told MS Money to use Canadian dollars as the base. I have also gone into my computer and made sure in the regional settings it was set to Canada, Canadian English, etc. Running Windows XP Pro Service Pack 2 and MS Money 2005. Any help would be greatly appreciated. Shann ...

Is this possible #2
In my last post I had talked about setting up a school shop, well I now need to do receipts and I have some small problems. Well I need to take the information that is required (e.g. what is being bought) to be taken to another sheet where it says the cost, price and quantity and the grand total. This is simple if I am using a Macro to copy and paste one line, but what if the child buys than more product? I have and an idea, I thought if the Quantity Section had a IF statement (e.g. IF there is more than 1 in this section than move it to the receipt.) But I do not know how to get round ...

Possible Bug in Sales Open Order Report
In Sales Open Order Report (SOP), an order (ORD) that was partially shipped and the remaining qty actually cancelled will still appear in the report with a « Qty Remaining » (to ship) equal to zero (0), until it is voided or deleted. If “Qty Remaining” (to ship) equal zero, then it should not appear in that report and should not require to be voided or deleted. ...

Possible to do a calc on certain rows?
Hi all, In 23 days time I am off around the world for 10 years. I have a pal TX and have found that I can use excel spread sheets. I already have budget set in MS ACCESS that is accessable online, but I would like budget in Excel so I can keep up to date as we travel around. In my budget I have the following columns (A1) Type (food, accommodation) (B1)Price (c1)Days (d1)Place then the following rows have the main information (A2)accommodation (B2)100.00 (c2)2 (d2)Auckland (A3) food (B3)60 (c3)2 (d3)Auckland (A4)Sightseeing (B4)40 (c4)1 (d4)North Shore Now is it possible to do somet...

Is Multi-Sequence Search Selection Macro Possible?
Hi: This is the first time I am posting to this newsgroup and if it is not the right place for my question, please point me to the correct newsgroup. If I have three columns with a multiple sequences of numbers, separated by empty cells, is it programmatically possible to have a macro to select a specific pattern in the three columns that meet the following conditions: For example using columns R, S and T. In column S, find a vertical sequence of 1, 2, 3, 4 and 5, that is followed by a vertical sequence of 1 through 13, directly to the left of column S, the next row down in column R, ...

OWA 5.5 to Exchange 2003 Server
I've customized OWA 5.5 to emulate the custom message form we use in OL. This doenst appear to be as doable in OWA 2003. Is there a way to run OWA 5.5 against the EX2003 server? On a clone of the old OWA 5.5 sever I've tried modifying the org, site, and server entries under the MSExchangeWEB\Parameters entries to point to the EX2003 server but I get "The Microsoft Exchange server is down or the HTTP service has been disabled...". Originally I tried it when I tried migrating and I thought it worked, but now I loaded EX2003 from scratch and subsequently it doesnt. If I ...

FRX Currency Converter #2
I have a situation with where I cannot identify which rate FRX is pulling on a consolidated report. I'm using a daily DAX table but I update the rates weekly. The column layout has date range filters and the row format is using CSPOT. I expected FRX to pull the rate based on the date filters in the column layout however this does not seem to be the case. It's not an average of the rates either. Has anyone else encountered this? How did you solve it? Thanks ...

Create a textbox that displays a list of possible items as we type
Hi All, Is it possible to display a list once user starts typing a word in vba? For eg, I have a userform connected to access database(I can already pull info from access, based on what user inputs in ID field), So Once a user starts typing in a text box, lets say User Name textbox, a list of users is displayed (something like google search, we start writing in the search bar and it suggests us what we might want based on what text we input) SO if I type in "A" in User Name text box, I get all the names starting with "A" such as: Name: [ A.... ] ...

Is it possible to re-link a cell back to a combo box?
Good moring all, I am using a combo box that shows data using *ListFillRange*. Thi combo box is also using the *LinkedCell*, in which what ever is picke in the data it shows on a certain cell. Is it possible to pick tha cell and re-link itself with the Combo Box? Thanks, Al -- Al ----------------------------------------------------------------------- AlP's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1839 View this thread: http://www.excelforum.com/showthread.php?threadid=34680 If that combobox has a *Style* of fmStyleDropDownCombo, then it seems to do t...

graph creation from table using cells with links not possible
Hello, I have problem with MS Excel 2003. It´s impossible to create a graph from the table which is automatically fullfilled by the links to another sheet. I have to fullfill the table manually (numbers not links to them are physically in the cells) and only then I´m able to create the graph correctly. Is there some possibillity, how to create a graph from the table wich has links instead real numbers in the cells? Thanks for Your ideas. Regards, Bobr. ...