Math help

[Nadine]

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.

 

[Bill Kuunders]

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

 

[Roger Govier]

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(M22)

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)

 

[Nadine]

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.