How to recognize a blank cell as zero

Toadstool

Well-known Member

Joined

Mar 5, 2018

Messages

2,549

Office Version

1. 2016

Platform

1. Windows

• May 24, 2020

• #2

Hi Pedro-Egoli,

I can't see what is wrong with your formula. Can you explain what is failing and provide a scenario?

 

Upvote 0

P

pedro-egoli

Well-known Member

Joined

Apr 25, 2004

Messages

1,217

Office Version

1. 2016

Platform

1. Windows

• May 24, 2020

• #3

The result of formula as it stands is that when a cell becomes blank in column D the result shows as the last non zero balance.
This then displays an incorrect balance in another cell dependent on result.

 

Upvote 0

Toadstool

Well-known Member

Joined

Mar 5, 2018

Messages

2,549

Office Version

1. 2016

Platform

1. Windows

• May 24, 2020

• #4

I still don't see the issue as here I have the last value as 11,308

...and if I make some cells empty I still get the same result:

 

Upvote 0

shaowu459

Well-known Member

Joined

Apr 26, 2018

Messages

691

Office Version

1. 365

Platform

1. Windows

• May 24, 2020

• #5

I don't see any unexpected results.

Book1.xlsx
	D	E	F
4
5
6	111		0
7
8	333
9
10
11
12
13	1
14
15	4
16
17
18	0
Sheet6

Cell Formulas
Range	Formula
F6	F6	=LOOKUP(9^99,D5:D500)

 

Upvote 0

P

pedro-egoli

Well-known Member

Joined

Apr 25, 2004

Messages

1,217

Office Version

1. 2016

Platform

1. Windows

• May 24, 2020

• #6

Toadstool said:

I still don't see the issue as here I have the last value as 11,308

View attachment 14614

...and if I make some cells empty I still get the same result:

View attachment 14615

Click to expand...

This is part of my sheet showing blanks

NEW sheet commenced 10717.xls
	A	B	C	D	E
34	23/05/2020			-$105.39	$3,412.49
35	24/05/2020	-$47.30		-$105.39	$3,365.19
36	24/05/2020	-$105.39			$3,259.80
37	25/05/2020				$3,259.80
May 2020

Cell Formulas
Range	Formula
E34:E37	E34	=IF(A34="","",E33+(B34+C34))

In cell with formula it displays -$105.39 rather than a blank or "0"
The solution provided by Shaow459 works so thank you both for your interest and help

 

Upvote 0