Math help
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.
|