Custom addition formula

Using Office2000 Sp3

I use Excel a lot for historical things including eg; areas of land
expressed in ACRES ROODS PERCHES which is not added in tens (similar to
�/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a
formula which when applied to entries across 3 columns gives a decimal
answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in
D1, Roods E1, perches F1. Once this is decimalised I can work out ratios
etc.

However, I occasionally get tabbed text files or excel files from people who
have entered lots of raw data as eg ARP where it would be useful to just add
the Acres, Roods Perches as they are, similar to autosum except across 3
columns AND get it to add in something other than base 10. How can i make a
formula or autosum add numbers in a column in base 40 ie 0-39, if 39 is
exceeded 1 is added to the column of the left and the original reset to zero
and when 3 is exceed in that column the same happens to the column on its
left.

A    R    P
2    1    13
3    2    34
6    3    10

Formula addition =12.79375 acres


But as an addition it would normally add as 11 6 57 when the correct answer
is really 12 3 17.

TIA

Tim Whiteaway


0
ga98tew (3)
11/25/2003 8:44:11 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
371 Views

Similar Articles

[PageSpeed] 35

Tim,

Hopefully this works for you.  I used your Raw data 
beginning in cell A1 as follows:

   A    B     C
1  2    1    13
2  3    2    34
3  6    3    10

Then I entered the following in Row4:

A4:  =SUM(A1:A3)+ROUNDDOWN(SUM(B1:B3)/4,0)
B4:  =MOD(SUM(B1:B3)+ROUNDDOWN(SUM(C1:C3)/40,0),4)
C4:  =MOD(SUM(C1:C3),40)

This returned 12  3  17 in those respective cells.

Hope that helps!

Eric
0
11/25/2003 9:26:33 PM
Many thanks Eric, exactly what I was looking to achieve!

Tim


0
ga98tew (3)
11/27/2003 6:00:31 PM
Reply:

Similar Artilces:

Click in cell w/ formula and get colors in referenced cells
Hi, When you click in a cell with a formula, Excel will then put color around the cells that are referenced. Somehow I turned that feature off. Now I do not get colors in th other cells. Where is that option to turn it back on???? I've looke and looked but I just can't find that option. Thanks for the help -- albea ----------------------------------------------------------------------- albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875 View this thread: http://www.excelforum.com/showthread.php?threadid=48436 ...

Hiding OLD customers in list
I have a customer list (tblCustomers) And an Orders table (tblOrders) I want to have an admin area functionality that prunes (hides from display in any list or report) Customers from the list that have not placed an order in X number of days (Admin can enter the number of days in a text box) Solution does not have to be in days or in a text box. Could be several check boxes (30 days, 10 weeks, 2 years...). I am just looking for the simplest solution for us to limit the Customer list by last order date. Any help here will be appreciated. Thanks in advance If it were me, I would have a ...

How can I customize the Filter Drop down on the formatting toolbar
I want to clear everything out of it, except the customized filters I have created. Can i do that? Project > Filter by > More Filters, then select each one, Edit, and remove the checkbox for Show in Menu. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I want to clear everything out of it, except the customized filters I > have created. Can i do that? > I've tried that - it doesn't affect that drop down filter list - whether they are checked or not. "Andrew Lavinsky" wrote: > Project > Filter by > Mo...

monthly budget/check registry spreadsheet formula
I'm working on a monthly budget/check registry spreadsheet. What I want to do is have the check register link to the allowed balances in the monthly budget. I have two fields in my monthly budget for each item. A projected cost and actual cost. I want to link entries in the check registry to the actual cost of the fields in the monthly budget. I plan to use account numbers to link these fields to each other. For example . . . Say I buy groceries for $50. When I enter this into the check registry I enter all the necessary information along with an account number that links this tra...

Help Creating A Formula #2
I need to make a formula for alook up. The first spreadsheet i inventory, the second one is pg 103 strapping. I want to enter the fee and inches for pg 103 on the inventory and have it look up the feet an inches on strapping chart and enter the appropriate gallons in column Attachment filename: p & g 101 strapping.zip Download attachment: http://www.excelforum.com/attachment.php?postid=50210 -- Message posted from http://www.ExcelForum.com ...

Time duration formula
I would like to know the formula to work out my employees employment duration, from a given start date to the current day. many thanks Take a look at DATEDIF. Explained at http://www.cpearson.com/excel/datedif.htm -- HTH RP (remove nothere from the email address if mailing direct) "Ska" <Ska@discussions.microsoft.com> wrote in message news:B10818AE-1CAD-4CE5-BDED-BC0DDC96C876@microsoft.com... > I would like to know the formula to work out my employees employment > duration, from a given start date to the current day. > > many thanks Thanks Bob, I have us...

MS CRM 3.0 Customer Case Problem
Dear CRM Experts, I have a problem with MS CRM 3.0: I want to create a web based form in our company website that customers create their Cases (tickets) by it and we (I and my Tech. staff) response to them (Ticketing system). It seems for security reasons we shouldn’t allow CRM browsing outside of our Internal Network (Intranet). Please help me how can present solution for this problem. Best Regards, Mohsen Ahmadi You have a couple of options. There are third-party apps that will let you integrate CRM with your website, but with 3.0 you would probably need to purchase the External C...

Excel formula help #4
Hello! Can somone help me with this formula 2004:21-0-1-1-11 in this cell i wan't to get out "21" 2005:2-0-0-0-1 in this cell i wan't to get out "2" My result from the formula is "-21" and ":2-" is there a formula that i can use on both and get the result "21" and "2"? I want to add the together at the end and get the result "23" but with : and - i only get error. -- johhny ------------------------------------------------------------------------ johhny's Profile: http://www.excelforum.com/member.php?act...

Custom Reports Using MS Access?
Ran across a thread where Glen Adams suggested using MS Access to create custom reports instead of Crystal Reports. But Glenn never said where to point Access to obtain the needed information to create the reports. Glenn, if you are watching can you give us some more info on how to use Access to create a report? I'm not Glen... I think you would use ODBC... to connect to the RMS database. Then use Access to connect via ODBC... Marc Wagner www.gmroii.net "Dan" <anonymous@discussions.microsoft.com> wrote in message news:0df801c46ea7$d79b00b0$a601280a@phx.gbl... &g...

CONCATENATE text to create a formula to be evaluated
Hi, I am wanting to concatenate a set of text to create a formula. I have done so below. =CONCATENATE("=MAX('",O1,"'!A3:A65536)") The result is: =MAX('2009'!A3:A65536) ....but it treats this as a text string when I try to use INDIRECT Cell O1 contains the year minus 1. In this case O1 = 2009. All of my worksheets are named as a year e.g. 2007....2008....2009...2010. I am entering this on sheet 2010. Now the real question: How do I make another cell evaluate this string as an actual formula and spit out the highest number for ...

text in formula
Help!!! Is there a way to have a formula where a cell with text is in it, but it is not included in the formula. Like I have a random cell that appears sometimes within the range but because it is text and I do not want to include it in the formula. Is there a certain "symbol" that could be used? HELP!! Hi maureen, It depends on what the actual formula is, but *some* functions ignore text entries. You could use the ISNUMBER function to include only those entries that are numbers. Post your formula for a more detailed reply. Biff >-----Original Message----- >Help...

Customer (Pole) Display
Hi, I'm having a problem where Pole Display's on one customer site are dying one by one. At first it starts flickering, then within a few days it dies completely. Has anyone else encountered a similar problem? Thanks. This may be an OPOS driver mismatch or wrong Baud Rate issue. Which PD model and OPOS driver are you using? Try to change the baud rate to others value first. "trcboy" <trcboy@discussions.microsoft.com> wrote in message news:A30ECE7B-0AF4-4497-873D-D9900D5F6B4F@microsoft.com... > Hi, > > I'm having a problem where Pole Display's ...

Slightly OT: Online print house for custom greeting/note cards
Howdy, I somehow got "volunteered" (again) to create a few different designs for notecard/greeting cards for a non-profit org. so they can sell them to raise money. I'm looking for a cost effective online source (print house) that can take uploads of my designs. 4 color face & folds etc. I've looked at a few, but was wondering if anyone has any input Tnx, Rob Can't help you - I use a local printer. Sorry. -- JoAnn Paules MVP Microsoft [Publisher] "Rob Giordano (Crash)" <webmaster@siriussystems.invalid> wrote in message news:%233wnw3P1...

need formula help
I'm trying to have a formula do 3 scenerios in one cell and have a partial formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is greater than 0 and less than or equal to 7 use 3000. I would like to continue the formula that if G39 is greater than 7 and less than 14 use 5000 and if G39 is greater than 14 use 7500. How can I do this? Hi, =IF(AND(G39>0, G39<=7),3000,IF(AND(G39>7, G39<=14),5000,if(G39>14,7500))) "tom" wrote: > I'm trying to have a formula do 3 scenerios in one cell and have a partial > form...

Formula Help #66
I need help with a formula. I can't figure out how to calculate for missing number. '05 we sold x units, I know we had a X% increase over '04 so what wa the '04 sales? Column A = '05 Columb B = '04 Column C = % increas -- bab ----------------------------------------------------------------------- babz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2781 View this thread: http://www.excelforum.com/showthread.php?threadid=49307 '04 sales = '05 sales / 1.X% increase Vaya con Dios, Chuck, CABGx3 "babz" wrote: >...

Custom Report 03-19-10
There is no way for me to run a report on Buydown Pricing, is there anyone who has a report for this option? If not, can someone write a custom report for it, and how much might something like this run me? I would like it to do something like buydown quantity greater/less/equal and same for buydown price... please contact me at nagarra @ yahoo . com or respond here with your contact info! -- Thank You Vince :) ...

Copying formulas to another worksheet
Can anyone please help me, I am trying to copy a formula =(sump1:p3) from one worksheet into a new worksheet. When I go to paste the formula the only thing that comes up is =SUM(#REF!). Can I even copy and paste a formula from one worksheet into a new worksheet? and if so how? Thanks When you paste a formula it relatively changes the cell references to suit where you have pasted it to. EG. If you pasted =sum(P1:P3) from cell P4 to cell B4 it would change to =sum(B1:B3). However, if you pasted it to cell B2 it could not reasonably change the references. If you are pasting because you...

Creating Formula with Embedded Variable ID
I need to create a single formula for my workbook that references cells from various tabs (over 20) in another workbook. I want to copy this formula into multiple tabs within my workbook. My workbook and the external workbook have the same number of and identically named tabs. I want to embed a variable into the formula that will take on the name of the tab that the formula is copied to. I imagine the formula would look something like this: ='K:\Sales Reports\[Ohio Team, Mar-21-2004.xls]variable_identifying_the_tab_goes_here'!D1 I know I can use the following formula to derive th...

Custom CommandBar gone
Using Access 2003 I created a custom commandbar and I was using it as the menu bar for my main form. But it's not showing up anymore. It's still a choice in the menu bar drop down field of the form's properties. It is not listed in the toolbar list when I right click on the default menu bar and choose to customize. But if I click on the Properties button on another toolbar, the next window has a drop down box for Selected Toolbar. It is still listed as a choice here. I've tried doing a Compact and Repair, but that didn't help anything. Where did it go and can I get it ...

customizations import from 3.0 to 4.0
Is is possible to import customizations into a 4.0 system that were exported from a 3.0 install? You will need to upgrade 3.0 implementation to 4.0 by running MS CRM 4.0 setup. Please go through Microsoft Dynamics CRM 4.0 Installing Guide. You can download this guide from folowing URL http://www.microsoft.com/downloads/details.aspx?FamilyId=1CEB5E01-DE9F-48C0-8CE2-51633EBF4714&displaylang=en I don't think that there is any supported way through which you can import 3.0 Customization file to 4.0 implementation. Cheers, Rohit "Leo_B" wrote: > Is is possible to im...

How to reference workbook name in hyperlink formula?
In order to simplify the maintenance of workbook formulas I need to reference workbook name in hyperlink formula =[myWorkbok.xls]mySheet!$E4 in this way =[A1]mySheet!$E4 given the file name stored in cell A1 Cell A1 : myWorkbook How do I write the formula to make it work? Will it work even if the referenced workbook is closed? Regards Frank Krogh The function you'd want to use is =indirect(). But =indirect() won't work if the sending file is closed. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm ...

Amount in Words Complete Right Formula
give me complete formula See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Naveed" <Naveed@discussions.microsoft.com> wrote in message news:29B7C0D6-1DED-4321-B1EB-CA6DDCE73852@microsoft.com... > give me complete formula > ...

custom errors in HRESULT
We have methods that all return HRESULT. We want to also report our custom error values in HRESULT. What's the right way to do this? "Sasha" <Sasha@discussions.microsoft.com> wrote in message news:87F67172-76EA-4B3D-9FEE-EBFC687F83D0@microsoft.com... > We have methods that all return HRESULT. We want to also report our custom > error values in HRESULT. What's the right way to do this? See "Error Handling (COM)" at http://msdn.microsoft.com/library/en-us/com/html/15f3ae3e-1794-4948-a7aa-6309a703364b.asp In particular, read the section on "Usi...

Customize global adress list column in a new mail
Hi! (I have Outlook 2007 and Exchange 2007). when i create a new mail message, hit the "To" button, my global adress list window appear when column by default: Name, Title, business Phone, Location, E-mail address, Company and Alias. Those column from Active directory of course. My question is: can I add/remove column in this window? For example adding "department" from Active directory? thx Since this is customized by your Exchange Admin, I would start by asking him/her if it is possible. This question can most likely be answered in an Exchange or W...

Formula to reference another worksheet, locate data, then record i
Hi All, It has been over 10 years since I did my Excel studies and I've unfortunately forgotten everything I haven't used regularly. My office has recently upgraded to Office 2007 (upgrade not being the descriptor I'd have chosen!) and I'm struggling with Excel. I've found my way around most issues, but I'm REALLY stuck now and suffering Friday-itis on top of it all! Essentially I have a multi-sheet workbook for my debtors ledger. Each page has separate columns for the customer numbers, names, total debts in each age range (7 days, 14 days etc), totals and lastl...