Formula Help (Assumes Cell Formatting)

Joneye

Joneye

Well-known Member
Joined
May 28, 2010
Messages
777
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Hi Good people,

The problem cell is E:79, currently reporting #value, if i manually enter "0" in cell D:76 then cell E:79 updates ok.

What i need to understand is this a sum or formatting error? and what steps / advice would you offer to fix it?


Book1
ABCDEFGHIJKLMNOP
1Filename :
2Date Now :24-Jul-16
3 
4---------
5Week CommRentArrearsOverpaidReceivedComments
6---------
7
811-Apr-16£120.00£0.00£0.00£120.00£0.00
918-Apr-16£120.00£120.00 £240.00£0.00
1025-Apr-16£120.00£240.00 £360.00£0.00
1102-May-16£120.00£360.00 £480.00£480.00£0.00
1209-May-16£120.00£480.00 £600.00£0.00
1316-May-16£120.00£600.00 £720.00£0.00
1423-May-16£120.00£720.00 £840.00£0.00
1530-May-16£120.00£840.00 £480.00£960.00£0.00
1606-Jun-16£120.00£960.00 £1,080.00£0.00
1713-Jun-16£120.00£1,080.00 £1,200.00£0.00
1820-Jun-16£120.00£1,200.00 £1,320.00£0.00
1927-Jun-16£120.00£1,320.00 £480.00£1,440.00£0.00
2004-Jul-16£120.00£1,440.00 £1,560.00£0.00
2111-Jul-16£120.00£1,560.00 £1,680.00£0.00
2218-Jul-16£120.00£1,680.00 £1,800.00£0.00
2325-Jul-16£120.00£1,800.00 £1,920.00£0.00
2401-Aug-16£120.00  £2,040.00£0.00
2508-Aug-16£120.00  £2,160.00£0.00
2615-Aug-16£120.00  £2,280.00£0.00
2722-Aug-16£120.00  £2,400.00£0.00
2829-Aug-16£120.00  £2,520.00£0.00
2905-Sep-16£120.00  £2,640.00£0.00
3012-Sep-16£120.00  £2,760.00£0.00
3119-Sep-16£120.00  £2,880.00£0.00
3226-Sep-16£120.00  £3,000.00£0.00
3303-Oct-16£120.00  £3,120.00£0.00
3410-Oct-16£120.00  £3,240.00£0.00
3517-Oct-16£120.00  £3,360.00£0.00
3624-Oct-16£120.00  £3,480.00£0.00
3731-Oct-16£120.00  £3,600.00£0.00
3807-Nov-16£120.00  £3,720.00£0.00
3914-Nov-16£120.00  £3,840.00£0.00
4021-Nov-16£120.00  £3,960.00£0.00
4128-Nov-16£120.00  £4,080.00£0.00
4205-Dec-16£120.00  £4,200.00£0.00
4312-Dec-16£120.00  £4,320.00£0.00
4419-Dec-16£120.00  £4,440.00£0.00
4526-Dec-16£120.00  £4,560.00£0.00
4602-Jan-17£120.00  £4,680.00£0.00
4709-Jan-17£120.00  £4,800.00£0.00
4816-Jan-17£120.00  £4,920.00£0.00
4923-Jan-17£120.00  £5,040.00£0.00
5030-Jan-17£120.00  £5,160.00£0.00
5106-Feb-17£120.00  £5,280.00£0.00
5213-Feb-17£120.00  £5,400.00£0.00
5320-Feb-17£120.00  £5,520.00£0.00
5427-Feb-17£120.00  £5,640.00£0.00
5506-Mar-17£120.00  £5,760.00£0.00
5613-Mar-17£120.00  £5,880.00£0.00
5720-Mar-17£120.00  £6,000.00£0.00
5827-Mar-17£120.00  £6,120.00£0.00
5903-Apr-17£120.00  £6,240.00£0.00
6010-Apr-17  £6,240.00£0.00
61==========
6224-Jul-16
63£0.00
64====
65
66
67Week CommRentArrearsOverpaidReceived
68-------
69
7027-Jun-16£120.00£1,320.00 £480.00
71
7204-Jul-16£120.00£1,440.00 £0.00
73
7411-Jul-16£120.00£1,560.00 £0.00
75
7618-Jul-16£120.00£1,680.00 £0.00
77
78-------
79Carried forward to next statement and now due#VALUE!#VALUE!
80=
Sheet1
Cell Formulas
RangeFormula
B2=NOW()
B3=B2
B70=VLOOKUP($A$70,$A$8:$C$60,2)
B72=VLOOKUP($A$72,$A$8:$C$60,2)
B74=VLOOKUP($A$74,$A$8:$C$60,2)
B76=VLOOKUP($B$3,$A$8:$C$60,2)
D8=IF((O7-P8)>1," ",(O7-P8)*-1)
D9=IF((O8-P9)>1," ",(O8-P9)*-1)
D10=IF((O9-P10)>1," ",(O9-P10)*-1)
D11=IF((O10-P11)>1," ",(O10-P11)*-1)
D12=IF((O11-P12)>1," ",(O11-P12)*-1)
D13=IF((O12-P13)>1," ",(O12-P13)*-1)
D14=IF((O13-P14)>1," ",(O13-P14)*-1)
D15=IF((O14-P15)>1," ",(O14-P15)*-1)
D16=IF((O15-P16)>1," ",(O15-P16)*-1)
D17=IF((O16-P17)>1," ",(O16-P17)*-1)
D18=IF((O17-P18)>1," ",(O17-P18)*-1)
D19=IF((O18-P19)>1," ",(O18-P19)*-1)
D20=IF((O19-P20)>1," ",(O19-P20)*-1)
D21=IF((O20-P21)>1," ",(O20-P21)*-1)
D22=IF((O21-P22)>1," ",(O21-P22)*-1)
D23=IF((O22-P23)>1," ",(O22-P23)*-1)
D24=IF((O23-P24)>1," ",(O23-P24)*-1)
D25=IF((O24-P25)>1," ",(O24-P25)*-1)
D26=IF((O25-P26)>1," ",(O25-P26)*-1)
D27=IF((O26-P27)>1," ",(O26-P27)*-1)
D28=IF((O27-P28)>1," ",(O27-P28)*-1)
D29=IF((O28-P29)>1," ",(O28-P29)*-1)
D30=IF((O29-P30)>1," ",(O29-P30)*-1)
D31=IF((O30-P31)>1," ",(O30-P31)*-1)
D32=IF((O31-P32)>1," ",(O31-P32)*-1)
D33=IF((O32-P33)>1," ",(O32-P33)*-1)
D34=IF((O33-P34)>1," ",(O33-P34)*-1)
D35=IF((O34-P35)>1," ",(O34-P35)*-1)
D36=IF((O35-P36)>1," ",(O35-P36)*-1)
D37=IF((O36-P37)>1," ",(O36-P37)*-1)
D38=IF((O37-P38)>1," ",(O37-P38)*-1)
D39=IF((O38-P39)>1," ",(O38-P39)*-1)
D40=IF((O39-P40)>1," ",(O39-P40)*-1)
D41=IF((O40-P41)>1," ",(O40-P41)*-1)
D42=IF((O41-P42)>1," ",(O41-P42)*-1)
D43=IF((O42-P43)>1," ",(O42-P43)*-1)
D44=IF((O43-P44)>1," ",(O43-P44)*-1)
D45=IF((O44-P45)>1," ",(O44-P45)*-1)
D46=IF((O45-P46)>1," ",(O45-P46)*-1)
D47=IF((O46-P47)>1," ",(O46-P47)*-1)
D48=IF((O47-P48)>1," ",(O47-P48)*-1)
D49=IF((O48-P49)>1," ",(O48-P49)*-1)
D50=IF((O49-P50)>1," ",(O49-P50)*-1)
D51=IF((O50-P51)>1," ",(O50-P51)*-1)
D52=IF((O51-P52)>1," ",(O51-P52)*-1)
D53=IF((O52-P53)>1," ",(O52-P53)*-1)
D54=IF((O53-P54)>1," ",(O53-P54)*-1)
D55=IF((O54-P55)>1," ",(O54-P55)*-1)
D56=IF((O55-P56)>1," ",(O55-P56)*-1)
D57=IF((O56-P57)>1," ",(O56-P57)*-1)
D58=IF((O57-P58)>1," ",(O57-P58)*-1)
D59=IF((O58-P59)>1," ",(O58-P59)*-1)
D60=IF((O59-P60)>1," ",(O59-P60)*-1)
D70=VLOOKUP($A$70,$A$8:$F$60,4)
D72=VLOOKUP($A72,$A$8:$F$60,4)
D74=VLOOKUP($A74,$A$8:$F$60,4)
D76=VLOOKUP($A76,$A$8:$F$60,4)
O8=B8+C8
O9=B9+O8
O10=B10+O9
O11=B11+O10
O12=B12+O11
O13=B13+O12
O14=B14+O13
O15=B15+O14
O16=B16+O15
O17=B17+O16
O18=B18+O17
O19=B19+O18
O20=B20+O19
O21=B21+O20
O22=B22+O21
O23=B23+O22
O24=B24+O23
O25=B25+O24
O26=B26+O25
O27=B27+O26
O28=B28+O27
O29=B29+O28
O30=B30+O29
O31=B31+O30
O32=B32+O31
O33=B33+O32
O34=B34+O33
O35=B35+O34
O36=B36+O35
O37=B37+O36
O38=B38+O37
O39=B39+O38
O40=B40+O39
O41=B41+O40
O42=B42+O41
O43=B43+O42
O44=B44+O43
O45=B45+O44
O46=B46+O45
O47=B47+O46
O48=B48+O47
O49=B49+O48
O50=B50+O49
O51=B51+O50
O52=B52+O51
O53=B53+O52
O54=B54+O53
O55=B55+O54
O56=B56+O55
O57=B57+O56
O58=B58+O57
O59=B59+O58
O60=B60+O59
P8=Q7
P9=Q8
P10=Q9
P11=Q10
P12=Q11
P13=Q12
P14=Q13
P15=Q14
P16=Q15
P17=Q16
P18=Q17
P19=Q18
P20=Q19
P21=Q20
P22=Q21
P23=Q22
P24=Q23
P25=Q24
P26=Q25
P27=Q26
P28=Q27
P29=Q28
P30=Q29
P31=Q30
P32=Q31
P33=Q32
P34=Q33
P35=Q34
P36=Q35
P37=Q36
P38=Q37
P39=Q38
P40=Q39
P41=Q40
P42=Q41
P43=Q42
P44=Q43
P45=Q44
P46=Q45
P47=Q46
P48=Q47
P49=Q48
P50=Q49
P51=Q50
P52=Q51
P53=Q52
P54=Q53
P55=Q54
P56=Q55
P57=Q56
P58=Q57
P59=Q58
P60=Q59
C9=IF(D9=" ",IF(A9-7>$B$3," ",IF((O9-P9)<1," ",O9-P9-B9))," ")
C10=IF(D10=" ",IF(A10-7>$B$3," ",IF((O10-P10)<1," ",O10-P10-B10))," ")
C11=IF(D11=" ",IF(A11-7>$B$3," ",IF((O11-P11)<1," ",O11-P11-B11))," ")
C12=IF(D12=" ",IF(A12-7>$B$3," ",IF((O12-P12)<1," ",O12-P12-B12))," ")
C13=IF(D13=" ",IF(A13-7>$B$3," ",IF((O13-P13)<1," ",O13-P13-B13))," ")
C14=IF(D14=" ",IF(A14-7>$B$3," ",IF((O14-P14)<1," ",O14-P14-B14))," ")
C15=IF(D15=" ",IF(A15-7>$B$3," ",IF((O15-P15)<1," ",O15-P15-B15))," ")
C16=IF(D16=" ",IF(A16-7>$B$3," ",IF((O16-P16)<1," ",O16-P16-B16))," ")
C17=IF(D17=" ",IF(A17-7>$B$3," ",IF((O17-P17)<1," ",O17-P17-B17))," ")
C18=IF(D18=" ",IF(A18-7>$B$3," ",IF((O18-P18)<1," ",O18-P18-B18))," ")
C19=IF(D19=" ",IF(A19-7>$B$3," ",IF((O19-P19)<1," ",O19-P19-B19))," ")
C20=IF(D20=" ",IF(A20-7>$B$3," ",IF((O20-P20)<1," ",O20-P20-B20))," ")
C21=IF(D21=" ",IF(A21-7>$B$3," ",IF((O21-P21)<1," ",O21-P21-B21))," ")
C22=IF(D22=" ",IF(A22-7>$B$3," ",IF((O22-P22)<1," ",O22-P22-B22))," ")
C23=IF(D23=" ",IF(A23-7>$B$3," ",IF((O23-P23)<1," ",O23-P23-B23))," ")
C24=IF(D24=" ",IF(A24-7>$B$3," ",IF((O24-P24)<1," ",O24-P24-B24))," ")
C25=IF(D25=" ",IF(A25-7>$B$3," ",IF((O25-P25)<1," ",O25-P25-B25))," ")
C26=IF(D26=" ",IF(A26-7>$B$3," ",IF((O26-P26)<1," ",O26-P26-B26))," ")
C27=IF(D27=" ",IF(A27-7>$B$3," ",IF((O27-P27)<1," ",O27-P27-B27))," ")
C28=IF(D28=" ",IF(A28-7>$B$3," ",IF((O28-P28)<1," ",O28-P28-B28))," ")
C29=IF(D29=" ",IF(A29-7>$B$3," ",IF((O29-P29)<1," ",O29-P29-B29))," ")
C30=IF(D30=" ",IF(A30-7>$B$3," ",IF((O30-P30)<1," ",O30-P30-B30))," ")
C31=IF(D31=" ",IF(A31-7>$B$3," ",IF((O31-P31)<1," ",O31-P31-B31))," ")
C32=IF(D32=" ",IF(A32-7>$B$3," ",IF((O32-P32)<1," ",O32-P32-B32))," ")
C33=IF(D33=" ",IF(A33-7>$B$3," ",IF((O33-P33)<1," ",O33-P33-B33))," ")
C34=IF(D34=" ",IF(A34-7>$B$3," ",IF((O34-P34)<1," ",O34-P34-B34))," ")
C35=IF(D35=" ",IF(A35-7>$B$3," ",IF((O35-P35)<1," ",O35-P35-B35))," ")
C36=IF(D36=" ",IF(A36-7>$B$3," ",IF((O36-P36)<1," ",O36-P36-B36))," ")
C37=IF(D37=" ",IF(A37-7>$B$3," ",IF((O37-P37)<1," ",O37-P37-B37))," ")
C38=IF(D38=" ",IF(A38-7>$B$3," ",IF((O38-P38)<1," ",O38-P38-B38))," ")
C39=IF(D39=" ",IF(A39-7>$B$3," ",IF((O39-P39)<1," ",O39-P39-B39))," ")
C40=IF(D40=" ",IF(A40-7>$B$3," ",IF((O40-P40)<1," ",O40-P40-B40))," ")
C41=IF(D41=" ",IF(A41-7>$B$3," ",IF((O41-P41)<1," ",O41-P41-B41))," ")
C42=IF(D42=" ",IF(A42-7>$B$3," ",IF((O42-P42)<1," ",O42-P42-B42))," ")
C43=IF(D43=" ",IF(A43-7>$B$3," ",IF((O43-P43)<1," ",O43-P43-B43))," ")
C44=IF(D44=" ",IF(A44-7>$B$3," ",IF((O44-P44)<1," ",O44-P44-B44))," ")
C45=IF(D45=" ",IF(A45-7>$B$3," ",IF((O45-P45)<1," ",O45-P45-B45))," ")
C46=IF(D46=" ",IF(A46-7>$B$3," ",IF((O46-P46)<1," ",O46-P46-B46))," ")
C47=IF(D47=" ",IF(A47-7>$B$3," ",IF((O47-P47)<1," ",O47-P47-B47))," ")
C48=IF(D48=" ",IF(A48-7>$B$3," ",IF((O48-P48)<1," ",O48-P48-B48))," ")
C49=IF(D49=" ",IF(A49-7>$B$3," ",IF((O49-P49)<1," ",O49-P49-B49))," ")
C50=IF(D50=" ",IF(A50-7>$B$3," ",IF((O50-P50)<1," ",O50-P50-B50))," ")
C51=IF(D51=" ",IF(A51-7>$B$3," ",IF((O51-P51)<1," ",O51-P51-B51))," ")
C52=IF(D52=" ",IF(A52-7>$B$3," ",IF((O52-P52)<1," ",O52-P52-B52))," ")
C53=IF(D53=" ",IF(A53-7>$B$3," ",IF((O53-P53)<1," ",O53-P53-B53))," ")
C54=IF(D54=" ",IF(A54-7>$B$3," ",IF((O54-P54)<1," ",O54-P54-B54))," ")
C55=IF(D55=" ",IF(A55-7>$B$3," ",IF((O55-P55)<1," ",O55-P55-B55))," ")
C56=IF(D56=" ",IF(A56-7>$B$3," ",IF((O56-P56)<1," ",O56-P56-B56))," ")
C57=IF(D57=" ",IF(A57-7>$B$3," ",IF((O57-P57)<1," ",O57-P57-B57))," ")
C58=IF(D58=" ",IF(A58-7>$B$3," ",IF((O58-P58)<1," ",O58-P58-B58))," ")
C59=IF(D59=" ",IF(A59-7>$B$3," ",IF((O59-P59)<1," ",O59-P59-B59))," ")
C60=IF(D60=" ",IF(A60-7>$B$3," ",IF((O60-P60)<1," ",O60-P60-B60))," ")
C70=VLOOKUP($A$70,$A$8:$F$60,3)
C72=VLOOKUP($A$72,$A$8:$C$60,3)
C74=VLOOKUP($A$74,$A$8:$C$60,3)
C76=VLOOKUP($B$2,$A$8:$D$60,3)
A9=A8+7
A10=A9+7
A11=A10+7
A12=A11+7
A13=A12+7
A14=A13+7
A15=A14+7
A16=A15+7
A17=A16+7
A18=A17+7
A19=A18+7
A20=A19+7
A21=A20+7
A22=A21+7
A23=A22+7
A24=A23+7
A25=A24+7
A26=A25+7
A27=A26+7
A28=A27+7
A29=A28+7
A30=A29+7
A31=A30+7
A32=A31+7
A33=A32+7
A34=A33+7
A35=A34+7
A36=A35+7
A37=A36+7
A38=A37+7
A39=A38+7
A40=A39+7
A41=A40+7
A42=A41+7
A43=A42+7
A44=A43+7
A45=A44+7
A46=A45+7
A47=A46+7
A48=A47+7
A49=A48+7
A50=A49+7
A51=A50+7
A52=A51+7
A53=A52+7
A54=A53+7
A55=A54+7
A56=A55+7
A57=A56+7
A58=A57+7
A59=A58+7
A60=A59+7
A62=B2
A63=E3
A70=VLOOKUP($B$3-21,$A$8:$A$60,1)
A72=VLOOKUP($B$3-14,$A$8:$A$60,1)
A74=VLOOKUP($B$3-7,$A$8:$A$60,1)
A76=VLOOKUP($B$3,$A$8:$A$60,1)
E70=VLOOKUP($A$70,$A$8:$F$60,5)
E72=VLOOKUP($A$72,$A$8:$F$60,5)
E74=VLOOKUP($A$74,$A$8:$F$60,5)
E76=VLOOKUP($B$3,$A$8:$F$60,5)
E79=SUM(B76+C76+D76+E76)
F79=IF(E79<1," CR"," ")
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
We really don't need to see all your data, just a small enough sample that we can see what you have and what you want. You just posted 14 pages/screens of data that we need to scroll up and down through to see anything :(
 
Upvote 0
FDibbins said:
We really don't need to see all your data, just a small enough sample that we can see what you have and what you want. You just posted 14 pages/screens of data that we need to scroll up and down through to see anything :(
Click to expand...

Ok will do, not sure how to delete this thread :(
 
Upvote 0
You must log in or register to reply here.

Similar threads

SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
430
SaraWitch
SaraWitch
K
  • Question
Need formula help (multiple results check?)
Replies
7
Views
329
Kishan
K
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
578
felixstraube
F
J
  • Solved
help finding my error
Replies
1
Views
127
Proulxs
P
K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
233
Eric W
Eric W

Forum statistics

Threads
1,214,525
Messages
6,120,051
Members
448,940
Latest member
mdusw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top