Displaying #Value and I cannot find the root of the problem

[DanielBellfield]

Good afternoon all,

Looking for some help on the below. Basically i am trying to get the cell to be blank if there is no date in another coloum. It works fine, but if the cell above it is also blank it displays #value. The dates change accoarding to month and therefore i want to keep the formula the same but edit the dates.

Heres where i'm upto.

[TABLE="class: grid, width: 892"]
<tbody>[TR]
[TD]Order Intake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Daily Target:[/TD]
[TD]17500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Daily Total[/TD]
[TD][/TD]
[TD][/TD]
[TD]Running Total[/TD]
[TD]Daily Average Running Total[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(C5="","",C5)[/TD]
[TD]=IF(B5=0," ",$B$3)[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(C6="","",C6+D5)[/TD]
[TD]=IF(B6=0," ",$B$3+E5)[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(C7="","",C7)[/TD]
[TD]=IF(B7=0," ",$B$3+E6)[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(C8="","",C8+D7)[/TD]
[TD]=IF(B8=0," ",$B$3+E7)[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]42552[/TD]
[TD][/TD]
[TD]=IF(C9="","",C9)[/TD]
[TD]=IF(B9=0," ",$B$3+E8)[/TD]
[/TR]
</tbody>[/TABLE]

Help please!

Thank you.
Dan

 

[Michael_E]

The reason it's displaying value is because "" is a string and not automatically calculated to 0 as one would inherently assume. I assume it's happening in D6, D8 and E6:E9.

You can fix it by changing D6 and D8 to =IF(C6="","",IF(D5="",C6,C6+D5)) and =IF(C8="","",IF(D7="",C8,C8+D7))
I'm not sure why you have your E column checking for 0 and inserting spaces, but you can fix your #value problem by editing the formulas the same way, just add the nested IF statement to check if the column above is blank

or you can just change all your " "s and ""s to 0s, and then it'll work too

I realize I was mistaken, It's not the ""s that are the problem it's where you're inputting " ", "" is automatically calculated as 0, if you change your " "s to ""s it will fix your problem. However your running total will be weird if there no data on an earlier day in the week, for instance there are numbers for Monday and Tuesday but not Wednesday, at that point Thursday won't factor in Monday and Tuesdays numbers... if that makes sense.

I'm not sure if that's intended, but if it's not you can remove the IFs in E and replace them with just =B$3$ + the cell above and you won't have that problem.

 

[Jonmo1]

Or change
C6+D5
to
SUM(C6,D5)

 

[DanielBellfield]

Sorry guys the cell in question is displayed in RED

[TABLE="class: grid, width: 892"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Order Intake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Month:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Daily Target:[/TD]
[TD]17500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Daily Total[/TD]
[TD][/TD]
[TD][/TD]
[TD]Running Total[/TD]
[TD]Daily Average Running Total[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Monday[/TD]
[TD]01/08/2016[/TD]
[TD][/TD]
[TD]=IF(C5="","",C5)[/TD]
[TD]=IF(B5=0," ",$B$3)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Tuesday[/TD]
[TD]02/08/2016[/TD]
[TD][/TD]
[TD]=IF(C6="","",C6+D5)[/TD]
[TD]=IF(B6=0," ",$B$3+E5)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Wednesday[/TD]
[TD]03/08/2016[/TD]
[TD][/TD]
[TD]=IF(C7="","",C7)[/TD]
[TD]=IF(B7=0," ",$B$3+E6)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Thursday[/TD]
[TD]04/08/2016[/TD]
[TD][/TD]
[TD]=IF(C8="","",C8+D7)[/TD]
[TD]=IF(B8=0," ",$B$3+E7)[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Friday[/TD]
[TD]05/08/2016[/TD]
[TD][/TD]
[TD]=IF(C9="","",C9)[/TD]
[TD]=IF(B9=0," ",$B$3+E8)[/TD]
[/TR]
</tbody>[/TABLE]

Any Ideas why this would be displaying as #VALUE

 

[Michael_E]

Putting in your data as you have it written here had the E column displaying

17500,35000,52500,70000,and then 87500 for me.

 

[Michael_E]

Put =IF(B5=0," ",SUM($B$3)) in E5 and =IF(B6=0," ",SUM($B$3,E5)) in E6, then paste E6 through E9

 

[Jonmo1]

Anywhere you have

Anycell+Anyothercell

change it to

SUM(Anycell, Anyothercell)



Text+Number = #Value! error

the SUM function will ignore the text.