Math help

I have a worksheet with columns A-P.
A=Hrs
B=Rate
C=Amount Billed (A*B)
D=Hrs Approved
E=Amt Approved (D*B)
F=Hrs Approved
G=Amt Approved (F*B)
H=Sum Approved (E+G)
I=Not Approved (C-H)
J=Approved & Paid (M+N+O)
K=Approved Not Paid (H-J)
L=Variance (C-J)
M=Pmt 1
N=Pmt 2
O=Pmt 3
P=Adjustment

Below is the data for row 2:
166.67
 $68.50 
 $11,416.90 
166.67
 $11,416.90 


 $11,416.90 
 $-   
 $11,416.90 
 $(0.01)
 $(0.01)
 $11,416.90 


-0.005
Row 3:
160.00 
 $25.76 
 $4,121.60 
160.00 
 $4,121.60 


 $4,121.60 
 $-   
 $4,121.06 
 $0.54 
 $0.54 
 $2,190.78 
 $1,930.28 

-0.54
Row 4:
10.00 
 $27.80 
 $278.00 
10.00 
 $278.00 


 $278.00 
 $-   
 $278.54 
 $(0.54)
 $(0.54)


 $278.54 
0.54
****** END OF DATA******
I need a formula for cell J2 that will calculate cell the sum of M+N+O+P and 
will result in 0.  Thanks.
0
Utf
5/7/2010 11:02:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
778 Views

Similar Articles

[PageSpeed] 11

Nadine,
It looks like you have doubled up on a few calculations and are now looking 
for an answer that's already there.
For instance the K and L column are the same other than if you have a number 
in
column F then L and K will be different but I'm not sure that you do want 
that. So I would delete column L.
The adjustment you want to show in P column must be the same as the variance 
you already calculated in K column.
Or if you want a zero balance in P column then you need to calculate   H 
minus J minus K.

Bill K
Greetings from New Zealand



"Nadine" <Nadine@discussions.microsoft.com> wrote in message 
news:8B8C0483-5C1F-4D32-AD79-5527458299D3@microsoft.com...
>I have a worksheet with columns A-P.
> A=Hrs
> B=Rate
> C=Amount Billed (A*B)
> D=Hrs Approved
> E=Amt Approved (D*B)
> F=Hrs Approved
> G=Amt Approved (F*B)
> H=Sum Approved (E+G)
> I=Not Approved (C-H)
> J=Approved & Paid (M+N+O)
> K=Approved Not Paid (H-J)
> L=Variance (C-J)
> M=Pmt 1
> N=Pmt 2
> O=Pmt 3
> P=Adjustment
>
> Below is the data for row 2:
> 166.67
> $68.50
> $11,416.90
> 166.67
> $11,416.90
>
>
> $11,416.90
> $-
> $11,416.90
> $(0.01)
> $(0.01)
> $11,416.90
>
>
> -0.005
> Row 3:
> 160.00
> $25.76
> $4,121.60
> 160.00
> $4,121.60
>
>
> $4,121.60
> $-
> $4,121.06
> $0.54
> $0.54
> $2,190.78
> $1,930.28
>
> -0.54
> Row 4:
> 10.00
> $27.80
> $278.00
> 10.00
> $278.00
>
>
> $278.00
> $-
> $278.54
> $(0.54)
> $(0.54)
>
>
> $278.54
> 0.54
> ****** END OF DATA******
> I need a formula for cell J2 that will calculate cell the sum of M+N+O+P 
> and
> will result in 0.  Thanks. 


0
Bill
5/8/2010 1:41:35 AM
Hi Nadine

C2 =ROUND(A2*B2,2)
E2=ROUND(D2*B2,2)
G2=ROUND(F2*B2,2)
H2=E2+G2
I2=C2-H2
J2=SUM(M2:P2)

P2 should be 0 (No adjustment)
P3 should be 0.54
P4 should be -0.54

If this is related to your earlier query, then assuming you had a column 
with Invoice Number, lets say Q for the moment, and you say you may have 
up to 20 payments against each invoice, I would enter the payments on 
Sheet2 with Invoice No in A, Date in B and Amount in C.
Then on this sheet, just use column M for Payment with a formula of
=ROUND(SUMIF(Sheet2!A:A,Q2,Sheet2!C:C),2)
--
Regards
Roger Govier

Nadine wrote:
> I have a worksheet with columns A-P.
> A=Hrs
> B=Rate
> C=Amount Billed (A*B)
> D=Hrs Approved
> E=Amt Approved (D*B)
> F=Hrs Approved
> G=Amt Approved (F*B)
> H=Sum Approved (E+G)
> I=Not Approved (C-H)
> J=Approved & Paid (M+N+O)
> K=Approved Not Paid (H-J)
> L=Variance (C-J)
> M=Pmt 1
> N=Pmt 2
> O=Pmt 3
> P=Adjustment
> 
> Below is the data for row 2:
> 166.67
>  $68.50 
>  $11,416.90 
> 166.67
>  $11,416.90 
> 
> 
>  $11,416.90 
>  $-   
>  $11,416.90 
>  $(0.01)
>  $(0.01)
>  $11,416.90 
> 
> 
> -0.005
> Row 3:
> 160.00 
>  $25.76 
>  $4,121.60 
> 160.00 
>  $4,121.60 
> 
> 
>  $4,121.60 
>  $-   
>  $4,121.06 
>  $0.54 
>  $0.54 
>  $2,190.78 
>  $1,930.28 
> 
> -0.54
> Row 4:
> 10.00 
>  $27.80 
>  $278.00 
> 10.00 
>  $278.00 
> 
> 
>  $278.00 
>  $-   
>  $278.54 
>  $(0.54)
>  $(0.54)
> 
> 
>  $278.54 
> 0.54
> ****** END OF DATA******
> I need a formula for cell J2 that will calculate cell the sum of M+N+O+P and 
> will result in 0.  Thanks.
0
Roger
5/8/2010 4:10:06 PM
Hi Bill,

I should have clarified:  hours can be approved in part therefore the need 
for 2 groupings of approved hours, so col L needs to stay in.

The variance in Col K is the true variance of any unpaid costs or over paid 
costs.  This is not the same as the adjustment amount.  The adjustment amount 
is to handle any data entry erros not costs not yet approved.


"Bill Kuunders" wrote:

> Nadine,
> It looks like you have doubled up on a few calculations and are now looking 
> for an answer that's already there.
> For instance the K and L column are the same other than if you have a number 
> in
> column F then L and K will be different but I'm not sure that you do want 
> that. So I would delete column L.
> The adjustment you want to show in P column must be the same as the variance 
> you already calculated in K column.
> Or if you want a zero balance in P column then you need to calculate   H 
> minus J minus K.
> 
> Bill K
> Greetings from New Zealand
> 
> 
> 
> "Nadine" <Nadine@discussions.microsoft.com> wrote in message 
> news:8B8C0483-5C1F-4D32-AD79-5527458299D3@microsoft.com...
> >I have a worksheet with columns A-P.
> > A=Hrs
> > B=Rate
> > C=Amount Billed (A*B)
> > D=Hrs Approved
> > E=Amt Approved (D*B)
> > F=Hrs Approved
> > G=Amt Approved (F*B)
> > H=Sum Approved (E+G)
> > I=Not Approved (C-H)
> > J=Approved & Paid (M+N+O)
> > K=Approved Not Paid (H-J)
> > L=Variance (C-J)
> > M=Pmt 1
> > N=Pmt 2
> > O=Pmt 3
> > P=Adjustment
> >
> > Below is the data for row 2:
> > 166.67
> > $68.50
> > $11,416.90
> > 166.67
> > $11,416.90
> >
> >
> > $11,416.90
> > $-
> > $11,416.90
> > $(0.01)
> > $(0.01)
> > $11,416.90
> >
> >
> > -0.005
> > Row 3:
> > 160.00
> > $25.76
> > $4,121.60
> > 160.00
> > $4,121.60
> >
> >
> > $4,121.60
> > $-
> > $4,121.06
> > $0.54
> > $0.54
> > $2,190.78
> > $1,930.28
> >
> > -0.54
> > Row 4:
> > 10.00
> > $27.80
> > $278.00
> > 10.00
> > $278.00
> >
> >
> > $278.00
> > $-
> > $278.54
> > $(0.54)
> > $(0.54)
> >
> >
> > $278.54
> > 0.54
> > ****** END OF DATA******
> > I need a formula for cell J2 that will calculate cell the sum of M+N+O+P 
> > and
> > will result in 0.  Thanks. 
> 
> 
> .
> 
0
Utf
5/10/2010 6:45:01 PM
Reply:

Similar Artilces:

Missing Report Manager help file
Greetings: I run Windows XP home and Office XP Professional. Sometime last year I installed Report Manager add-in for Excel. In March 2005 I had to do a reinstall. Since then, when I click on View>Report>Manager>help I get a message stating that the XLMAIN8.HLP file cannot be found. The utility is checked in the Add-On dialog box. As I still have the download in the My Downloads folder I have even tried reinstalling the utility again but still the help file remains lost somewhere. Can someone offer some advice on what to do next? Where could the file have gone? How can I get it back...

help sos #2
error 421 smtp service is not available ...

Help!! think I messed this up!!
Hey need your help!! We have 2 domains, 1 2000 and 1 4.0 domain. We have 2 2000 exchange servers running in the 2000 domain and 1 exchange 5.5 box running in the 4.0 domain. We have ADC running. I moved the 5.5 user to the 2k exchange box like your supposed to. Management doesn't want to move the user into the 2000 domain. Nice huh? Well they could open their mailbox on the first 2000 exchange server. But it would act like it was trying to connect to the 2nd 2000 exhange box becuase it was coming up with a logon box with the 2nd exchange server name. So I disabled the ADC and mov...

screen is showing slides in backward order .. . help!
Hello I have a pptx presentation with six slides. For some reason, everything is defualting the the right (the bullets come out to the right of the words, the slides print as handouts in backwards order, the outline pane is on the right hand side of the slide pane). THis is only happening for this presentation. Others I create are the typical way PP usually works. PLEASE HELP! I wish I could upload it here but am unable to do so. See your other post -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_t...

Need help on this....thanks
I have an Excel worksheet which stored a survey outcome of over 1200 members. There are over 30 questions in the survey and the first column captured the member ID. The problem is 3 of the survey questions that allow multiple answers, instead the answer for those three questions are captured in seperate column, the answers are stored in a single column and it makes it very difficult to analysis those result. As a result I created addittional columns depending on the total number of the answers available for that question and assign the result manually to that column. It is very time consuming ...

help with cell referencing in named ranges
I have a named range covering about 30 columns of data. I'd like to lookup within the named range based on values in the 1st column, and a specific date which is the label on top of each of the other 29 columns. I have typed in a new column number as necessary, but I'd like to automate the column number by looking at a separate cell which gives the date. Ex: Dates across the top of the named range are 1/1/90, 1/1/91, etc thru 1/1/2003(in sheet1). Lets say I have a cell in sheet2(d1)that has the date 1/1/2003. In that same sheet starting in cell a1 I have a value which ...

HELP need index of the actual selected point
Hi, I need help. I must get the index of a the currently selectd point in the chart. I can't use the Mouseup event. It will be better to determinate the x and y value from the actual selected point but the index will also be good. Please help! thx Jens You need to use an old-style XLM command for this, as Stephen Bullen pointed out in his post 14-Jan-2001: Sub WhichPoint() Dim sPoint As String Dim iSeries As Integer, iPoint As Integer sPoint = ExecuteExcel4Macro("SELECTION()") If sPoint Like "S*P*" Then iSeries = Val(Mid$(sPoint, 2...

inbox.dbx infected with netsky !!! HELP!!!
I have one windows xp pro laptop with outlook express the inbox.dbx file seems corrupted because months of email just suddenly went missing for no reason I suspected that it is corrupted I installed easyrecovery pro and ran the emailrepair function to recover the inbox.dbx file I successfully recovered most of the mails But when the easyrecovery emailrepair is just about to write the repaired inbox.dbx file into the hard drive, my symantec antivirus corporate edition complains that the repaired inbox.dbx file is infected with the NETSKY virus .... and deletes the inbox.dbx file because it...

Pie Chart Help
Can anyone help? I have a spreadsheet to which I have one column that has different letters in, I need to create a pie chart that shows the amount of the same letter (i.e. 'P's in blue, 'F's in red etc) just so I can show how many of one letter there is? Any ideas and please explain fully as I am quite a new user Add a heading to the column of letters then you could create a pivot table to summarize the data, and create a pie chart from the pivot table results. There are instructions for pivot tables in Excel's Help, and Jon Peltier has information and links: ht...

POP email multiple duplicate downloads HELP!
Hi, ISP is SBC Global. I just installed Outlook 2003 yesterday and it keeps downloading ALL the mail I have in the SBC web inbox, even though I"ve marked them as "read" online, multiple times. Duplicate finder [bought and installed] doesn't recognize most of them as dupes. I've done "detect & repair," [got error 1311 source file not found; C:\msocache\allusers\90000409-60000-11D3-8CFE-0150048383C9\ZS561.CAB. Couldn't find that in my installation disk, either.] SBC says they do not support Outlook 2003 -- "it's too new." Superviso...

Help with System Attendant
Hi all: My situation is this. We had a single system running AD/exchange (windows and exchange 2000). Not ideal, I know :) The systems' mft became corrupted, and I spent a long night bringing up a 2nd exchange server. I was able to bring up the old server, move mailboxes, and have everyone up and running before the next biz day. I've since installed an AD controller on another machine, as the original (problem) server could die any day. I found where to set the 2nd server as a global catalog server, and thought I might have been done. That is, until I tried adding new use...

find&select button,help please?
Hi everybody, i'm using excel 2007,when i press the find&select button it dosen't sow me the cell i'm looking for,it has been disabled,although when i look the rows one by one i can find it,how can i able the find&select. i'd appreciate if u'd let me know,cuz i'm in trouble here ...

Help with rules not working #2
Hi: I'm continuing to experience problems with Rules working. This time, I have Outlook 2003 connected to Exchange 2000 Server, set up to run on a PC (Win XP pro). I'm connected to that PC with a remote desktop session that is always open. None of the rules work unless I run them manually (even rules that are server side). Does anyone know if a command line switch (or anything else) will work to ensure that rules actually run automatically? Thanks for any suggestions -- Dab Cut off: yourhead to respond ...

Help] How to merge two charts?
1. There are two charts M and N. Column A of chart M means part-number (numbers and letters), Column B of chart M means part-name (in Japanese); Column A of chart N means part-number (numbers and letters), Column B of chart N means part-name (in English); 2. Column M contains much more parts than Column N, which means N belongs to M; But M and N obey exactly the same part-number naming rule; 3. While most people can't Japanese but English, so it is planned to add the English part-names from Column N into Column M, just as remarks to corresponding Japanese part-names, meaning ...

please help OE & OL between 2 users
I am admin account and run Outlook. Girlfriend is limited account and runs OE. When she right clicks pics and the like to send to mail recepient, it opens an Outlook new message window. Now, these messages never get sent as she doesnt use Outlook as her email program. They simply accumulate in the Outlook outbox and never get sent. I cant set OE as her default email as it wont let me do that (I guess since Im admin and I use it). What should be done? Ive tried this all but the registry change http://support.microsoft.com/?kbid=306098 and it wont take. Whats the best way to do...

Oy, I need some help!!
Hi, Here is the problem: I use outlook express my wife uses outlook 2000 on a separate computer. I tried to combine the 2 computers into one. I copied my contacts from OE, went to the other computer and imported them in OE. Everything looked fine. When I went to Outlook, all the contacts were the ones in OE. I don't know what happened to her contacts. Is there any way to find and recover them? Do I need specific data recovery software? Is there any available on line? Please help... she is gonna kill me...... then divorce me!!!! Usually if Outlook and Outlook Express are set to s...

Help with memory leak (possibly in CArray)
Hi all, I've an App that takes about 4 hours to run before it throws the error I'm trying to fix, so I don't really fancy single-stepping it. It's throwing me a user break point in _heap_alloc_dbg at if (lRequest == _crtBreakAlloc) _CrtDbgBreak(); I guess this is an "out of heap memory" error. The App basically builds lots of models, tests them and then kicks the results out to a text file, so I'm creating and destroying a lot of objects, which is why I think I've a memory leak. The break actually comes during a call to CArray::SetSize, although I do apprec...

outlook express 5 help
I have a windows 2000 system that crashed. i backed up the outlook express folder and the applications data folder. I reinstalled Win2k. How do I import all the old Outlook express messages and address book on the new installation? Any help will be greatly appreciated. ...

HELP DBMS: 446, Great Plains: 0 What is it
Theres somebody how knows about the following ERROR MESSAGE DBMS: 446, Great Plains: 0 The window that showme the error message says: [Microsoft][ODBC SQL Server Driver][SQL Server] Cannot Resolve collation complete for equal to operation, then when i acept this message tellme that some sp called popSelectPODocInquiry return thje following results: DBMS: 446, Great Plains: 0 ... this happens when I enter to the window query in: query>purchase> purchase orders documents, then include open purchase orders, historic purchase orders, and receipt orders... tks, Camilo Villa It mea...

criteria HELP!!!!!!!!!!!!!!!!!!!!!
Hello, I've created three tables for the sales of a company and now i'm trying to run a query that only takes the sales after the month of january, the expression Builder was no help Please Advise Thanks ...

help with restricting paste in cells with data validation
I have a range (column infact) in which i would like users to only select options from the drop-down... i added the following code that prevent paste into cell with data validation from the same excel... Private Sub Worksheet_Change(ByVal Target As Range) 'Does the validation range still have validation? If HasValidation(Range("ValidationRange")) Then Exit Sub Else Application.Undo MsgBox "Your last operation was canceled." & _ "It would have deleted data validation rules.", vbCritical End If En...

SFO offline error! Please help!
Hi, I'm receiving an Exception from HRESULT: 0x80040E14 when i'm trying to access any pages offline. Online works fine, and it seems to go offline properly, syching works fine. It looks like some kind of authentication error. Does anyone know what the problem is? Thanks Linna Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [COMException (0x80040e14): Exception from HRESULT: 0x80040E14.] Mic...

HELP: weird high DPI problem??
A guy at work noticed that a custom control I wrote doesn't work correctly if you mess with the DPI settings. This control is the blue/rounded Explorer Bar from Windows XP. This control is built on top of a vertical rebar and I had to do a bunch of tricks to get it to work right. Anyways, in one of the routines, I calculate the exact height the control should be, if this calculation is to small, the rebar gets all garbled, if its too big, there are painting issues. I noticed in this calculation if I add in a fudge factor, it fixes the DPI related problems. The problem is that outside o...

badly need some help on CRM customization
Hi badly need some help on CRM customization. I need to add an additional account lookup field to the sold product. We sell software and we need to track end users (40% of buyers aren't the end users), so i want to be able to set an end user (to attach an account) to each product in the order. Yes I know that 1.2 doesn't support custom lookup fields but i need to transfer all of the data from our current customer database and i want it to be accessible from one interface. I wonder if i can create a field in the SalesOrderDetail table to store accountid (uniqueidentifier type), map i...

2nd cry for help-3 variable length lists of data that need to be concatenated in to one column
I have 3 variable length lists of data that need to be concatenated in to one column. Presently the following formula works (This is typed in to A1 and copied down the column using the drag handle so in cell A2 refs to C1,D1,E1 are automatically changed to C2,D2,E2 and so on): =IF(ROW()<=COUNTA(C:C),C1,IF(ROW()<=(COUNTA(C:C)+COUNTA(D:D)),OFFSET(D1,(COU NTA(C:C)),0,1,1),IF(ROW()<=(COUNTA(C:C)+COUNTA(D:D)+COUNTA(E:E)),OFFSET(E1,- (COUNTA(C:C)+COUNTA(D:D)),0,1,1),""))) Result - a list of data in column A which is the list of data in column C plus data in column D plus ...